Fórmulas de formato condicional de Excel basadas en otra celda

Ejemplos de fórmulas de formato condicional de Excel

Ahora que sabe cómo crear y aplicar formato condicional de Excel basado en otra celda, sigamos adelante y veamos cómo usar varias fórmulas de Excel en la práctica.

Fórmulas para comparar valores (números y texto)

Como sabe, Microsoft Excel proporciona un puñado de reglas listas para usar para formatear celdas con valores mayores, menores o iguales al valor que especifique (Formato condicional >Reglas de resaltado de celdas). Sin embargo, estas reglas no funcionan si desea formatear condicionalmente ciertas columnas o filas enteras. basado en el valor de una celda en otra columna. En este caso, utiliza fórmulas análogas:

CondiciónEjemplo de fórmulaIgual a=$B2=10No igual a=$B2<>10Mas grande que=$B2>10Mayor qué o igual a=$B2>=10Menos que=$B2<10Menos que o igual a=$B2<=10Entre=AND($B2>5, $B2<10)

La siguiente captura de pantalla muestra un ejemplo de la Mayor que la fórmula que resalta los nombres de los productos en la columna A si el número de artículos en stock (columna C) es mayor que 0. Preste atención a que la fórmula se aplica solo a la columna A ($A$2:$A$8). Pero si selecciona toda la tabla (en nuestro caso, $A$2:$E$8), se resaltarán filas enteras según el valor de la columna C.

>De manera similar, puedes crear una regla de formato condicional para comparar valores de dos celdas. Por ejemplo:

=$A2<$B2 – formatear celdas o filas si un valor en la columna A es menor que el valor correspondiente en la columna B.

=$A2=$B2 – formatee celdas o filas si los valores de las columnas A y B son iguales.

=$A2<>$B2 – formatear celdas o filas si un valor en la columna A no es el mismo que en la columna B.

Como puede ver en la captura de pantalla siguiente, estas fórmulas funcionan tanto para valores de texto como para números.

>Fórmulas Y y O

Si desea formatear su tabla de Excel según 2 o más condiciones, utilice la función =Y o =O:

CondiciónFórmulaDescripciónSi se cumplen ambas condiciones=AND($B2<$C2, $C2<$D2)Da formato a las celdas si el valor en la columna B es menor que en la columna C, y si el valor de la columna C es menor que el de la columna D.Si se cumple una de las condiciones=OR($B2<$C2, $C2<$D2)Da formato a las celdas si el valor en la columna B es menor que en la columna C, o si el valor de la columna C es menor que el de la columna D.

En la siguiente captura de pantalla, usamos la fórmula =AND($C2>0, $D2=»Worldwide») para cambiar el color de fondo de las filas si la cantidad de artículos en stock (Columna C) es mayor que 0 y si el producto se envía a todo el mundo (Columna D). Preste atención a que la fórmula funciona con valores de texto así como con números.

>Naturalmente, puedes utilizar dos, tres o más condiciones en tus fórmulas AND y OR. Para ver cómo funciona esto en la práctica, vea el vídeo: Formato condicional basado en otra celda.

Estas son las fórmulas básicas de formato condicional que utiliza en Excel. Ahora consideremos ejemplos un poco más complejos pero mucho más interesantes.

Formato condicional para celdas vacías y no vacías

Creo que todo el mundo sabe cómo formatear celdas vacías y no vacías en Excel; simplemente crea una nueva regla de «Formatee sólo las celdas que contengan» escriba y elija espacios en blanco o Sin espacios en blanco.

>Pero, ¿qué sucede si desea formatear las celdas de una determinada columna si la celda correspondiente de otra columna está vacía o no? En este caso, necesitarás volver a utilizar fórmulas de Excel:

Fórmula para espacios en blanco: =$B2=»» – formatee las celdas/filas seleccionadas si una celda correspondiente en la columna B está en blanco.

Fórmula para no espacios en blanco: =$B2<>»» – formatee las celdas/filas seleccionadas si la celda correspondiente en la columna B no está en blanco.

Y aquí hay un ejemplo de cómo se pueden utilizar las fórmulas anteriores en la práctica. Supongamos que tiene una columna (B) que es «Fecha de venta» y otra columna (C) «Entrega«. Estas 2 columnas tienen un valor solo si se realizó una venta y se entregó el artículo. Por lo tanto, desea que toda la fila se vuelva naranja cuando haya realizado una venta; y cuando se entregue un artículo, la fila correspondiente debería volverse naranja. verde Para lograr esto, necesita crear 2 reglas de formato condicional con las siguientes fórmulas:

• Filas naranjas (una celda en la columna B no está vacía): =$B2<>»»
• Filas verdes (las celdas de las columnas B y C no están vacías): =AND($B2<>»», $C2<>»»)

Una cosa más que puedes hacer es mover la segunda regla a la parte superior y seleccionar la Detener si es cierto casilla de verificación junto a esta regla:

>En este caso particular, la opción «Detener si es verdadero» es en realidad superflua y la regla funcionará con o sin ella. Es posible que desee marcar esta casilla solo como precaución adicional, en caso de que agregue algunas otras reglas en el futuro que puedan entrar en conflicto con cualquiera de las existentes.

Para obtener más información, consulte Formato condicional de Excel para celdas en blanco.

Fórmulas de Excel para trabajar con valores de texto.

Si desea dar formato a determinadas columnas cuando otra celda de la misma fila contiene una determinada palabra, puede utilizar una fórmula analizada en uno de los ejemplos anteriores (como =$D2=»Mundial»). Sin embargo, esto sólo funcionará para coincidencia exacta.

Para coincidencia parcialdeberá utilizar BUSCAR (no distingue entre mayúsculas y minúsculas) o ENCONTRAR (distingue entre mayúsculas y minúsculas).

Por ejemplo, para formatear celdas o filas seleccionadas si una celda correspondiente en la columna D contiene la palabra «Mundial«, use la siguiente fórmula. Esta fórmula encontrará todas esas celdas, independientemente de dónde se encuentre el texto especificado en una celda, incluido «Envíos a cualquier parte del mundo«, «En todo el mundo, excepto…«, etc:

=SEARCH(«Worldwide», $D2)>0

Si desea sombrear las celdas o filas seleccionadas si el contenido de la celda comienza con el texto de búsqueda, use este:

=SEARCH(«Worldwide», $D2)>1

>Fórmulas de Excel para resaltar duplicados.

Si su tarea es dar formato condicional a celdas con valores duplicados, puede utilizar la regla predefinida disponible en Formato condicional > Reglas de resaltado de celdas > Valores duplicados… El siguiente artículo proporciona una guía detallada sobre cómo utilizar esta función: Cómo resaltar automáticamente duplicados en Excel.

Sin embargo, en algunos casos los datos se ven mejor si colorea columnas seleccionadas o filas enteras cuando se producen valores duplicados en otra columna. En este caso, necesitará volver a emplear una fórmula de formato condicional de Excel, y esta vez usaremos la CONTAR.SI fórmula. Como sabes, esta función de Excel cuenta la cantidad de celdas dentro de un rango específico que cumplen con un único criterio.

Resalte duplicados, incluido 1calle ocurrencias

=COUNTIF($A$2:$A$10,$A2)>1 – esta fórmula encuentra valores duplicados en el rango especificado en la Columna A (A2:A10 en nuestro caso), incluidas las primeras apariciones.

Si elige aplicar la regla a toda la tabla, se formatearán todas las filas, como se ve en la captura de pantalla siguiente. He decidido cambiar un color de fuente en esta regla, sólo para variar 🙂

>Resaltar duplicados sin 1calle ocurrencias

Para ignorar la primera aparición y resaltar solo los valores duplicados posteriores, utilice esta fórmula: =COUNTIF($A$2:$A2,$A2)>1

>Resalte duplicados consecutivos en Excel

Si prefiere resaltar solo los duplicados en filas consecutivas, puede hacerlo de la siguiente manera. Este método funciona para cualquier tipo de datos: números, valores de texto y fechas.

• Seleccione la columna donde desea resaltar duplicados, sin el encabezado de la columna.
• Cree una(s) regla(s) de formato condicional usando estas fórmulas simples:
  Regla 1 (azul): =$A1=$A2 – resalta los 2Dakota del Norte ocurrencia y todas las ocurrencias posteriores, si las hubiere.
  Regla 2 (verde): =$A2=$A3 – resalta el 1calle ocurrencia.

En las fórmulas anteriores, A es la columna que desea verificar en busca de duplicados, $A1 es el encabezado de la columna, $A2 es la primera celda con datos.

¡Importante! Para que las fórmulas funcionen correctamente, es fundamental que la Regla 1, que destaca las 2Dakota del Norte y todas las apariciones duplicadas posteriores, debe ser la primera regla de la lista, especialmente si utiliza dos colores diferentes.

>Resaltar filas duplicadas

Si desea aplicar el formato condicional cuando se producen valores duplicados en dos o más columnas, deberá agregar una columna adicional a su tabla en la que concatene los valores de las columnas clave usando una fórmula simple como esta. =A2&B2. Después de eso, aplica una regla usando cualquier variación de la fórmula CONTAR.SI para duplicados (con o sin 1calle sucesos). Naturalmente, puedes ocultar una columna adicional después de crear la regla.

>Alternativamente, puede usar la función CONTAR.SI que admite múltiples criterios en una sola fórmula. En este caso, no necesitarás una columna auxiliar.

En este ejemplo, para resaltar filas duplicadas con primeras aparicionescrea una regla con la siguiente fórmula:
=COUNTIFS($A$2:$A$11, $A2, $B$2:$B$11, $B2)>1

Para resaltar filas duplicadas sin primeras aparicionesutiliza esta fórmula:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1

Compare 2 columnas para duplicados

Una de las tareas más frecuentes en Excel es comprobar 2 columnas en busca de valores duplicados, es decir, buscar y resaltar valores que existen en ambas columnas. Para hacer esto, necesitará crear una regla de formato condicional de Excel para cada columna con una combinación de =ISERROR() y =MATCH() funciones:

Para la columna A: =ISERROR(MATCH(A1,$B$1:$B$10000,0))=FALSE

Para la columna B: =ISERROR(MATCH(B1,$A$1:$A$10000,0))=FALSE

Puede ver un ejemplo de uso práctico en la siguiente captura de pantalla que resalta los duplicados en las columnas E y F.

>Como puede ver, las fórmulas de formato condicional de Excel se adaptan bastante bien a los engaños. Sin embargo, para casos más complejos, recomendaría utilizar el complemento Duplicate Remover que está especialmente diseñado para buscar, resaltar y eliminar duplicados en Excel, en una hoja o entre dos hojas de cálculo.

Fórmulas para resaltar valores por encima o por debajo del promedio

Cuando trabaja con varios conjuntos de datos numéricos, el PROMEDIO() La función puede resultar útil para formatear celdas cuyos valores están por debajo o por encima del promedio en una columna.

Por ejemplo, puedes usar la fórmula =$E2<AVERAGE($E$2:$E$8) para formatear condicionalmente las filas donde los números de venta están por debajo del promedio, como se muestra en la captura de pantalla a continuación. Si busca lo contrario, es decir, matizar los productos con un rendimiento superior al promedio, reemplace «<» por «>» en la fórmula: =$E2>AVERAGE($E$2:$E$8).

>Cómo resaltar el valor más cercano en Excel

Si tengo un conjunto de números, ¿hay alguna manera de usar el formato condicional de Excel para resaltar el número de ese conjunto más cercano a cero? Esto es lo que quería saber una de las lectoras de nuestro blog, Jessica. La pregunta es muy clara y directa, pero la respuesta es demasiado larga para las secciones de comentarios, por eso ves una solución aquí 🙂

Ejemplo 1. Encuentre el valor más cercano, incluida la coincidencia exacta

En nuestro ejemplo, buscaremos y resaltaremos el número más cercano a cero. Si el conjunto de datos contiene uno o más ceros, se resaltarán todos. Si no hay 0, se resaltará el valor más cercano a él, ya sea positivo o negativo.

En primer lugar, debe ingresar la siguiente fórmula en cualquier celda vacía de su hoja de trabajo; podrá ocultar esa celda más adelante, si es necesario. La fórmula encuentra el número en un rango determinado que es más cercano al número que especifica y devuelve el valor absoluto de ese número (el valor absoluto es el número sin su signo):

=MIN(ABS(B2:D13-(0)))

En la fórmula anterior, B2:D13 es su rango de celdas y 0 es el número para el que desea encontrar la coincidencia más cercana. Por ejemplo, si busca un valor más cercano a 5, la fórmula cambiará a: =MIN(ABS(B2:D13-(5)))

Y ahora, crea una regla de formato condicional con la siguiente fórmula, donde B3 es la celda superior derecha de su rango y $C$2 en la celda con la fórmula matricial anterior:

=OR(B3=0-$C$2,B3=0+$C$2)

Preste atención al uso de referencias absolutas en la dirección de la celda que contiene la fórmula matricial ($C$2), porque esta celda es constante. Además, debe reemplazar 0 con el número cuya coincidencia más cercana desea resaltar. Por ejemplo, si quisiéramos resaltar el valor más cercano a 5, la fórmula cambiaría a: =OR(B3=5-$C$2,B3=5+$C$2)

>Ejemplo 2. Resalte un valor más cercano al valor dado, pero NO una coincidencia exacta

En caso de que no desee resaltar la coincidencia exacta, necesitará una fórmula matricial diferente que encuentre el valor más cercano pero ignore la coincidencia exacta.

Por ejemplo, la siguiente fórmula matricial encuentra el valor más cercano a 0 en el rango especificado, pero ignora los ceros, si los hay:

=MIN(ABS(B3:C13-(0))+(10^0*(B3:C13=0)))

Por favor recuerde presionar Ctrl + Mayús + Intro después de que haya terminado de escribir su fórmula matricial.

La fórmula de formato condicional es la misma que en el ejemplo anterior:

=OR(B3=0-$C$2,B3=0+$C$2)

Sin embargo, dado que nuestra fórmula matricial en la celda C2 ignora la coincidencia exacta, la regla de formato condicional también ignora los ceros y resalta el valor 0,003 que es la coincidencia más cercana.

>Si desea encontrar el valor más cercano a algún otro número en su hoja de Excel, simplemente reemplace «0» con el número que desea tanto en las fórmulas de formato matricial como condicional.

Espero que las fórmulas de formato condicional que has aprendido en este tutorial te ayuden a entender cualquier proyecto en el que estés trabajando. Si necesita más ejemplos, consulte los siguientes artículos: