Cómo Crear un Mapa de Calor en Excel – Una Guía Paso A paso

Un mapa de calor en Excel es una representación visual que le muestra rápidamente una vista comparativa de un conjunto de datos.

Por ejemplo, en el conjunto de datos a continuación, puedo detectar fácilmente cuáles son los meses en los que las ventas fueron bajas (resaltadas en rojo) en comparación con otros meses.

En el conjunto de datos anterior, los colores se asignan en función del valor de la celda. La escala de colores es de verde a Amarillo a Rojo, con valores altos que obtienen el color verde y valores bajos que obtienen el color rojo.

Crear un mapa de calor en Excel

Mientras que puede crear un mapa de calor en Excel codificando manualmente las celdas por colores. Sin embargo, tendrá que rehacerlo cuando los valores cambien.

En lugar del trabajo manual, puede usar formato condicional para resaltar celdas en función del valor. De esta manera, en caso de que cambie los valores en las celdas, el color/formato de la celda actualizará automáticamente el mapa de calor en función de las reglas predeterminadas en formato condicional.

En este tutorial, aprenderá a:

• Crear rápidamente un mapa de calor en Excel utilizando formato condicional.
• Cree un mapa de calor dinámico en Excel.
• Crear un mapa de calor en tablas dinámicas de Excel.

¡Comencemos!

Crear un mapa de calor en Excel Con formato condicional

Si tiene un conjunto de datos en Excel, puede resaltar manualmente los puntos de datos y crear un mapa de calor.

Sin embargo, sería un mapa de calor estático, ya que el color no cambiaría al alterar el valor en una celda.

Por lo tanto, el formato condicional es el camino correcto, ya que hace que el color de una celda cambie cuando cambia el valor en ella.

Supongamos que tiene un conjunto de datos como se muestra a continuación:

Estos son los pasos para crear un mapa de calor utilizando estos datos:

• Seleccione el conjunto de datos. En este ejemplo, sería B2:D13.
• Ir a Casa –> Formato Condicional –> Escalas de Color. Muestra varias combinaciones de colores que se pueden usar para resaltar los datos. La escala de colores más común es la primera en la que las celdas con valores altos se resaltan en verde y las celdas con valores bajos en rojo. Tenga en cuenta que al pasar el mouse sobre estas escalas de colores, puede ver la vista previa en vivo en el conjunto de datos.
  

Esto le dará un mapa de calor como se muestra a continuación:

De forma predeterminada, Excel asigna el color rojo al valor más bajo y el color verde al valor más alto, y todos los valores restantes obtienen un color basado en el valor. Así que hay un degradado con diferentes tonos de los tres colores basados en el valor.

Ahora, qué pasa si no quieres un degradado y solo quieres mostrar rojo, amarillo y verde. Por ejemplo, desea resaltar todos los valores inferiores a 700 en rojo, independientemente del valor. Así que 500 y 650 ambos obtienen el mismo color rojo ya que es menos de 700.

Para hacer esto:

• Ir a Casa –> Formato Condicional –> Escalas de Color –> Más Opciones.
• En el cuadro de diálogo Nueva regla de formato, seleccione ‘Escala de 3 colores’ en el menú desplegable Estilo de formato.
• Ahora puede especificar el valor mínimo, el punto medio y el máximo y asignarle el color. Dado que queremos resaltar todas las celdas con un valor inferior a 700 en rojo, cambie el tipo a Número y el valor a 700.
• Haga clic en Aceptar.

Ahora obtendrá el resultado como se muestra a continuación. Tenga en cuenta que todos los valores por debajo de 700 obtienen el mismo tono de color rojo.

Una advertencia: Si bien el formato condicional es una herramienta maravillosa, desafortunadamente, es volátil. Esto significa que siempre que haya algún cambio en la hoja de trabajo, el formato condicional se vuelve a calcular. Si bien el impacto puede ser insignificante en conjuntos de datos pequeños, puede llevar a un libro de Excel lento cuando se trabaja con conjuntos de datos grandes.

Crear un mapa de calor dinámico en Excel

Dado que el formato condicional depende del valor de una celda, tan pronto como cambie el valor, el formato condicional se vuelve a calcular y cambia.

Esto hace posible hacer un mapa de calor dinámico.

Veamos dos ejemplos de creación de mapas de calor utilizando controles interactivos en Excel.

Ejemplo 1: Mapa de calor usando la barra de desplazamiento

Aquí hay un ejemplo en el que el mapa de calor cambia tan pronto como se usa la barra de desplazamiento para cambiar el año.

Este tipo de mapas de calor dinámicos se puede usar en paneles donde tiene restricciones de espacio, pero aún desea que el usuario acceda a todo el conjunto de datos.

Haga clic aquí para descargar la plantilla de mapa de calor

¿Cómo crear este mapa de calor dinámico?

Aquí está el conjunto de datos completo que se utiliza para crear este mapa de calor dinámico.

Estos son los pasos:

• En una hoja nueva (o en la misma hoja), introduzca los nombres de los meses (simplemente copie y pegue los datos originales).
• Ir a por el Desarrollador> Controles> Insertar –> Barra de Desplazamiento. Ahora haga clic en cualquier parte de la hoja de trabajo, e insertará una barra de desplazamiento. (haga clic aquí si no puede encontrar la pestaña desarrollador).
• Haga clic con el botón derecho en la barra de desplazamiento y haga clic en Control de formato.
• En el cuadro de diálogo Control de formato, realice los siguientes cambios:
  • Valor mínimo: 1
  • Valor máximo 5
  • Enlace celular: Sheet1!J J 1 1 (Puede hacer clic en el icono de la derecha y luego seleccionar manualmente la celda que desea vincular a la barra de desplazamiento).
• Haga clic en Aceptar.
• En la celda B1, ingrese la fórmula: = INDEX (Sheet1!B B 1 1: H H 1 13,FILA(),Hoja 1!COLUMNS J 1 1+COLUMNAS (Hoja2!$B$1:B1) -1)
• Cambiar el tamaño y colocar la barra de desplazamiento en la parte inferior del conjunto de datos.

Ahora, cuando cambie la barra de desplazamiento, ¡el valor en Sheet1!change J 1 1 cambiaría, y dado que las fórmulas están vinculadas a esta celda, se actualizaría para mostrar los valores correctos.

Además, dado que el formato condicional es volátil, tan pronto como el valor cambia, también se actualiza.

Ver mapa de calor dinámico de vídeo en Excel

Ejemplo 2: Crear un mapa de calor dinámico en Excel usando Botones de opción

Aquí hay otro ejemplo en el que puede cambiar el mapa de calor haciendo una selección de botón de opción:

En este ejemplo, puede resaltar 10 valores superiores/inferiores en función de la selección del botón de opción/radio.

Haga clic aquí para descargar la plantilla de Mapa de calor

Crear un mapa de calor en la Tabla dinámica de Excel

El formato condicional en las Tablas dinámicas funciona de la misma manera que con cualquier dato normal.

Pero hay algo importante que debes saber.

Permítanme tomar un ejemplo y mostrarles.

Supongamos que tiene una tabla dinámica como se muestra a continuación:

Para crear un mapa de calor en esta Tabla dinámica de Excel:

• Seleccione las celdas (B5: D14).
• Ir a Casa –> Formato Condicional –> Escalas de Color y seleccione la escala de color que desea aplicar.

Esto crearía instantáneamente el mapa de calor en la tabla dinámica.

El problema con este método es que si agrega nuevos datos en el motor y actualiza esta Tabla dinámica, el formato condicional no se aplicará a los nuevos datos.

Por ejemplo, al agregar nuevos datos en el back-end, ajustar los datos de origen y actualizar la Tabla dinámica, puede ver que el formato condicional no se le aplica.

Esto sucede cuando aplicamos el formato condicional a las celdas B5:D14 solamente.

Si desea que este mapa de calor sea dinámico para que se actualice cuando se agreguen nuevos datos, estos son los pasos:

• Seleccione las celdas (B5:D14).
• Ir a Casa –> Formato Condicional –> Escalas de Color y seleccione la escala de color que desea aplicar.
• De nuevo vaya a Inicio – > Formato condicional –> Administrar reglas.
• En el Administrador de Reglas de formato condicional, haga clic en el botón Editar.
• En el cuadro de diálogo Editar Regla de formato, seleccione la tercera opción: Todas las celdas que muestran los valores de ‘Ventas’ para ‘Fecha’y ‘ Cliente’.

Ahora el formato condicional se actualizará cuando cambie los datos del motor.

Nota: El formato condicional desaparece si cambia los campos de fila/columna. Por ejemplo, si elimina el campo de fecha y lo vuelve a aplicar, se perderá el formato condicional.

También le pueden gustar los siguientes tutoriales de Excel:

• Cómo resaltar cada dos filas en Excel.
• Cree un Gráfico de Columnas de Pila al 100% utilizando Formato Condicional.
• Cómo contar celdas de colores en Excel.
• Cómo resaltar celdas en blanco en Excel.
• Excel Sparklines – Una Guía Completa