Tutorial: Cómo usar el Formato Condicional en Excel (Conditional Formatting)
Excel nos ofrece una magnífica herramienta para aplicar formatos especiales a una serie de celdas, color, símbolos, iconos … siempre que los datos que aparecen en dichas celdas cumplan o no con un criterio previamente establecido por nosotros.
Usado en informes, en dashboards o en simples hojas de cálculo, el formato condicional nos aporta un plus en la visualización de los datos. Pero qué mejor forma de aprender a utilizarlo iniciando este tutorial, tomando como muestra de ejemplo las calificaciones en matemáticas de una clase imaginaria con 15 alumnos.
La base de datos de partida sería la siguiente:
Para acceder a la herramienta formato condicional, partimos de la pestaña Inicio, haciendo click en el icono Formato condicional se despliegan las opciones predefinidas existentes que iremos viendo una a una.
El primer ejercicio va a ser destacar aquellos alumnos que han aprobado el examen. Para ello, seleccionamos el rango de celdas que contienen las calificaciones, clickamos en el icono Formato condicional / Reglas para resaltar celdas / Es mayor que …
Doña Regla, la profesora, establece que sus alumnos superan la asignatura si su calificación es superior a 4,75. Aplicamos este criterio en el desplegable que se nos abre y le damos a aceptar.
El resultado es inmediato. Automáticamente aparecen resaltadas en relleno rojo claro con texto rojo oscuro (la opción de visualización que hemos elegido) las celdas que cumplen con este criterio. De esta manera, la profesora tendrá un feedback visual inmediato de los alumnos que han aprobado el examen.
Con la opción Reglas para resaltar celdas, podemos hacer tantas combinaciones como se nos ocurran y se adapten a las opciones preexistentes (es mayor que, es menor que, entre valores…). Más posibilidades nos aporta la última opción que aparece con el nombre de Más reglas, pues es donde nosotros podemos establecer otros criterios con más detalle.
Podemos hacer, por ejemplo, que se destaque en verde aquellos alumnos que han sacado sobresaliente si adoptamos como criterio el de haber obtenido más de un 8,75 en el examen. No olvides establecer el formato en el que se va a resaltar (lo verás en vista previa), pues por defecto no aparece ninguno
Otro ejemplo. Queremos resaltar aquellos alumnos en cuyo nombre aparezca la letra “e”, porque nos apetece saberlo. Procedemos con la siguiente ruta: Formato condicional / Reglas para resaltar celdas / Texto que contiene… y nos saldrá el siguiente cuadro de diálogo. Dándole a aceptar, nos aplica el formato a las celdas texto seleccionadas.
El siguiente subapartado de Formato condicional es el de Reglas superiores e inferiores. Clickando sobre él nos despliega unas posibilidades de criterios de orden: Las 10 cifras superiores, el 10 % de valores superiores, las 10 inferiores, 10 % de valores inferiores, por encima del promedio …
En este caso, vamos a destacar aquellos alumnos que se encuentran en el 10 % de mejores calificaciones del examen. El resultado es el siguiente:
Sólo dos alumnos, destacados en verde, están en el 10 % de mejores resultados.
Las posibilidades son muchas, incluso si clickamos en la opción Más reglas, establecemos los criterios que queramos en un abanico más amplio.
El tercer subapartado del icono Formato condicional, es el de Barras de datos. Sobre cada celda, se dibuja una barra con color y relleno seleccionable, cuya longitud depende del valor de la celda.
En este caso hemos elegido un relleno degradado azul, el resultado es el siguiente:
Como vemos, el resultado visual es muy enriquecedor para la realización de un informe o dashboard.
Antes de seguir, un consejo. Si queremos evitar superponer diferentes formatos condicionales, antes de aplicar una nueva opción, deberíamos eliminar el formato condicional anteriormente adoptado. Para ello seleccionamos el rango de celdas y clickamos en Formato condicional / Borrar reglas / Borrar reglas de las celdas seleccionadas, ó bien de toda la hoja.
Si al realizar gráficos de barras en las celdas seleccionadas queremos evitar que el valor de la celda aparezca y sólo lo haga la barra con la dimensión correspondiente, la ruta sería Formato condicional / Barras de datos / Más reglas, y seleccionamos la opción Mostrar sólo la barra.
El resultado sería el siguiente:
El siguiente subapartado a considerar sería el de barras de color (este nos acerca mucho al concepto de Mapa de Calor / Heat Map.
Aquí hemos incorporado una escala de colores, a modo de semáforo, en el que los tonos verdes serían para buenas notas, amarillos para medias, y rojos para malas notas. Los valores críticos los podemos controlar desde la opción Más reglas.
La diferencia con un Mapa de Calor / Heat Map, es simplemente la escala de color a elegir, en un extremo estarían los colores fríos con tonalidades azules, y del otro extremo los rojos.
Como se ve, varias opciones pero con una misma lógica de funcionamiento.
Una vez que se crean las barras, es fácil regresar y cambiar los colores. Se selecciona el rango de las celdas y se hace click en Formato condicional / Administrar reglas, donde se abre un cuadro de diálogo donde podemos modificar criterios, colores …
Conjunto de iconos
Es una subcategoría más dentro de Formato condicional, sin embargo, abre una serie de posibilidades enormes, fundamentalmente a la hora de diseñar dashboards o cuadros de mando donde se informa de la evolución de los principales indicadores (KPI).
Para este tutorial, incorporamos dos columnas de datos más; una donde se informa de las calificaciones de los alumnos en un examen anterior, y una última columna donde se calcula por diferencia la evolución al alza o a la baja de las calificaciones.
Las variedades de iconos son importantes. Comenzamos por establecer indicadores en función de la calificación del último examen. El procedimiento en este caso, aconseja que no indiquemos directamente el icono a usar una vez seleccionemos el rango de celdas, sino que clickemos en la última opción, Más reglas, donde se nos abre el siguiente cuadro de diálogo, donde no sólo decidimos qué tipo de iconos incorporar, sino el criterio para su asignación.
En este caso, seleccionamos estilo de formato “conjunto de iconos”, en estilo de icono seleccionamos “aspa, interrogante y validación”, y en reglas establecemos que se asignará icono verde de validación si la nota es igual o superior a 6, interrogante amarillo si está entre el 5 y el 6, y aspa roja si el dato es inferior a 5. Importante seleccionar al tipo de dato adecuado; número en este caso, pero pudiera establecerse reglas en función de porcentajes de datos, fórmulas o percentiles (abrir el desplegable). El resultado es el siguiente:
Ahora vamos a representar tendencias (trends) o en el ejemplo, evolución de las notas. Para ello procedemos de manera similar. Seleccionamos el rango de los datos de Evolución, seleccionamos los iconos, establecemos los criterios, y aceptamos.
Y el resultado:
Pues a partir de aquí, conviene experimentar. Estas son las bases de funcionamiento. ¿Te atreves a explorar?