Formato condicional con fórmulas: resalta filas enteras, fechas vencidas y duplicados
Selecciona el rango completo empezando en la fila 2 (por ejemplo A2:E8) y ve a Inicio › Formato condicional › Nueva regla... › Utilizar una fórmula que determine las celdas para aplicar formato. Escribe la fórmula pensando solo en la fila 2, con $ delante de la columna. Para facturas pendientes ya vencidas: =Y($D2="Pendiente";HOY()>$E2). Pulsa Formato..., elige un relleno y Aceptar.
Las reglas rápidas de formato condicional pintan una celda según su propio valor. Pero en la vida real quieres otra cosa: que se ponga en rojo toda la fila de una factura cuando está pendiente y ya ha vencido. Eso depende de dos columnas a la vez, y ninguna regla del menú lo hace.
La solución es una regla con fórmula. Piensa en ella como una pregunta de sí o no que Excel se hace en cada fila: «¿esta factura está pendiente y vencida?». Si la respuesta es VERDADERO, aplica el formato; si es FALSO, no hace nada.
La única dificultad son los símbolos $. En esta guía entiendes por qué $E2 funciona y $E$2 no, y montas tres reglas que usarás cada semana: vencidas, próximas a vencer y duplicadas.
Qué vas a aprender
- La regla de oro: escribir la fórmula pensando en la primera fila del rango
- Por qué se usa $E2 (columna fija, fila libre) para pintar filas enteras
- Resaltar facturas vencidas o que vencen en los próximos 7 días con HOY
- Marcar duplicados con
=CONTAR.SI($A$2:$A$8;$A2)>1 y ordenar las reglas
Paso a paso
Entiende la idea: una pregunta que Excel repite en cada fila
Seleccionas un rango, escribes una fórmula que sea verdadera o falsa para la primera fila y Excel la copia mentalmente a las demás, igual que cuando arrastras una fórmula hacia abajo. Donde da VERDADERO, pinta.
En el ejemplo, un registro de facturas: columna D con el estado y columna E con el vencimiento. Suponemos que hoy es 10/10/2026.
Selecciona todo el rango, empezando por A2
Haz clic en A2 y arrastra hasta E8. No incluyas los encabezados. Fíjate en que la celda activa (la blanca dentro de la selección, la que aparece en el cuadro de nombres) es A2: la fórmula que escribas se interpreta desde esa celda.
Si seleccionas de abajo arriba, la celda activa será A8 y tendrás que escribir la fórmula para la fila 8. Lo más seguro es seleccionar siempre desde la esquina superior izquierda.
Abre Inicio › Formato condicional › Nueva regla...
En la pestaña Inicio, grupo Estilos, abre Formato condicional y elige Nueva regla.... Se abre el cuadro Nueva regla de formato.
Elige el tipo «fórmula» y escribe la regla de vencidas
En Seleccionar un tipo de regla, elige la última opción: Utilizar una fórmula que determine las celdas para aplicar formato. En el cuadro Dar formato a los valores donde esta fórmula sea verdadera, escribe:
=Y($D2="Pendiente";$E2<HOY())
Se lee así: «el estado de esta fila es Pendiente y su vencimiento es anterior a hoy». La función Y solo devuelve VERDADERO si se cumplen las dos condiciones, y HOY devuelve la fecha del día.
VERDADERO si la factura de esa fila está pendiente y su vencimiento ya pasó.
Pulsa Formato... y elige un relleno
Haz clic en Formato.... Se abre una versión reducida de Formato de celdas, con solo cuatro pestañas: Número, Fuente, Bordes y Relleno. En Relleno, elige un rojo suave en Color de fondo. Pulsa Aceptar dos veces.
Elige colores claros: el texto debe seguir leyéndose bien, también al imprimir en blanco y negro.
Comprueba el resultado y entiende el $
Las filas de F-1022 y F-1025 se pintan enteras: están pendientes y vencidas. F-1024 también venció, pero está pagada, así que no se marca.
¿Por qué toda la fila? Porque el $ fija la columna. Excel evalúa la fórmula en cada celda, y en todas ellas mira la columna E de su propia fila:
| Escribes | Qué pasa |
|---|---|
$E2 | Columna fija, fila libre: pinta filas enteras. Correcto. |
E2 | En la columna B miraría F, en la C miraría G… Solo se pinta alguna celda suelta. |
$E$2 | Todas las filas miran la misma celda: o se pinta todo, o nada. |
Añade una segunda regla: vence en los próximos 7 días
Con el mismo rango A2:E8 seleccionado, repite Nueva regla... con esta fórmula y un relleno amarillo:
=Y($D2="Pendiente";$E2>=HOY();$E2-HOY()<=7)
Restar dos fechas da los días que hay entre ellas. Aquí se marcan las dos filas de F-1023, que vence dentro de 4 días. F-1026 queda en blanco: faltan 21.
VERDADERO si está pendiente y vence hoy o en los próximos 7 días.
Si escribes los días en una celda (por ejemplo H1) y usas $E2-HOY()<=$H$1, podrás cambiar el plazo sin tocar la regla.
Marca los números de factura duplicados con CONTAR.SI
Selecciona solo A2:A8 y crea otra regla con fórmula:
=CONTAR.SI($A$2:$A$8;$A2)>1
Aquí el rango donde se cuenta va totalmente fijo ($A$2:$A$8), porque siempre es la lista entera; lo que cambia en cada fila es el valor que se busca ($A2). Si aparece más de una vez, se pinta. F-1023 está registrada dos veces.
Cuenta cuántas veces aparece el número de esta fila en la lista; si es más de una, lo marca.
Ordena y revisa las reglas en Administrar reglas
Ve a Inicio › Formato condicional › Administrar reglas... y, en Mostrar reglas de formato para, elige Esta hoja. Verás tus tres reglas con su fórmula y su rango en Se aplica a. Usa las flechas para cambiar el orden: si dos reglas dan formatos que chocan en la misma celda, gana la de arriba. La casilla Detener si es verdad impide que se apliquen las reglas de debajo cuando una se cumple.
Trucos de experto
- Prueba la fórmula en una celda. Escribe
=Y($D2="Pendiente";$E2<HOY())enF2y arrástrala hacia abajo. Donde veasVERDADEROse pintará. Cuando funcione, cópiala a la regla y borra la columna auxiliar. - Filas alternas sin tabla. La regla
=RESIDUO(FILA();2)=0pinta las filas pares: el clásico «papel pijama» que facilita leer listados largos. - Fines de semana en un calendario. Si la fecha está en la columna
A,=DIASEM($A2;2)>5marca sábados y domingos. - Pídele la regla a la IA, pero con contexto. Dile a ChatGPT: «fórmula de formato condicional para el rango A2:E8, Excel en español con punto y coma, pinta la fila si D es Pendiente y E es anterior a hoy». Luego compruébala con la columna auxiliar. Más en pedir fórmulas a la IA.
- ¿Dudas con los $? Repásalos en referencias relativas y absolutas: es exactamente la misma lógica.
Errores comunes y cómo arreglarlos
Haces clic en una celda mientras escribes la fórmula y Excel inserta $E$2.
Al hacer clic, Excel pone la referencia absoluta. Quita el $ de la fila para que quede $E2; si no, se pinta todo el rango o nada.
Los colores aparecen desplazados una fila.
La fórmula no corresponde a la celda activa. Si el rango empieza en A2, la fórmula debe hablar de la fila 2 ($E2), no de la 1.
Al pulsar las flechas dentro del cuadro de la fórmula aparecen referencias raras.
El cuadro está en modo Introducir y las flechas seleccionan celdas. Pulsa F2 para pasar al modo Modificar y moverte por el texto.
La regla de fechas no marca nada aunque haya facturas vencidas.
Las fechas están guardadas como texto. Comprueba que se alinean a la derecha y conviértelas en fechas reales.
En Excel para Mac el camino es el mismo: Inicio › Formato condicional › Nueva regla. Elige el estilo Clásico y la opción de usar una fórmula para determinar qué celdas formatear; la fórmula, con sus $ y sus funciones, es idéntica.
Preguntas frecuentes
¿Puedo usar referencias de tabla como Facturas[Vence] en la regla?
No directamente: las fórmulas de formato condicional no aceptan referencias estructuradas. Usa referencias normales como $E2; si el rango es una tabla, las filas nuevas heredan la regla.
Mi Excel da error con el punto y coma. ¿Qué hago?
Tu configuración regional usa la coma como separador (pasa en México y otros países). Escribe =Y($D2="Pendiente",$E2<HOY()); los nombres de función siguen en español.
¿Los colores se actualizan solos cada día?
Sí. HOY se recalcula al abrir el libro y cada vez que Excel recalcula, así que una factura pasa de amarillo a rojo sin que hagas nada.
¿Funciona en versiones antiguas de Excel?
Sí. Las reglas con fórmula, Y, HOY y CONTAR.SI funcionan desde Excel 2007 y también en Excel para la web.
Fuentes oficiales
Documentación de Microsoft consultada para esta guía.
Se guarda solo en este navegador.
Sigue aprendiendo
Formato condicional: que Excel resalte solo lo importante
Aprende a usar el formato condicional de Excel para resaltar ventas, duplicados y valores clave con colores, barras e iconos. Paso a paso, en 5 minutos.
Referencias relativas y absolutas ($A$1): por qué tu fórmula se rompe al arrastrarla
Fija una celda con $ para que tu fórmula no se descuadre al arrastrarla. Referencias relativas, absolutas y mixtas, con la tecla F4, paso a paso.
Fechas en Excel: HOY, SIFECHA y DIAS.LAB para calcular edades, plazos y vencimientos
Calcula vencimientos, edades, antigüedad y días laborables en Excel con HOY, SIFECHA, DIAS.LAB y DIA.LAB. Fórmulas listas para copiar, paso a paso.
Varias condiciones a la vez: SI.CONJUNTO, Y, O
Combina condiciones en Excel con Y, O y SI.CONJUNTO: bonus, envíos gratis, comisiones por tramos y aprobación de gastos. Paso a paso con ejemplos reales.
SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO: sumar y contar con condiciones
Aprende a sumar y contar solo las filas que cumplen varias condiciones (región, comercial, fechas, importes) con SUMAR.SI.CONJUNTO. Paso a paso, con ejemplos.