Formato condicional con fórmulas (10 ejemplos)

El formato condicional es una forma fantástica de visualizar rápidamente datos en una hoja de cálculo. Con el formato condicional, puede hacer cosas como resaltar fechas en los próximos 30 días, marcar problemas de ingreso de datos, resaltar filas que contienen los principales clientes, mostrar duplicados y más.

Excel se envía con una gran cantidad de 'ajustes preestablecidos' que facilitan la creación de nuevas reglas sin fórmulas. Sin embargo, también puede crear reglas con sus propias fórmulas personalizadas. Al usar su propia fórmula, asume la condición que activa una regla y puede aplicar exactamente la lógica que necesita. Las fórmulas le brindan la máxima potencia y flexibilidad.

Por ejemplo, usando el ajuste preestablecido 'Igual a', es fácil resaltar celdas iguales a 'manzana'.

Pero, ¿qué pasa si desea resaltar celdas iguales a 'manzana' o 'kiwi' o 'lima'? Claro, puede crear una regla para cada valor, pero eso es un gran problema. En su lugar, puede simplemente usar una regla basada en una fórmula con la Función OR :

Aquí está el resultado de la regla aplicada al rango B4: F8 en esta hoja de cálculo:

Aquí está la fórmula exacta utilizada:

Inicio rápido

Puede crear una regla de formato condicional basada en fórmulas en cuatro sencillos pasos:

1. Seleccione las celdas a las que desea dar formato.

2. Cree una regla de formato condicional y seleccione la opción Fórmula

3. Ingrese una fórmula que devuelva VERDADERO o FALSO.

4. Configure las opciones de formato y guarde la regla.

los Función ISODD solo devuelve VERDADERO para números impares, lo que activa la regla:

fórmula de índice en Excel con ejemplo

Video: Cómo aplicar formato condicional con una fórmula

Lógica de la fórmula

Las fórmulas que aplican formato condicional deben devolver VERDADERO o FALSO, o equivalentes numéricos. Aquí hay unos ejemplos:

cómo calcular el pago de su hipoteca en Excel

Todas las fórmulas anteriores devuelven VERDADERO o FALSO, por lo que funcionan perfectamente como desencadenante del formato condicional.

Cuando se aplica formato condicional a un rango de celdas, ingrese las referencias de celda con respecto a la primera fila y columna de la selección (es decir, la celda superior izquierda). El truco para comprender cómo funcionan las fórmulas de formato condicional es visualizar la misma fórmula que se aplica a cada celda de la selección , con las referencias de celda actualizadas como de costumbre. Imagine que ingresó la fórmula en la celda superior izquierda de la selección y luego copió la fórmula en toda la selección. Si tiene problemas con esto, consulte la sección sobre Fórmulas ficticias debajo.

Ejemplos de fórmulas

A continuación, se muestran ejemplos de fórmulas personalizadas que puede utilizar para aplicar formato condicional. Algunos de estos ejemplos se pueden crear utilizando los ajustes preestablecidos integrados de Excel para resaltar celdas, pero las fórmulas personalizadas pueden ir mucho más allá de los ajustes preestablecidos, como puede ver a continuación.

Destacar pedidos de Texas

Para resaltar filas que representan pedidos de Texas (abreviado TX), use una fórmula que bloquee la referencia a la columna F:

Para más detalles, consulte este artículo: Resaltar filas con formato condicional .

Video: Cómo resaltar filas con formato condicional

Resaltar fechas en los próximos 30 días

Para resaltar las fechas que ocurren en los próximos 30 días, necesitamos una fórmula que (1) asegure que las fechas estén en el futuro y (2) se asegure de que las fechas sean 30 días o menos a partir de hoy. Una forma de hacer esto es usar el Y función junto con la Función AHORA como esto:

Con una fecha actual del 18 de agosto de 2016, el formato condicional destaca las fechas de la siguiente manera:

los Función AHORA devuelve la fecha y hora actuales. Para obtener detalles sobre cómo funciona esta fórmula, consulte este artículo: Resaltar fechas en los próximos N días .

Resaltar las diferencias de columna

Dadas dos columnas que contienen información similar, puede usar formato condicional para detectar diferencias sutiles. La fórmula utilizada para activar el formato a continuación es:

Ver también: una versión de esta fórmula que usa la función EXACTA para hacer una comparación sensible a mayúsculas y minúsculas .

Resalta los valores perdidos

Para resaltar valores en una lista que faltan en otra, puede usar una fórmula basada en la Función COUNTIF :

Esta fórmula simplemente verifica cada valor en Lista A contra valores en el rango con nombre 'lista' (D5: D10). Cuando el recuento es cero, la fórmula devuelve VERDADERO y activa la regla, que resalta los valores en Lista A que faltan en Lista B .

Video: Cómo encontrar valores perdidos con COUNTIF

Resalte las propiedades con más de 3 dormitorios por menos de $ 350k

Para buscar propiedades en esta lista que tengan al menos 3 dormitorios pero menos de $ 300,000, puede usar una fórmula basada en la función AND:

Los signos de dólar ($) bloquean la referencia a las columnas C y D, y el Y función se utiliza para asegurarse de que ambas condiciones sean VERDADERAS. En las filas donde la función Y devuelve VERDADERO, se aplica el formato condicional:

Resaltar los valores superiores (ejemplo dinámico)

Aunque Excel tiene ajustes preestablecidos para 'valores superiores', este ejemplo muestra cómo hacer lo mismo con una fórmula y cómo las fórmulas pueden ser más flexibles. Al usar una fórmula, podemos hacer que la hoja de trabajo sea interactiva: cuando se actualiza el valor en F2, la regla responde instantáneamente y resalta los nuevos valores.

La fórmula utilizada para esta regla es:

congelar las 3 filas superiores en Excel

Donde 'datos' es el rango con nombre B4: G11 y 'entrada' es el rango con nombre F2. Esta pagina tiene detalles y una explicación completa .

diagramas de Gantt

Lo crea o no, incluso puede usar fórmulas para crear diagramas de Gantt simples con formato condicional como este:

Esta hoja de trabajo usa dos reglas, una para las barras y otra para el sombreado de fin de semana:

Este artículo explica la fórmula de las barras. , y este artículo explica la fórmula para el sombreado de fin de semana .

Cuadro de búsqueda simple

Un truco interesante que puede hacer con el formato condicional es crear un cuadro de búsqueda simple. En este ejemplo, una regla resalta las celdas de la columna B que contienen texto escrito en la celda F2:

La fórmula utilizada es:

Para obtener más detalles y una explicación completa, consulte:

• Artículo: Cómo resaltar celdas que contienen texto específico
• Artículo: Cómo resaltar filas que contienen texto específico
• Video: Cómo crear un cuadro de búsqueda para resaltar datos

Solución de problemas

Si no puede hacer que sus reglas de formato condicional se activen correctamente, lo más probable es que haya un problema con su fórmula. Primero, asegúrese de comenzar la fórmula con un signo igual (=). Si olvida este paso, Excel convertirá silenciosamente toda su fórmula a texto, dejándola inútil. Para solucionarlo, simplemente elimine las comillas dobles que Excel agregó a cada lado y asegúrese de que la fórmula comience con igual (=).

Si su fórmula se ingresó correctamente, pero no activa la regla, es posible que deba profundizar un poco más. Normalmente, puede usar la tecla F9 para verificar los resultados en una fórmula o usar la función Evaluar para recorrer una fórmula. Desafortunadamente, no puede usar estas herramientas con fórmulas de formato condicional, pero puede usar una técnica llamada 'fórmulas ficticias'.

Fórmulas ficticias

Las fórmulas ficticias son una forma de probar sus fórmulas de formato condicional directamente en la hoja de trabajo, para que pueda ver lo que realmente están haciendo. Esto puede ser un gran ahorro de tiempo cuando tiene dificultades para que las referencias de celda funcionen correctamente.

En pocas palabras, ingresa la misma fórmula en un rango de celdas que coincide con la forma de sus datos. Esto le permite ver los valores devueltos por cada fórmula, y es una excelente manera de visualizar y comprender cómo funciona el formato condicional basado en fórmulas. Para una explicación detallada, ver este articulo .

Video: Pruebe el formato condicional con fórmulas ficticias

Limitaciones

Existen algunas limitaciones que vienen con el formato condicional basado en fórmulas:

1. No puede aplicar iconos, escalas de colores o barras de datos con una fórmula personalizada. Está limitado al formato de celda estándar, incluidos los formatos numéricos, la fuente, el color de relleno y las opciones de borde.
2. No puede utilizar determinadas construcciones de fórmulas como uniones, intersecciones o constantes de matriz para los criterios de formato condicional.
3. No puede hacer referencia a otros libros de trabajo en una fórmula de formato condicional.

A veces puede trabajar en torno a los números 2 y 3. Es posible que pueda mover la lógica de la fórmula a una celda de la hoja de trabajo y luego hacer referencia a esa celda en la fórmula. Si está intentando utilizar una constante de matriz, intente crear un rango con nombre en su lugar.

Más recursos sobre fórmulas de FQ

• Más de 30 ejemplos de fórmulas de formato condicional
• Entrenamiento en video con hojas de trabajo de práctica