Formato Condicional en Excel

Qué es un formato condicional en Excel

El formato condicional es una herramienta de Excel para representar de forma visual y más atractiva la información de una o varias celdas siempre que ésta información cumpla unas condiciones que se han definido previamente. Muy utilizado para dar mayor visibilidad a nuestros informes o para resaltar valores clave dentro de una lista. Su uso está muy extendido y sirve de gran ayuda para realizar presentaciones visuales impactantes, pero también como “alarma visual” para reconocer dentro de un conjunto de información datos que a priori son más relevantes.

Al igual que las funciones condicionales los formatos condicionales operan a nivel lógico y en consecuencia actúan cuando una evaluación tiene como resultado “VERDADERO”. Un caso típico es marcar con fondo sombreado en rojo tenue y con letras en color rojo intenso las cifras más elevadas de un listado. Los datos de la lista se comparan unos con otros hasta localizar los valores más grandes, entonces, estos valores se formatean con el estilo definido.

Contenido de este artículo

A lo largo de este mini artículo veremos diferentes formas de aplicar los formatos condicionales:

• Aplicando reglas para resaltar celdas cuando
  • Un valor sea mayor que…
  • Un valor sea menor que…
  • Un valor se encuentre entre unos umbrales que definimos
  • Un valor concreto
  • Una celda de la lista contiene un texto concreto
  • La lista que evaluamos contiene un valor fecha que coincide con una fecha que establecemos
  • Existen valores duplicados
• Aplicando reglas para valores superiores e inferiores
  • Los 10 valores superiores “top ten”
  • El 10% de los valores superiores
  • Los 10 valores inferiores o “bottom ten”
  • EL 10% de los valores inferiores
  • Por encima del promedio
  • Por debajo del promedio
• Barras de datos, escalas de color y conjuntos de iconos
• Formatos condicionales a partir de fórmulas

Un valor sea mayor que…

El formato condicional “es mayor que…” nos permite evaluar un conjunto de valores y aplicar el formato condicional o alarma visual sobre aquellos valores que son más grandes que un valor predefinido.  Internamente compara cada valor del listado con el valor prefijado y en aquellos casos en los que la evaluación tiene como resultado “VERDADERO” o “TRUE” entonces se aplicará el formato preestablecido. Ejemplo: 1,2,4,7,2,6,7 aplicaremos color de letra rojo cursiva a los valores superiores a 5.

La comparativa interna que realiza Excel será:

1>5=FALSO

2>5=FALSO

4>5=FALSO

7>5=VERDADERO

2>5=FALSO

6>5=VERDADERO

7>5=VERDADERO

Y el resultado que obtendremos en la hoja de Excel: 1,2,4,7,2,6,7

Entonces, ¿Cómo aplicamos dentro de Excel el formato condicional?  Vemos en la imagen que aparece a continuación un conjunto de facturas de las que disponemos de su importe en la columna D. Lo que pretendemos es aplicar una alarma visual sobre aquellas cifras que sean superiores a 3000 €. Para ello seleccionamos el rango de valores sobre los que aplicar el formato y seguidamente haremos clic en el menú “Formato condicional” dentro de “Estilos” en el menú de “Inicio”. La opción seleccionada será “Es mayor que”. Solamente tendremos que rellenar dos opciones, la primera de ellas la cifra contra la que se comparan los datos del listado y que en nuestro caso hemos establecido en 3000€ y la segunda opción el color que deseamos aplicar.

Un valor sea menor que…

El formato condicional “es menor que…” nos permite evaluar un conjunto de valores y aplicar el formato condicional o alarma visual sobre aquellos valores que son más pequeños que un valor predefinido.  Internamente compara cada valor del listado con el valor prefijado y en aquellos casos en los que la evaluación tiene como resultado “VERDADERO” o “TRUE” entonces se aplicará el formato preestablecido. Ejemplo: 1,2,4,7,2,6,7 aplicaremos color de letra rojo cursiva a los valores inferiores a 5.

La comparativa interna que realiza Excel será:

1<5=VERDADERO

2<5= VERDADERO

4<5= VERDADERO

7<5=FALSO

2<5= VERDADERO

6<5= FALSO

7<5= FALSO

Y el resultado que obtendremos en la hoja de Excel: 1,2,4,7,2,6,7. Para aplicar este formato condicional los pasos a seguir igual que hicimos con el ejemplo anterior, tras seleccionar el rango adecuado de datos y hacer clic dentro del menú de formato condicional tendremos que seleccionar la opción de “menores que…”

En nuestro ejemplo hemos definido valores de 1000€. De manera que las facturas con importes inferiores a la cantidad señalada se colorean con el formato establecido.

Un valor se encuentre entre unos umbrales que definimos

El formato condicional “celdas comprendidas entre…” nos permite evaluar un conjunto de valores y aplicar el formato condicional o alarma visual sobre aquellos valores que comprendidos entre dos cotas.  Internamente compara cada valor del listado con el umbral inferior y el umbral superior en aquellos casos en los que la evaluación tiene como resultado “VERDADERO” o “TRUE” entonces se aplicará el formato preestablecido. Internamente Excel aplica una función de tipo “Y”. Solo todas las comprobaciones se cumplen entonces se aplicará el formato.

Ejemplo: 1,2,4,7,2,6,7 aplicaremos color de letra rojo cursiva a los valores mayores a 3 y menores a 7. ¡OJO! En esta comparación Excel lo evalúa como “mayor o igual” y “menor o igual”.

La comparativa interna que realiza Excel será:

(1>=3) y (1<=7) = FALSO

(2>=3) y (2<=7) = FALSO

(4>=3) y (4<=7) =VERDADERO

(7>=3) y (7<=7) =VERDADERO

(2>=3) y (2<=7) =FALSO

(6>=3) y (6<=7) =VERDAERO

(7>=3) y (7<=7) =VERDADERO

Y el resultado obtenido: 1,2,4,7,2,6,7

En nuestro ejemplo la idea será resaltar aquellas facturas con importe comprendido entre 1500€ y 2500€

Un valor concreto

El formato condicional “es igual a…” nos permite evaluar un conjunto de valores y aplicar el formato condicional o alarma visual sobre aquellos valores que sean estrictamente iguales al valor definido.  Internamente compara cada valor del listado con el valor definido y si el resultado de la comparación es “VERDADERO” o “TRUE” entonces se aplicará el formato preestablecido.

Ejemplo: 1,2,4,7,2,6,7 aplicaremos color de letra rojo cursiva a los valores iguales a 2.

La comparativa interna que realiza Excel será:

(1=2) = FALSO

(2=2) = VERDADERO

(4=2) = FALSO

(7=2) = FALSO

(2=2) = VERDADERO

(6=2) = FALSO

(7=2) = FALSO

Y el resultado obtenido: 1,2,4,7,2,6,7

En el ejemplo que hemos preparado para este artículo señalamos las facturas cuyo importe sea igual a 3675,36€

Una celda de la lista contiene un texto concreto

El formato condicional “Texto que contiene…” nos permite evaluar un conjunto de celdas y aplicar el formato condicional o alarma visual sobre aquellas celdas cuando contienen un texto previamente definido.  Internamente compara cada valor del listado con el valor definido y si el resultado de la comparación es “VERDADERO” o “TRUE” entonces se aplicará el formato preestablecido.

Ejemplo: “Factura, Albarán, Factura, Albarán, Pedido, Albarán, Abono” aplicaremos color de letra rojo cursiva a las celdas que contengan el texto “Factura”.

La comparativa interna que realiza Excel será:

(Factura = Factura) = VERDADERO

(Albarán = Factura) = FALSO

(Factura = Factura) = VERDADERO

(Albarán = Factura) = FALSO

(Pedido = Factura) = FALSO

(Albarán= Factura) = FALSO

(Abono= Factura) = FALSO

El resultado que obtendremos de esta evaluación será el siguiente: “Factura, Albarán, Factura, Albarán, Pedido, Albarán, Abono”.

Para ilustrarlo con nuestro fichero de facturas en esta ocasión nos centraremos en la columna “ESTADO” y haciendo clic en el asistente “Texto que contiene” definimos la palabra “Pendiente” como cadena de búsqueda.

La lista que evaluamos contiene un valor fecha que coincide con una fecha que establecemos

El formato condicional “Una fecha…” nos permite evaluar un conjunto de celdas en las que como contenido se han reflejado fechas y aplicar el formato condicional o alarma visual sobre aquellas celdas en las que la fecha coincida con la fecha previamente establecida.  Internamente compara cada valor del listado con el valor definido y si el resultado de la comparación es “VERDADERO” o “TRUE” entonces se aplicará el formato preestablecido.

Ahora que ya hemos entendido como internamente Excel va realizando las comparaciones lógicas para cada uno de los valores del listado vamos a visualizar directamente sobre nuestro ejemplo como las celdas que se resaltan son aquellas que precisamente cumplen con el criterio establecido.

En el momento de ejecutar este ejemplo nos encontramos en el mes de junio de 2018 y como criterio hemos seleccionado aquellas fechas correspondientes al mes anterior (mayo 2018) es por eso que el resultado son fechas de mayo de 2018. Se permite seleccionar distintas alternativas que pretenden cubrir la mayor parte de situaciones.

Existen valores duplicados

El formato condicional “Duplicar valores…” nos permite evaluar un conjunto de celdas y aplicar el formato condicional o alarma visual sobre aquellas celdas cuyo contenido se repite dentro del listado.  Internamente compara cada valor del listado con el resto de valores y si el valor se repite entonces el resultado de la comparación es “VERDADERO” o “TRUE” y se aplicará el formato preestablecido.

En esta ocasión hemos seleccionado el conjunto de “Números de factura” con la intención de averiguar que facturas aparecen varias veces.

Los 10 valores superiores “top ten”

El formato condicional “10 superiores…” nos permite evaluar un conjunto de celdas con valores numéricos y aplicar el formato condicional o alarma visual sobre aquellas celdas cuyos valores se encuentran entre los 10 más altos.  Internamente ordena los valores de mayor a menor y señala los 10 más elevados.

Aunque el formato se llama “10 superiores” podemos elegir el número de elementos y así presentar el “top 5”, “top 10”.

Una vez seleccionados todos los valores de la columna “IMPORTE” y aplicado el formato condicional vemos como se colorean las diez celdas con los montos más elevados.

El 10% de los valores superiores

El formato condicional “10% de valores superiores…” nos permite evaluar un conjunto de celdas con valores numéricos y aplicar el formato condicional o alarma visual sobre un conjunto de celdas concreto que se calcula como un porcentaje de celdas del total y que además contiene los valores más altos. Internamente ordena los valores de mayor a menor y se queda con un porcentaje de celdas.

Supongamos que contamos con una lista de 20 valores: 1,1,2,2,1,1,5,6,6,7,4,3,6,7,8,4,6,7,9,5 y le decimos al formato condicional que queremos que nos señale el 10% de valores superiores. Entonces Excel calcula el 10% de celdas respecto a las 20 celdas que contienen datos. En este caso entonces 10% de 20 será igual a 2 celdas. Entonces a partir de este dato se marcarán los dos valores más altos de la lista. Como resultado 1,1,2,2,1,1,5,6,6,7,4,3,6,7,8,4,6,7,9,5.

Si en vez de aplicar un 10% hubiéramos aplicado un 20% entonces tendremos que el 20% de 20 celdas se corresponde con 4 celdas. Así que nos señalará los 4 valores más alto. 1,1,2,2,1,1,5,6,6,7,4,3,6,7,8,4,6,7,9,5. Si observas con atención verás que hemos marcado en rojo 5 celdas y es que el valor 7 aparece 3 veces entonces se cubre una celda más.

Aplicamos este formato condicional sobre el conjunto de fechas de nuestro listado de facturas. Contamos con 25 facturas entonces en nuestro Excel tenemos 25 celdas que contienen fechas a evaluar. Como hemos elegido un 25% es decir ¼ de los valores que aproximadamente serán 6 celdas se marcarán en color rojo las 6 fechas más cercanas a la fecha actual.

Los 10 valores inferiores o “bottom ten”

El formato condicional “10 inferiores…” nos permite evaluar un conjunto de celdas con valores numéricos y aplicar el formato condicional o alarma visual sobre aquellas celdas cuyos valores se encuentran entre los 10 más bajos.  Internamente ordena los valores de mayor a menor y señala los 10 más pequeños.

Aunque el formato se llama “10 inferiores” podemos elegir el número de elementos y así presentar el “bottom 5”, “bottom 10”.

Una vez seleccionados todos los valores de la columna “IMPORTE” y aplicado el formato condicional vemos como se colorean las diez celdas con los montos menos elevados.

EL 10% de los valores inferiores

El formato condicional “10% de valores inferiores…” nos permite evaluar un conjunto de celdas con valores numéricos y aplicar el formato condicional o alarma visual sobre un conjunto de celdas concreto que se calcula como un porcentaje de celdas del total y que además contiene los valores más bajos. Internamente ordena los valores de mayor a menor y se queda con un porcentaje de celdas.

Para seguir con nuestro ejemplo hemos marcado las facturas que se encuentran entre el 10% con menores importes.

Por encima del promedio

El formato condicional “por encima del promedio…” nos permite evaluar un conjunto de celdas con valores numéricos y aplicar el formato condicional o alarma visual sobre un conjunto de celdas concreto cuyos valores sean mayores al promedio de todos los valores de la lista.

Para nuestro ejemplo hemos calculado el promedio de los valores de la columna “IMPORTE” posteriormente se ha aplicado el formato condicional para contrastar que efectivamente se aplica correctamente.

Por debajo del promedio

El formato condicional “por debajo del promedio…” nos permite evaluar un conjunto de celdas con valores numéricos y aplicar el formato condicional o alarma visual sobre un conjunto de celdas concreto cuyos valores sean menores al promedio de todos los valores de la lista.

Para nuestro ejemplo hemos calculado el promedio de los valores de la columna “IMPORTE” posteriormente se ha aplicado el formato condicional para contrastar que efectivamente se aplica correctamente.

Barras de datos, escalas de color y conjuntos de iconos

Las barras de datos, escalas de color y conjuntos de iconos forman parte de los formatos condicionales más divertidos y visuales que nos ofrece Excel. Vamos a comenzar realizando un ejemplo con las barras de datos. Las barras de datos nos permiten de una forma visual apreciar que datos de tipo numérico son mayores dentro de una lista.

El formato condicional “barras de datos…” nos permite evaluar un conjunto de celdas que se ordenan internamente y aplica el formato condicional o alarma visual sobre un conjunto de celdas previamente seleccionadas y cuyo contenido es numérico asignándole a cada celda una barra horizontal de color tan grande como posición ocupe ese valor dentro de la lista.

Ejemplo: 1,2,4,7,2,6,7 aplicaremos “barras de datos”

Como el valor más grande de la lista es “7” entonces la celda que contiene el “7” prácticamente se rellena con una barra horizontal vemos como las demás son proporcionales en tamaño.

Para nuestro ejemplo elegimos de nuevo la columna de importes y seguidamente desde el menú de formato condicional y seleccionando la opción barra de datos vemos como se colorea la barra para cada celda.

El formato condicional “escalas de color…” nos permite evaluar un conjunto de celdas que se ordenan internamente y aplica el formato condicional o alarma visual sobre un conjunto de celdas previamente seleccionadas y cuyo contenido es numérico asignándole a cada celda un color o intensidad de color.

Generalmente se interpreta el “verde” como valor más alto y “rojo” como valor inferior dejando las escalas de naranjas como valores intermedios. No obstante esta es una de las representaciones que podemos seleccionar. Existen también en el sentido contrario asignando verde a los valores inferiores y rojo a los valores superiores.

Finalmente presentamos los formatos condicionales más divertidos “Conjuntos de iconos”. Los conjuntos de iconos permiten representar “semáforos” que visualmente nos hacen que podamos tener una referencia de cómo interpretar un valor dentro de un conjunto. Todos estos formatos condicionales se utilizan con valores de tipo numérico.

Hemos seleccionado para nuestro ejemplo el conjunto de “IMPORTES” de facturas. Ahora aplicando los conjuntos de iconos tipo “formas” vemos como los valores superiores tienen una “bola verde” y los valores inferiores una “bola roja”.

¿Pero cómo podemos invertir la escala de colores?

Supongamos que nos centramos únicamente en los “Impagados”. Entonces las facturas de mayor importe quiero que se representen con una “bola roja” puesto que me interesa que sean una alerta y me ayuden a poner en marcha alguna acción. Para invertir esta escala de colores haremos clic en “Formato Condicional” y en el submenú “administrar reglas”. Ahí localizamos la regla que se está aplicando al conjunto de datos y podremos “editarla”

Dentro del menú avanzado de la regla tenemos la oportunidad de “Invertir el criterio de ordenación de icono” incluso de elegir los umbrales que deseamos que se apliquen para pintar las alarmas visuales.

Una vez que tenemos aplicados los conjuntos de iconos podemos utilizar el criterio “bola de color” para filtrar los informes. Así por ejemplo podemos recuperar las facturas de mayor importe seleccionando la bola de color rojo.

Formatos condicionales a partir de fórmulas

Bajo determinadas circunstancias puede ser preciso tener un mayor grado de control sobre el formato que deseamos aplicar a los datos. Entonces podremos aplicar un formato condicional basado en una “fórmula” o bien basado en “una función”.

• Aplicar un formato condicional a partir de una fórmula.
• Aplicar un formato condicional a partir de una función.

Para aplicar el formato que deseemos sobre los datos a partir de una fórmula o función es preciso recordar que el formato condicional trabaja internamente a nivel lógico y por tanto el resultado de nuestra fórmula o función debe ser o VERDADERO o FALSO. Respetando esta regla aplicar el formato es muy sencillo.

Aplicar un formato condicional a partir de una fórmula

Partimos de un supuesto en el que contamos con actividades que debemos realizar con una fecha de entrega determinada. Sin embargo no siempre hemos cumplido con la entrega de forma puntual. Vamos a definir un formato condicional a partir de una fórmula para que de una forma visual nos señale en “rojo” fechas que se desviaron de la entrega de la actividad. De esta manera y a modo de alarma visual podremos enseguida ver que actividades se retrasaron. Seleccionaremos las fechas de la columna “Fecha final REAL” son estas fechas las que han de cambiar su color cuando se hubieran desviado de la “Fecha Estimada”.

Dentro del asistente de reglas de formato condicional nos centraremos en la última opción “Utilice una fórmula que determine las celdas para aplicar formato”. En la parte de formato podemos seleccionar los colores de fondo, bordes y colores de letra que deseamos aplicar siempre que el resultado de la evaluación que apliquemos en la “formula” tenga como resultado “VERDADERO”

En el espacio habilitado para aplicar la fórmula de tipo lógico comparamos la fecha de la columna “Fecha final REAL” con la “Fecha Estimada”. Si la fecha de entrega es superior a la fecha estimada entonces el resultado de la evaluación será verdadero y en consecuencia se aplicará el formato condicional.

=F7>E7

Como hemos seleccionado previamente todos los datos de la columna y no hemos hecho uso de referencias entonces Excel aplicará la referencia relativa y comparará línea a línea cada fila para aplicar o no en función del resultado de cada evaluación el formato condicional.

Puedes encontrar algunos ejemplos más en este sitio.

Aplicar un formato condicional a partir de una función

Puedes consultar un ejemplo sobre como aplicar una función en vez de una fórmula para aplicar el formato condicional. En este supuesto pintamos un gráfico tipo Gantt mediante formato condicional.