Formato Condicional de Excel con Fórmulas: Visualiza Datos Automáticamente

El formato condicional convierte hojas de cálculo estáticas en dashboards visuales. Pero cuando usas fórmulas en lugar de reglas simples, desbloqueas un formato poderoso y dinámico que se adapta a tus datos. Esta guía te enseña a construir reglas de formato condicional basadas en fórmulas con escenarios empresariales reales — sin teoría, solo patrones prácticos que puedes copiar y adaptar.

Consejo: Sigue con tus propios datos. La mayoría de los ejemplos funcionan con cualquier conjunto de datos que tenga filas y columnas.

1) ¿Qué es el Formato Condicional Basado en Fórmulas? (Contexto Rápido)

El formato condicional basado en fórmulas te permite aplicar reglas de formato usando fórmulas de Excel. En lugar de "resaltar celdas mayores que 1000", puedes escribir "resaltar celdas mayores que el promedio" o "resaltar celdas donde el valor en la columna B coincide con un valor en la columna D".

¿Por qué usar fórmulas en formato condicional?

• Dinámico: Las reglas se adaptan automáticamente cuando cambian los datos
• Flexible: Compara entre columnas, filas o rangos completos
• Poderoso: Combina múltiples condiciones con lógica Y/O
• Reutilizable: Una regla funciona para columnas o tablas completas

Cuándo usar reglas basadas en fórmulas:

• Resaltar valores por encima/debajo del promedio
• Comparar valores entre columnas
• Marcar duplicados o valores únicos
• Formato basado en fechas (vencidos, próximos, dentro del rango)
• Escenarios complejos con múltiples condiciones

2) Tu Primera Regla con Fórmula: Resaltar por Encima del Promedio

🎯 Escenario: Tienes datos de ventas y quieres resaltar valores por encima del promedio.

Configuración de Datos:

• Columna A: Nombres de productos
• Columna B: Montos de ventas

Paso a Paso:

1. Selecciona tu rango de datos: Haz clic y arrastra para seleccionar B2:B7 (o tu columna de ventas)
2. Abre Formato Condicional:
   • Ve a Inicio → Formato Condicional → Nueva Regla
   • Elige "Usar una fórmula para determinar qué celdas se formatean"
3. Ingresa la fórmula:
   • En el cuadro de fórmula, escribe: =B2>PROMEDIO($B$2:$B$7)
   • Haz clic en Formato → elige un color de relleno (p. ej., verde)
   • Haz clic en Aceptar dos veces

Resultado: Las celdas con ventas por encima del promedio se resaltan en verde.

Cómo funciona:

• B2 es relativo (cambia para cada celda)
• $B$2:$B$7 es absoluto (siempre se refiere al rango completo)
• Excel evalúa la fórmula para cada celda en la selección

Error Común: Si usas B2>PROMEDIO(B2:B7) sin signos de dólar, Excel ajustará el rango para cada celda, causando resultados incorrectos. Siempre usa referencias absolutas ($) para el rango en PROMEDIO, SUMA, etc.

Mini ejercicio: Crea una regla que resalte valores por debajo del promedio en rojo.

3) Comparar Entre Columnas: Resaltar Discrepancias

🎯 Escenario: Tienes ventas en la columna B y objetivos en la columna C. Resalta donde las ventas no cumplen los objetivos.

Configuración de Datos:

• Columna B: Ventas Reales
• Columna C: Ventas Objetivo

Regla con Fórmula:

1. Selecciona B2:B7
2. Nueva Regla → Usar una fórmula
3. Fórmula: =B2<C2
4. Formato: Relleno rojo

Resultado: Las celdas en la columna B se resaltan en rojo cuando las ventas reales están por debajo de los objetivos.

Por qué funciona:

• B2 y C2 son ambos relativos
• Excel compara B2 con C2, B3 con C3, etc.
• Cada fila se evalúa independientemente

Variación — resaltar cuando las ventas superan los objetivos en un 10%:

=B2>C2*1.1

Error Común: Asegúrate de que ambas columnas tengan el mismo número de filas. Si la columna C tiene menos filas, Excel comparará B2 con una celda vacía, lo que se evalúa como FALSO.

Mini ejercicio: Agrega una tercera columna "Estado" y resalta filas donde Estado es "Vencido" Y Ventas < Objetivo.

4) Resaltar Filas Completas Basado en Una Columna

🎯 Escenario: Resalta filas completas donde las ventas son superiores a $4000.

Configuración de Datos:

• Columnas A-E: Producto, Ventas, Estado, Crecimiento, Tendencia
• Quieres resaltar la fila completa, no solo la columna de ventas

Paso a Paso:

1. Selecciona el rango completo de datos: A2:E7 (incluye todas las columnas)
2. Nueva Regla → Usar una fórmula
3. Fórmula: =$B2>4000
4. Formato: Relleno verde claro

Resultado: Las filas completas se resaltan cuando la columna B (Ventas) supera 4000.

Cómo funciona:

• $B2 bloquea la columna (B) pero permite que la fila cambie
• $B2 evalúa B2 para la fila 2, B3 para la fila 3, etc.
• El signo de dólar antes de B asegura que siempre revisemos la columna B

Patrones comunes:

• Resaltar filas donde la columna A es igual a "Laptop": =$A2="Laptop"
• Resaltar filas donde la columna C es "Vencido": =$C2="Vencido"
• Resaltar filas donde la columna B está por encima del promedio: =$B2>PROMEDIO($B$2:$B$7)

Error Común: Si olvidas el signo de dólar antes de la columna (B2 en lugar de $B2), Excel revisará columnas diferentes para cada celda, causando resaltado incorrecto.

Mini ejercicio: Resalta filas completas donde Crecimiento es superior a 50 Y Ventas es superior a 3000.

5) Formato Basado en Fechas: Resaltar Tareas Vencidas

🎯 Escenario: Tienes una lista de tareas con fechas de vencimiento. Resalta tareas que están vencidas (pasadas la fecha de hoy).

Configuración de Datos:

• Columna A: Nombres de tareas
• Columna B: Fechas de vencimiento

Regla con Fórmula:

1. Selecciona A2:B10 (o tu rango de tareas)
2. Nueva Regla → Usar una fórmula
3. Fórmula: =$B2<HOY()
4. Formato: Relleno rojo

Resultado: Las filas con fechas de vencimiento en el pasado se resaltan en rojo.

Variaciones:

• Resaltar tareas vencidas en los próximos 7 días: =Y($B2>=HOY(), $B2<=HOY()+7)
• Resaltar tareas vencidas hoy: =$B2=HOY()
• Resaltar tareas vencidas Y no completadas: =Y($B2<HOY(), $C2<>"Hecho")

Cómo funciona:

• HOY() devuelve la fecha actual
• Las fechas en Excel se almacenan como números (días desde el 1 de enero de 1900)
• Las comparaciones funcionan directamente: fechas anteriores son números más pequeños

Error Común: Si las fechas se almacenan como texto (importadas desde CSV), las comparaciones no funcionarán. Conviértelas a fechas reales primero usando FECHANUMERO() o reformateando las celdas.

Mini ejercicio: Crea una regla que resalte tareas vencidas en 3 días en amarillo, y tareas vencidas en rojo (usa dos reglas separadas).

6) Resaltar Duplicados (o Valores Únicos)

🎯 Escenario: Tienes una lista de IDs de pedidos y quieres resaltar duplicados.

Configuración de Datos:

• Columna A: IDs de Pedidos

Regla con Fórmula:

1. Selecciona A2:A100 (tu rango de IDs de pedidos)
2. Nueva Regla → Usar una fórmula
3. Fórmula: =CONTAR.SI($A$2:$A$100, A2)>1
4. Formato: Relleno naranja

Resultado: Todos los IDs de pedidos duplicados se resaltan.

Cómo funciona:

• CONTAR.SI($A$2:$A$100, A2) cuenta cuántas veces aparece A2 en el rango
• Si el conteo es mayor que 1, el valor es un duplicado
• El rango es absoluto ($A$2:$A$100) para que no cambie
• El valor de búsqueda (A2) es relativo, por lo que revisa cada celda

Resaltar solo valores únicos:

=CONTAR.SI($A$2:$A$100, A2)=1

Resaltar duplicados en múltiples columnas:

=CONTAR.SI($A$2:$E$100, A2)>1

Error Común: CONTAR.SI no distingue entre mayúsculas y minúsculas. "ABC" y "abc" se consideran duplicados. Usa EXACTO() si necesitas comparación que distinga mayúsculas y minúsculas.

Mini ejercicio: Resalta nombres de clientes duplicados en la columna C, pero solo si sus ventas (columna B) son superiores a 2000.

7) Valores Superiores/Inferiores N con Fórmulas

🎯 Escenario: Resalta los 3 valores de ventas más altos.

Configuración de Datos:

• Columna B: Montos de ventas

Regla con Fórmula:

1. Selecciona B2:B7
2. Nueva Regla → Usar una fórmula
3. Fórmula: =B2>=K.ESIMO.MAYOR($B$2:$B$7, 3)
4. Formato: Relleno verde

Resultado: Los 3 valores más altos se resaltan.

Cómo funciona:

• K.ESIMO.MAYOR($B$2:$B$7, 3) devuelve el 3er valor más grande
• Si B2 es mayor o igual a este valor, está en los 3 primeros
• Usa K.ESIMO.MENOR($B$2:$B$7, 3) para los 3 inferiores

Resaltar el 10% superior:

=B2>=PERCENTIL($B$2:$B$7, 0.9)

Resaltar valores por encima del percentil 90:

=B2>PERCENTIL($B$2:$B$7, 0.9)

Error Común: K.ESIMO.MAYOR y K.ESIMO.MENOR ignoran texto y errores. Asegúrate de que tu rango contenga solo números, o envuelve con SI.ERROR().

Mini ejercicio: Resalta el 20% inferior de valores de ventas en rojo, y el 20% superior en verde (usa dos reglas).

8) Múltiples Condiciones: Lógica Y

🎯 Escenario: Resalta filas donde Ventas > 3000 Y Crecimiento > 50.

Configuración de Datos:

• Columna B: Ventas
• Columna D: Crecimiento

Regla con Fórmula:

1. Selecciona A2:E7 (filas completas)
2. Nueva Regla → Usar una fórmula
3. Fórmula: =Y($B2>3000, $D2>50)
4. Formato: Relleno azul

Resultado: Las filas que cumplen ambas condiciones se resaltan.

Patrones de lógica Y:

• Tres condiciones: =Y($B2>3000, $D2>50, $C2="Alto")
• Rango de fechas: =Y($B2>=FECHA(2025,1,1), $B2<=FECHA(2025,12,31))
• Texto y número: =Y($A2="Laptop", $B2>4000)

9) Múltiples Condiciones: Lógica O

🎯 Escenario: Resalta filas donde Ventas > 4000 O Crecimiento > 70.

Regla con Fórmula:

1. Selecciona A2:E7
2. Nueva Regla → Usar una fórmula
3. Fórmula: =O($B2>4000, $D2>70)
4. Formato: Relleno amarillo

Resultado: Las filas que cumplen cualquiera de las condiciones se resaltan.

Patrones de lógica O:

• Múltiples opciones: =O($A2="Laptop", $A2="Tablet", $A2="Monitor")
• Texto o número: =O($C2="Vencido", $B2<1000)
• O complejo: =O($B2>5000, Y($B2>3000, $D2>60))

Combina Y y O:

=O(Y($B2>4000, $D2>50), $C2="Crítico")

Esto resalta filas donde (Ventas > 4000 Y Crecimiento > 50) O Estado es "Crítico".

Error Común: Los paréntesis importan en fórmulas complejas. O(A, Y(B, C)) es diferente de Y(O(A, B), C). Prueba tu lógica con datos de muestra.

Mini ejercicio: Resalta filas donde Ventas está en los 3 primeros O Crecimiento es superior a 70.

10) Formato Dinámico: Resaltar Basado en Otra Hoja

🎯 Escenario: Tienes una lista maestra de productos de alta prioridad en Hoja2. Resalta productos coincidentes en Hoja1.

Configuración de Datos:

• Hoja1, Columna A: Nombres de productos
• Hoja2, Columna A: Lista de productos de alta prioridad

Regla con Fórmula:

1. Selecciona Hoja1!A2:A100
2. Nueva Regla → Usar una fórmula
3. Fórmula: =CONTAR.SI(Hoja2!$A$2:$A$10, A2)>0
4. Formato: Relleno verde

Resultado: Los productos en Hoja1 que existen en la lista de prioridad de Hoja2 se resaltan.

Cómo funciona:

• CONTAR.SI(Hoja2!$A$2:$A$10, A2) verifica si A2 existe en Hoja2
• Si el conteo > 0, el producto está en la lista de prioridad
• Las referencias de hoja funcionan entre hojas

Comparación entre hojas:

=A2=Hoja2!B5

Compara A2 en la hoja actual con B5 en Hoja2.

Error Común: Si renombras Hoja2, la fórmula se rompe. Usa INDIRECTO() con una referencia de celda para el nombre de la hoja si necesitas referencias de hoja dinámicas.

11) Barras de Datos e Iconos con Fórmulas

Aunque las barras de datos y los iconos no usan fórmulas directamente, puedes combinarlos con reglas basadas en fórmulas para visualizaciones poderosas.

Escenario: Aplicar barras de datos solo a valores por encima del promedio.

Paso a Paso:

1. Selecciona B2:B7
2. Formato Condicional → Barras de Datos → elige un color
3. Administrar Reglas → Edita la regla
4. Marca "Detener si es verdadero" y agrega una nueva regla encima:
   • Fórmula: =B2<=PROMEDIO($B$2:$B$7)
   • Formato: Sin formato (o relleno blanco)
   • Marca "Detener si es verdadero"

Resultado: Solo los valores por encima del promedio muestran barras de datos.

Iconos con condiciones:

1. Crea una columna auxiliar con una fórmula: =SI(B2>4000, 1, SI(B2>2000, 0, -1))
2. Aplica iconos a la columna auxiliar
3. Oculta la columna auxiliar si es necesario

Poniéndolo Todo Junto — Un Ejemplo Completo de Dashboard

Crea un dashboard de ventas con múltiples reglas de formato condicional:

Configuración:

• Columna A: Producto
• Columna B: Ventas
• Columna C: Objetivo
• Columna D: Crecimiento %

Reglas:

1. Resaltar filas donde Ventas > Objetivo: =$B2>$C2 (Verde)
2. Resaltar filas donde Ventas < Objetivo: =$B2<$C2 (Rojo)
3. Resaltar las 3 ventas principales: =$B2>=K.ESIMO.MAYOR($B$2:$B$7, 3) (Borde azul)
4. Resaltar Crecimiento > 70%: =$D2>70 (Relleno amarillo)
5. Resaltar vencido (si agregas fechas): =$E2<HOY() (Naranja)

Aplica todas las reglas a A2:D7 y ajusta los colores de formato para crear un dashboard visual.

Lista de Verificación Rápida (Antes de Compartir tu Hoja)

• Todas las referencias de rango usan referencias absolutas ($) donde sea necesario
• Las fórmulas están probadas con datos de muestra
• Las reglas no entran en conflicto (revisa el orden en Administrar Reglas)
• Rendimiento: Demasiadas reglas en rangos grandes pueden ralentizar Excel
• Los colores son accesibles (no solo rojo/verde para usuarios daltónicos)
• Las reglas están documentadas (agrega comentarios o una hoja separada)

Resumen de Errores Comunes

1. Referencias Relativas vs Absolutas: Usa $B$2 para rangos, B2 para verificaciones de celda
2. Texto vs Números: Las fechas almacenadas como texto no se compararán correctamente
3. Celdas Vacías: CONTAR.SI trata las celdas vacías como 0, lo que puede afectar los resultados
4. Distinción entre Mayúsculas y Minúsculas: CONTAR.SI no distingue entre mayúsculas y minúsculas; usa EXACTO() si es necesario
5. Orden de Reglas: Excel aplica reglas de arriba hacia abajo; usa "Detener si es verdadero" para prevenir conflictos
6. Rendimiento: Limita reglas en rangos muy grandes (10,000+ celdas)

Conclusión

El formato condicional basado en fórmulas transforma Excel de una calculadora en un dashboard visual. Comienza con comparaciones simples (B2>C2), luego agrega complejidad (Y/O, promedios, percentiles). La clave es entender referencias relativas vs absolutas — una vez que domines eso, puedes construir cualquier regla de formato.

Recuerda: Prueba tus fórmulas con datos de muestra antes de aplicar a rangos grandes. Un signo de dólar incorrecto puede resaltar toda la hoja incorrectamente.

Si quieres práctica práctica con formato condicional y fórmulas, prueba los ejercicios en la aplicación — cada escenario refuerza estos patrones con datos empresariales reales.