Formato condicional en Excel: qué es y cómo se usa

El formato condicional de Excel pinta las celdas automáticamente según el valor que contienen o según una fórmula. En vez de colorear a mano una por una, escribes una regla y Excel se ocupa de aplicarla y de actualizarla cuando los datos cambian. En este tutorial trabajarás con una hoja de Notas y una hoja de Ventas; al terminar sabrás resaltar aprobados y suspensos, ver los datos con barras y mapas de calor, marcar tendencias con iconos y crear reglas con tu propia fórmula. Está disponible en todas las versiones de Excel desde 2007, en Excel para la web y en Excel para Mac.

Qué es el formato condicional y para qué te sirve

Imagina la hoja de Notas de tu clase: cinco alumnos con sus notas finales en la columna B. Quieres ver de un vistazo quién ha aprobado y quién no, sin repasar la columna número a número. Con el formato condicional escribes una vez "pinta en verde lo que sea mayor o igual que 5 y en rojo lo que sea menor que 5" y Excel se encarga del resto, incluso cuando añades un alumno nuevo.

La regla mira cada celda del rango que le indiques, compara su valor con la condición y aplica el formato (relleno, color de letra, borde, icono o barra) solo si se cumple. Si el valor cambia, el formato cambia con él. Si borras el valor, el formato desaparece.

Las reglas viven en la propia hoja, no en las celdas: cuando guardas el libro, las guardas con él. Y como están en un único lugar, modificarlas es cambiar una vez y afecta a todas las celdas que las cumplen.

Dónde está y cómo se aplica

El formato condicional está en la pestaña Inicio, dentro del grupo Estilos. Al desplegar el botón principal ves el menú con todas las opciones listas para usar: Resaltar reglas de celdas, Reglas superiores e inferiores, Barras de datos, Escalas de color, Conjuntos de iconos, Nueva regla, Borrar reglas y Administrar reglas.

Las cinco primeras opciones son reglas rápidas: eliges el tipo, escribes la condición y Excel aplica un formato por defecto. Nueva regla abre el cuadro de diálogo con los seis tipos de regla y los formatos personalizables. Administrar reglas muestra el panel lateral con todas las reglas de la hoja, su orden, su alcance y un botón para borrarlas.

Las cinco formas de ver los datos de un vistazo

Nueva regla y el Administrador de reglas

Cuando las reglas rápidas no te bastan, abres Nueva regla y ves los seis tipos disponibles: Aplicar formato a todas las celdas (para usar una escala, una barra o un conjunto de iconos con tus umbrales), Aplicar formato únicamente a las celdas que contengan (condiciones sobre el valor: mayor que, entre, igual a, texto, fechas, duplicados), Aplicar formato únicamente a los valores con rango superior o inferior (los 10 mejores, el 10 % inferior, por encima o por debajo del promedio), Dar formato a los valores únicos o duplicados, Dar formato únicamente a las celdas que cumplan una fórmula, y Aplicar formato en función de los valores de otras celdas.

En el menú Inicio > Estilos > Formato condicional > Administrar reglas se abre el Administrador de reglas de formato condicional, un panel donde ves cada regla, su tipo, el rango donde se aplica (Se aplica a), un botón Editar regla para cambiar la condición o el formato, dos flechas para subir y bajar el orden (importante cuando varias reglas se solapan) y un botón Eliminar regla.

Resaltar valores con una regla rápida

La hoja Notas tiene cinco alumnos con su nota final. Vas a aplicar dos reglas rápidas sobre la misma columna: aprobados en verde y suspensos en rojo.

Resaltar celdas que sean mayores o menores que un número

1. Selecciona B2:B6.
2. Abre Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Es mayor que.
3. Escribe 7 en el cuadro de la izquierda.
4. En la lista desplegable de la derecha, elige Formato personalizado. En la pestaña Relleno escoge verde claro; en la pestaña Fuente escoge verde oscuro. Acepta.
5. Vuelve a abrir el menú y elige Es menor que, escribe 5 y en formato personalizado pon relleno rojo claro y letra rojo oscuro.

Marcar celdas que contengan un texto o una fecha concreta

Las reglas rápidas también sirven para textos y fechas. Imagina que en A2:A6 hay cinco tareas y una está pendiente.

1. Selecciona A2:A6. Aplica Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Texto que contiene.
2. Escribe pendiente, en formato personalizado pon relleno amarillo y letra marrón oscuro. Acepta.
3. Selecciona B2:B6. Aplica la regla Una fecha posterior a y en el desplegable elige Hoy.
4. En formato personalizado pon relleno naranja y letra blanca. Acepta.

Resaltar duplicados y únicos

Sobre la columna A de la hoja Tareas, una regla rápida pilla los repetidos de un vistazo. Creas dos "Aucía" en A2 y A7 y aplicas Duplicar valores.

1. Selecciona A2:A7.
2. Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Duplicar valores.
3. En la lista desplegable elige Formato personalizado con relleno rojo claro y letra rojo oscuro. Acepta.

Ver los datos con barras, colores e iconos

Las reglas rápidas colorean; las de la parte de abajo del menú enseñan el valor de otra forma: con una barra, con un degradado o con un icono. La hoja Ventas tiene cinco productos con su importe.

Barras de datos para comparar a simple vista

1. Selecciona B2:B6.
2. Inicio > Estilos > Formato condicional > Barras de datos > Relleno degradado azul.

Escalas de color como mapa de calor

1. Selecciona B2:B6.
2. Inicio > Estilos > Formato condicional > Escalas de color > Escala de 2 colores: blanco y azul.

Con 2 colores y con 3 colores

En la escala de 2 colores el mínimo es un color y el máximo, otro. Si los datos tienen valores extremos, el degradado queda muy estirado y los valores medios se ven casi iguales. La escala de 3 colores añade un punto medio para resolver esto: por ejemplo, rojo claro en el mínimo, amarillo en 25 y verde claro en el máximo. En el cuadro de Nueva regla > Aplicar formato a todas las celdas > Escala de 2 o 3 colores puedes elegir los colores y los valores mínimo, medio y máximo. Con los datos de Ventas, la barra de 3 colores marca la zona media en amarillo y separa mejor los importes de 75 y 180 que la escala de 2 colores.

Conjuntos de iconos para marcar tendencia

1. Selecciona B2:B6.
2. Inicio > Estilos > Formato condicional > Conjuntos de iconos > 3 flechas (de color).

Crear una regla con tu propia fórmula

Las reglas rápidas comparan el valor de la celda con un número, un texto o una fecha. Con la regla Usar una fórmula puedes comparar el valor de la celda con OTRA celda: la celda que contiene el objetivo.

La hoja Ventas añade una columna C con el vendedor y la celda C7 con el objetivo del mes, 250.

Nueva regla con fórmula que compara con una celda fija

1. Selecciona B2:B6.
2. Inicio > Estilos > Formato condicional > Nueva regla.
3. Elige Usar una fórmula que determine las celdas para aplicar formato.
4. En el cuadro de la fórmula escribe =B2>=$C$7.
5. Pulsa Formato, escoge relleno verde en la pestaña Relleno y Acepta.
6. Acepta la regla.

=B2>=$C$7

Pintar toda la fila según una columna

Para pintar la fila entera, el rango Se aplica a tiene que cubrir todas las columnas, y la fórmula tiene que tener el signo $ delante de la columna que contiene la condición.

1. Selecciona A2:D6.
2. Abre Nueva regla > Usar una fórmula y escribe =$C2>=$C$7.
3. Formato con relleno verde. Acepta.

=$C2>=$C$7

La regla se escribe pensando en la primera celda

La fórmula siempre se evalúa desde la celda superior izquierda del rango Se aplica a. Para esa celda, B2, la fórmula =B2>=$C$7 es verdadera o falsa. Cuando la regla se copia a B3, Excel ajusta la referencia a =B3>=$C$7, y así sucesivamente. Por eso C7 lleva los dos $: $C$7 es referencia absoluta y no se mueve al copiar.

Por qué la regla no se aplica o se aplica a medias

Estas son las cuatro causas más frecuentes por las que una regla con fórmula no pinta lo que esperas.

La fórmula se aplica al rango equivocado

El cuadro de Nueva regla muestra al final el campo Se aplica a. Si ahí pone $B$2:$B$6 y la fórmula compara con C7, la regla solo mira la columna B. Si el rango es B2:D6, la regla se evalúa celda a celda en las tres columnas y la referencia B2 se desplaza a C2 y D2 al moverse por la fila, con resultados inesperados.

En el Administrador de reglas, edita la regla y corrige Se aplica a. Para pintar toda la fila, escribe $A$2:$D$6 y pon el $ delante de la columna en la fórmula ($C2>=$C$7).

La regla no salta con valores vacíos ni con errores

Si una celda del rango tiene un error como #N/A, la fórmula se evalúa para esa celda y la regla puede dejar de aplicarse a todo el rango, sobre todo si la condición depende de BUSCARV o de un valor que no existe. Para blindar la regla, envuelve la parte que puede fallar en SI.ERROR y devuelve un valor neutro (0 o "N/A").

=SI.ERROR(B2>=$C$7;FALSO)

Borrar formato condicional sin tocar los datos

Para quitar todas las reglas de la hoja: Inicio > Estilos > Formato condicional > Borrar reglas > Borrar reglas de toda la hoja. Para quitar una sola, ábrela en el Administrador de reglas y pulsa Eliminar regla. El valor de la celda y su formato manual se quedan como estaban.

Pegar encima quita o arrastra el formato

Al copiar una fila con Ctrl+C y pegarla con Ctrl+V, el formato condicional se pega con la fila. Al pegar con Pegado especial (Ctrl+Alt+V) y elegir Formatos, a veces se pegan solo los formatos manuales y las reglas se quedan a medio camino. La opción Formatos condicionales del Pegado especial, cuando aparece, pega únicamente las reglas; si no está, lo más seguro es pegar solo valores y volver a crear la regla sobre la fila nueva.

Lo que el formato condicional no hace (o hace con cuidado)

Muchas reglas sobre rangos enormes ralentizan el libro

Cada regla se recalcula al cambiar un valor del rango al que se aplica. Con cientos de miles de filas y varias reglas por columna, el libro abre y guarda más lento. Si tu rango va a crecer, conviértelo en Tabla de Excel con Insertar > Tablas > Tabla: el rango de la regla se ajusta solo al añadir filas, y las fórmulas usan referencias estructuradas como =Ventas[Importe]>=Ventas[[#Encabezados];[Objetivo]].

En informes de tabla dinámica no todo funciona en el área Valores

Las reglas de duplicados y únicos se aplican solo sobre el origen, no sobre el resultado de una tabla dinámica. Cuando abres Nueva regla sobre el área Valores de una tabla dinámica, la lista de tipos de regla se reduce: solo verás escalas de color, barras de datos y conjuntos de iconos. El campo Aplicar regla de formato a permite elegir entre el rango seleccionado, todas las celdas de Ventas de Importe, el campo Importe, el campo Producto, etc. Si después mueves un campo de la tabla dinámica, las reglas pueden dejar de aplicarse: revísalas en el panel Formato condicional > Administrar reglas.

El formato cambia cómo SE VE el valor, no el valor

Una celda con formato 0,00 muestra 3,14 pero guarda 3,14159. Lo mismo pasa con el formato condicional: la celda con 7,5 y regla Mayor que 7 se pinta, pero su valor real es 7,5. Si necesitas redondear de verdad para una suma o un promedio, usa la función REDONDEAR (=REDONDEAR(B2;2)). Las funciones SI, SUMAR.SI o PROMEDIO.SI reciben el valor tal cual está guardado, no el que se ve.

Lo que ya sabes hacer con el formato condicional

• Resaltar con dos clics las celdas mayores o menores que un número, con la regla rápida Es mayor que o Es menor que.
• Marcar los 10 superiores o el 10 % inferior con Reglas superiores e inferiores, sin escribir fórmula.
• Ver los importes a simple vista con Barras de datos, sobre todo en una columna de 10 a 20 valores.
• Convertir una columna de importes en un mapa de calor con Escalas de color, de 2 o de 3 colores.
• Comparar con una celda fija (objetivo, umbral, media) con la regla Usar una fórmula y referencias absolutas ($C$7) y, si conviene, mixtas ($C2) para pintar filas enteras.

Siguiente paso lógico: combinar el formato condicional con la validación de datos para que el lector solo pueda escribir en la columna los valores que después se pintan (por ejemplo, una lista desplegable de categorías y, a la vez, una regla que pinta de rojo los importes negativos).

Dudas al usar el formato condicional en Excel