Gráfico de correlación en Excel: cómo crearlo y entenderlo

Tienes una hoja con notas de seis alumnos en cuatro asignaturas y la pregunta de siempre: si un alumno sube en Matemáticas, ¿sube también en Física? ¿Y baja cuando sube en Literatura? El gráfico de correlación responde a eso pintando, de un vistazo, qué variables se mueven juntas y cuáles van cada una por su lado. En este tutorial lo construyes paso a paso en un libro de notas realista: empiezas con dos funciones que calculan el coeficiente r, sigues con una matriz de correlaciones rellena con fórmulas, la conviertes en un mapa de calor con formato condicional y terminas con un gráfico de dispersión con su línea de tendencia. Todo funciona en Excel 2010, 2013, 2016, 2019, 2021, 2024 y Microsoft 365, y también en Excel para la web y para Mac.

Qué es un gráfico de correlación y para qué te sirve

Imagina la hoja Notas de un instituto: seis alumnos con sus notas de Matemáticas, Física, Literatura e Historia. La pregunta natural es si las asignaturas se parecen entre sí, o si quien destaca en una suspende en otra. Un gráfico de correlación pinta esa relación con colores o con puntos: si dos asignaturas suben juntas las verás en un tono, si van en direcciones opuestas las verás en otro, y si no tienen relación las verás en un color neutro.

El protagonista de ese gráfico es el coeficiente de correlación de Pearson, que en Excel se llama COEF.DE.CORREL. Es un número entre -1 y +1: cerca de +1 las dos variables suben juntas, cerca de -1 una sube cuando la otra baja, y cerca de 0 no hay relación lineal clara. Un gráfico de correlación no es más que la forma visual de leer ese coeficiente para muchos pares de variables a la vez.

Coeficiente r, valor p y la escala de -1 a +1

El coeficiente r mide solo la relación lineal: hasta qué punto los puntos de una nube se alinean en una recta. Si r vale 0,80, las dos variables están muy alineadas; si vale -0,80, también están alineadas pero una sube cuando la otra baja. El valor p, que Excel no calcula de forma directa, es la probabilidad de obtener esa r si en realidad no hubiera relación: cuanto más pequeño, más fiable es el coeficiente. La escala va siempre de -1 a +1, y r vale exactamente 1 cuando una variable es la otra multiplicada por un número positivo, y -1 cuando lo es por un número negativo.

Cuándo un gráfico de correlación te ahorra tablas

Cuando tienes varias variables y necesitas ver de un golpe cuáles se parecen y cuáles no, una tabla de números obliga a leer fila a fila y comparar mentalmente. Un mapa de calor colorea cada celda según su r y deja las relaciones fuertes a la vista. También sirve como filtro antes de montar un modelo: si dos variables tienen r muy alta, probablemente aportan lo mismo, y una sobra. Por último, un gráfico de dispersión con línea de tendencia te enseña la forma de la relación: lineal, en curva, o con un valor atípico que tira de la recta.

Las funciones de Excel que calculan la correlación

COEF.DE.CORREL y PEARSON devuelven exactamente el mismo número; puedes usar la que prefieras. COEFICIENTE.R2 es el cuadrado de r, así que siempre sale entre 0 y 1 y te dice qué porcentaje de la variación de una variable explica la otra. Más información en la ayuda oficial de COEF.DE.CORREL, PEARSON y COEFICIENTE.R2.

COEF.DE.CORREL: el coeficiente de Pearson en una celda

=COEF.DE.CORREL(B2:B7;C2:C7)

Esta fórmula devuelve el coeficiente de Pearson entre las notas de Matemáticas (columna B) y las de Física (columna C) de los seis alumnos. Cuanto más cerca de 1 o de -1, más fuerte es la relación lineal.

COEFICIENTE.R2: el cuadrado de r

=COEFICIENTE.R2(C2:C7;B2:B7)

Devuelve el cuadrado de r, siempre entre 0 y 1. Si COEF.DE.CORREL te da 0,85, COEFICIENTE.R2 te da 0,7225, que se interpreta como "el 72,25 % de la variación de Física se explica por Matemáticas".

Dónde está el gráfico en la cinta y qué necesitas para crearlo

Excel no tiene un tipo de gráfico llamado "de correlación". Lo que sí tiene es un gráfico de dispersión (cada punto es una pareja de valores) y un formato condicional de escala de color que convierte una tabla en un mapa de calor. Por eso el camino real es: primero calculas los coeficientes con fórmulas, luego los pintas con formato condicional y, si quieres ver la forma de la relación entre dos variables, usas un gráfico de dispersión con línea de tendencia. El botón [Gráficos recomendados] del grupo [Gráficos] en la pestaña [Insertar] te ayuda a elegir el tipo adecuado a partir del rango seleccionado.

Matriz de correlaciones: el primer paso antes del gráfico

La matriz es una tabla cuadrada con los nombres de las variables en la primera fila y en la primera columna. En cada celda interior va un COEF.DE.CORREL entre el par correspondiente. La diagonal vale siempre 1 (la correlación de una variable consigo misma).

Construir tu primera matriz de correlaciones con fórmulas

1. En la hoja Notas, escribe los nombres de las cuatro asignaturas en F1:I1 y, debajo, cópialos también en E2:E5 (puedes escribir Matemáticas en E2, Física en E3, Literatura en E4 e Historia en E5).
2. Selecciona F2 y escribe la fórmula para cruzar Matemáticas (columna B) con ella misma: =COEF.DE.CORREL($B$2:$B$7;$B$2:$B$7).
3. Pulsa Entrar. El resultado es 1, porque una variable está perfectamente correlacionada consigo misma.
4. En G2 cruza Matemáticas con Física: =COEF.DE.CORREL($B$2:$B$7;$C$2:$C$7). Anota el valor que sale.
5. En H2 cruza Matemáticas con Literatura, y en I2 con Historia.
6. Copia F2:I2 y pega en F3:I5 para rellenar el resto de la matriz.

Copiar la fórmula con el controlador de relleno

1. Selecciona G2 y mira la fórmula en la barra de fórmulas: verás $B$2:$B$7 y $C$2:$C$7 con los dólares.
2. Coloca el cursor sobre la esquina inferior derecha de G2 (el controlador de relleno) hasta que aparezca una cruz negra.
3. Arrastra hacia la derecha hasta I2 y suelta; la fórmula se copia a H2 e I2 ajustando la segunda referencia (C, D, E).
4. Selecciona F2:I2, arrastra el controlador hacia abajo hasta la fila 5 y suelta.

Interpretar la diagonal y los pares repetidos

La diagonal vale siempre 1: una variable está perfectamente correlacionada consigo misma. Además, la matriz es simétrica: el cruce Matemáticas-Física (G2) es el mismo número que Física-Matemáticas (F3). Por eso basta con mirar un solo triángulo. Si te molesta ver el doble, puedes borrar una mitad y dejar solo el triángulo inferior, pero mantenerlo completo ayuda a leer la matriz fila a fila.

Pintar la matriz con un formato condicional de colores

1. Selecciona la matriz F2:I5.
2. En la cinta, ve a Inicio > Estilos > Formato condicional > Escalas de color.
3. Elige una escala de tres colores (rojo-blanco-verde, o la que más te guste).
4. Acepta. Verás la matriz pintada: las celdas con r cercano a 1 en un color, las cercanas a -1 en otro y las cercanas a 0 en un tono neutro.

Escala de 3 colores con rojo, blanco y azul

Con los límites en -1, 0 y 1, el color refleja el valor real de r. Las correlaciones fuertes positivas saldrán en azul intenso, las fuertes negativas en rojo, y las cercanas a cero quedarán casi blancas.

Escala de 2 colores para ver solo el signo

Si solo te importa el signo, una escala de 2 colores en tonos de rojo basta: las correlaciones negativas en rojo claro, las positivas en rojo oscuro.

Ajustar los límites para que el color sea justo

Por defecto Excel usa percentiles, y la escala se reparte entre el menor y el mayor valor del rango. Si tu matriz va de -0,78 a 1, el percentil percentil el 0 como un valor intermedio raro y los colores no reflejarán la escala real. Cámbialo a [Número] y escribe -1, 0 y 1 a mano. También desmarca la casilla [Solo mostrar la barra de datos] si quieres ver la regla en el Administrador de reglas.

Convertir la matriz en un mapa de calor de verdad

El mapa de calor no es más que la matriz coloreada, pero conviene prepararla bien para que se lea de un vistazo. Crea una hoja nueva llamada Datos con las notas de cinco variables distintas (ventas, publicidad, precio, visitas, satisfacción) en A1:E13 y la matriz en G2:K6. Así separas los datos originales del gráfico.

Añadir los valores numéricos para leerlos sin lupa

1. Selecciona la matriz G2:K6.
2. Pulsa Ctrl+1 para abrir Formato de celdas.
3. En la pestaña [Número], categoría [Número], escribe 0,00 en [Posiciones decimales].
4. Acepta.
5. Vuelve a seleccionar la matriz y, en Inicio > Fuente > Color de fuente, elige blanco.

Congelar el resultado con Pegado especial valores

1. Selecciona la matriz G2:K6 y pulsa Ctrl+C.
2. Crea una hoja nueva llamada MapaCalor y selecciona A1.
3. Pulsa Ctrl+Mayús+V para abrir Pegado especial.
4. Marca [Valores] y desmarca [Fórmulas] y [Formato].
5. Acepta.
6. Aplica de nuevo la escala de color de 3 colores con límites -1, 0 y 1.

El gráfico de dispersión para ver la relación entre dos variables

1. Vuelve a la hoja Datos y selecciona las dos columnas que quieras cruzar, por ejemplo A1:A13 (Ventas) y B1:B13 (Publicidad). Incluye los encabezados.
2. En la cinta, ve a Insertar > Gráficos > Dispersión.
3. Elige [Dispersión solo con marcadores]. Aparece una nube de puntos.
4. Si los puntos se alinean en una diagonal ascendente, r es alto y positivo; si la diagonal es descendente, r es alto y negativo; si forman una nube sin forma, r está cerca de 0.

Añadir la línea de tendencia y leer su R²

1. Haz clic en cualquier punto de la nube para seleccionar la serie.
2. Clic derecho sobre un punto y elige [Agregar línea de tendencia].
3. En el panel, marca [Lineal].
4. Baja hasta [Presentar valor R cuadrado en el gráfico] y márcalo.
5. Cierra el panel. Sobre la línea aparecerá un texto del tipo R² = 0,6724.

=COEFICIENTE.R2(B2:B13;A2:A13)

Compara ese R² con el que da la fórmula en una celda: deben coincidir. Si la línea no aparece, asegúrate de que has hecho clic en un punto y no en un área vacía del gráfico.

Detectar valores atípicos que engañan a r

Vuelve a la hoja Notas y añade un séptimo alumno con un 10 en Matemáticas y un 1 en Física (Irene, fila 8, columnas B y C). Vuelve a la celda G2 y observa el coeficiente: el r entre Matemáticas y Física habrá cambiado bastante. Un solo punto extremo puede subir o bajar r varios décimos. Por eso, antes de tomar una decisión basada en r, conviene dibujar la nube y ver si hay valores atípicos que tiran de la recta.

Errores que de verdad pasan al hacer el gráfico

#¿NOMBRE? al escribir el nombre de la función

Suele pasar al traducir mal el nombre. La función es COEF.DE.CORREL, con dos puntos después de COEF, y los argumentos separados por punto y coma. Si tu Excel está en otro idioma, usa el asistente de funciones: Mayús+F3, busca "correlación" y selecciona la que devuelva el coeficiente de Pearson. Más atajos en la lista oficial de atajos de Excel.

El gráfico se queda en blanco o no toma los datos

Causa típica: el rango seleccionado incluye la fila de encabezados en una columna sí y en otra no, o hay celdas vacías o con texto entre los números. Revisa que las dos columnas tengan la misma cantidad de valores numéricos y que la selección empiece y termine en celdas con número. Si la matriz tiene un #¿NOMBRE? o un #¡VALOR!, el formato condicional no la pintará.

Los colores del formato condicional desaparecen al cambiar los datos

Si la matriz se generó con un rango fijo y luego añadiste filas, Excel no amplía el rango solo. La solución más limpia es convertir los datos en una tabla de Excel: selecciona los datos, Insertar > Tablas > Tabla, marca [La tabla tiene encabezados] y acepta. Ahora el formato condicional seguirá a la tabla cuando crezca.

Crees que r alto implica causalidad y no es así

r mide relación lineal, no causa-efecto. Dos variables pueden subir juntas porque las dos dependen de una tercera (el clásico ejemplo del helado y los ahogamientos, que suben los dos cuando hace calor). Lee siempre r junto con la lógica del problema, no como una prueba de que una variable cause la otra.

Lo que ya sabes hacer con el gráfico de correlación

• Calcular el coeficiente de Pearson entre dos variables con COEF.DE.CORREL.
• Calcular el porcentaje de varianza explicada con COEFICIENTE.R2.
• Construir una matriz de correlaciones completa con fórmulas, fijando los rangos con dólares.
• Convertir la matriz en un mapa de calor con formato condicional de 2 o 3 colores.
• Dibujar la relación entre dos variables con un gráfico de dispersión y su línea de tendencia, y detectar valores atípicos que muevan r.

Siguiente paso: añade una sexta variable a la hoja Datos, amplia la matriz a G2:L7, aplica la escala de color a todo el rango de una vez y exporta el mapa de calor como imagen para tu informe. Más detalles sobre gráficos en la ayuda oficial de gráficos de Excel.

Dudas al hacer un gráfico de correlación en Excel