Formato Condicional Basado en una Fórmula – Excel y Google Sheets

Este tutorial muestra cómo aplicar un formato condicional basado en una fórmula en Excel y Google Sheets.

 

El formato condicional en Excel viene con un montón de reglas preestablecidas para permitirle formatear rápidamente las celdas de acuerdo con su contenido. Sin embargo, estas reglas preestablecidas tienen limitaciones, y es ahí donde entra en juego el uso de una fórmula para determinar el formato de una celda. Esto te da mucha más flexibilidad sobre tu formato condicional.

Reglas de Fórmula de Formato Condicional

Igual a

1. Resalte las celdas en las que desea establecer el formato condicional y, a continuación, en la cinta de opciones, seleccione Inicio > Formato condicional > Nueva regla.

 

2. Seleccione Usar una fórmula para determinar las celdas a las que dar formato, e introduzca la fórmula =$C4=»Oeste»

 

Siempre hay que empezar la fórmula con un signo igual.

La fórmula comprueba si el resultado es VERDADERO o FALSO, como la tradicional sentencia SI de Excel, pero en el caso del formato condicional, no es necesario añadir las condiciones de verdadero y falso. Si la fórmula devuelve VERDADERO, entonces el formato que establezcas en la parte de Formato de la regla se aplica a las celdas relevantes.

Es necesario utilizar una referencia mixta en esta fórmula ($C4) para bloquear la columna (hacerla absoluta) pero hacer la fila relativa. Esto permite que el formato formatee toda la fila en lugar de una sola celda que cumpla los criterios.

Cuando la regla se evalúa para todas las celdas del rango, la fila cambia, pero la columna no. Esto hace que la regla ignore los valores en cualquiera de las otras columnas y sólo se concentre en los valores de la columna C. Si la columna C de esa fila contiene la palabra Oeste, el resultado de la fórmula es VERDADERO, y el formato se aplica para toda la fila.

3. Haz clic en Formato… y selecciona el formato deseado.

 

4. Haz clic en Aceptar y luego en Aceptar de nuevo para ver el resultado.

Las filas en las que la Columna C es Oeste aparecen resaltadas, mientras que otras filas no lo están. En el ejemplo anterior, sólo dos filas acaban resaltadas.

No Igual A

Si desea crear una regla opuesta, puede utilizar el operador <>(no igual a) en lugar de =. =$C4<>»Oeste»

El resultado es el contrario: La mayoría de las filas están ahora resaltadas

Mayor Que

También puede comprobar si es mayor que utilizando una fórmula. Por ejemplo, puede utilizar una fórmula para comprobar si los valores de la columna D son mayores que $26.000. =$D4>26000

 

Esta regla resalta sólo las filas en las que el valor de la columna D es mayor que $26.000.

Mayor Que o Igual A

De manera similar, podría resaltar las filas en las que el valor de la columna D es mayor o igual a $25,999 con esta fórmula de abajo: =$D4>=25999

El resultado de esta fórmula sería ligeramente diferente: Las filas en las que el valor rojo es exactamente $25.999 también se resaltan (aquí, la fila 7).

Menor Que

Cambiando el signo > por el signo < se comprueban los valores inferiores a $26.000. =$D4<26000

 

Ahora sólo se resaltan las filas en las que el rojo es inferior a $26.000.

 

Menor o Igual Que

Para cambiar la prueba para incluir el valor objetivo (por ejemplo, $22.500), utilice <=. =$E4<=22500

 

Ahora, cualquier fila en la que la columna E sea igual a $22.500 también se resalta.

 

Crear Reglas con O, Y

Fórmula O

Puede crear una regla más complicada incorporando la función O de Excel para ampliar la fórmula y buscar dos o más valores.

1. Introduce la fórmula: =OR($C4=»Oeste»;$C4=»Este»)

 

2. Haz clic en el botón Formato… y selecciona el formato que desees, y luego haz clic en Aceptar para volver a Excel.

Esto resalta todas las filas en las que la Región es Oeste o Este.

Fórmula Y

Del mismo modo, puede crear una regla utilizando Y. Esto significaría comprobar los valores de dos o más columnas.

La fórmula anterior comprueba si el texto de la columna C es Oeste y el valor de la fila correspondiente de la columna D es superior a $23.000.

Esta regla de formato condicional resalta sólo las filas en las que se cumplen ambas condiciones.

Fórmula SI

No tiene que añadir necesariamente una declaración SI a la fórmula cuando la crea en una regla de formato condicional, ya que el formato condicional siempre va a aplicar la regla si la fórmula que ha creado devuelve un valor verdadero. Así, por ejemplo, esta fórmula: =SI($C4=»Oeste»;VERDADERO;FALSO) y esta fórmula =$C4=»Oeste» ambas aplican el mismo formato condicional a su hoja de cálculo.

 

Funciones Incorporadas de Excel

Muchas funciones incorporadas de Excel pueden utilizarse en el formato condicional, como ESBLANCO, ESERROR, ESPAR, ESNUMERO, etc.

Función ESBLANCO

Puede utilizar la función ESBLANCO para crear una regla para encontrar filas que contengan celdas en blanco o vacías en la hoja de trabajo.

1. Resalta el rango y luego, en la cinta de opciones, selecciona Inicio > Formato condicional > Nueva regla.
2. Escriba la fórmula =ESBLANCO($E3)

 

3. Haz clic en el botón Formato… y selecciona el formato deseado y luego haz clic en Aceptar para volver a Excel.

 

ESBLANCO con O

Puede ampliarlo para incluir celdas en blanco o celdas con un error.

1. Escriba la fórmula =O(ESBLANCO($E3);ESERROR($E3))

2. Haz clic en el botón Formato... y selecciona el formato que desees y luego haz clic en Aceptar para volver a Excel.

 

Otras Funciones en el Formato Condicional

Hay una variedad de otras funciones que se pueden utilizar en la creación de reglas basadas en fórmulas de formato condicional.

• CONTAR.SI.CONJUNTO – cuenta las celdas que cumplen una determinada condición (útil para resaltar los duplicados)
• K.ESIMO.MAYOR – encuentra el k-ésimovalor más grande
• MAX – encuentra el número más grande
• MIN – encuentra el número más pequeño
• RESIDUO – devuelve el resto después de la división (útil para resaltar cada dos filas)
• NO – cambia VERDADERO a FALSO y viceversa
• AHORA – devuelve la fecha y hora actuales (útil para formatear condicionalmente fechas y horas)
• FILA – devuelve el número de filas de una matriz
• HALLAR – busca una cadena de texto dentro de otra cadena (útil para encontrar un texto específico)
• HOY – devuelve la fecha actual (útil para aplicar un formato condicional a las fechas)
• BUSCARV – devuelve el resultado de una búsqueda vertical
• DIASEM – devuelve el día de la semana
• BUSCARX – una nueva y más potente función de búsqueda

Por ejemplo, puede utilizar la función MAX para resaltar la fila que contiene el valor máximo en una columna especificada. =$E4=MAX($E:$E)

Ahora, sólo se resalta la fila con el valor máximo en la columna E.

Si tiene problemas para conseguir que una fórmula funcione al crear una regla de formato condicional, es una buena idea probar la fórmula primero en su hoja de Excel. Una vez que la tengas ahí, podrás incorporarla a tu regla.

 

Formato Condicional Basado en una Fórmula en Google Sheets

El formato condicional funciona de forma muy similar en Google Sheets que en Excel.

1. Resalte las celdas en las que desea establecer el formato condicional y luego, en el Menú, seleccione Formato > Formato condicional.

 

2. Asegúrate de que la opción Aplicar al rango es correcta y, a continuación, selecciona Fórmula personalizada en el cuadro desplegable Reglas de formato. Escribe la fórmula.

 

3. A continuación, selecciona el estilo de formato y haz clic en Listo.

También puedes utilizar mayor que, mayor o igual que, menor que y menor o igual que en Google Sheets. Y y O también funcionan, así como las demás funciones enumeradas anteriormente para Excel (excepto BUSCARX, que no existe en Google Sheets).