Cómo usar el formato condicional basado en otra celda en Google Sheets

A veces es necesario examinar montones de datos en una hoja de cálculo, a menudo en busca de determinados valores. Sin embargo, sin elementos de diseño o señales visuales, la búsqueda de datos puede llevar mucho tiempo y ser difícil para la vista. Afortunadamente, con Hojas de cálculo de Google puede utilizar el formato condicional para cambiar el color de las celdas que busca en función del valor de la celda. Esta funcionalidad se denomina formato condicional. Se puede hacer basándose en la celda individual, o basándose en otra celda. Te mostraré cómo funciona con la ayuda de algunos ejemplos.

Diferentes tipos de formato condicional

Ejemplo 1: Identificar las casillas por debajo de un umbral

Empezaré con un ejemplo básico utilizando formato condicional para cambiar el color de una celda. Consideremos la siguiente hoja de cálculo. El supervisor del almacén, en una estación de servicio de automóviles local, la utiliza para realizar un seguimiento de un inventario de piezas de repuesto.

Debe hacer un nuevo pedido de un producto si la cantidad en existencias (columna B) es inferior al umbral de 20. Hay un gran número de productos y los valores de la columna B cambian con el tiempo. El número de productos es elevado y los valores de la columna B varían con el tiempo. Por lo tanto, le resulta difícil identificar los productos que se agotan. Incluso si coloreara las celdas manualmente para poder pescarlas fácilmente más tarde, eso duplicaría su trabajo: ¡no es la solución ideal!

La funcionalidad de formato condicional viene al rescate, con la que puedes cambiar los colores de las celdas en función del valor de la celda en Google Sheets. Para aplicar este formato, primero selecciona todas las celdas de la columna B.

Ahora navegue hasta Formato > Formato condicional. Se abre una barra lateral en la parte derecha de la pantalla. Observará que en la barra Aplicar al intervalo el valor ya está fijado en B2:B27. Esto se debe a que ese rango ya está seleccionado. En cualquier caso, si desea cambiar el rango, puede hacerlo.

Elegiré Inferior o igual a en el Formatear celdas si campo desplegable. A continuación, introduce el valor 20 en el campo inmediatamente siguiente. Básicamente, está configurando Google Sheets para que formato de celdas de una determinada manera si cumple esta condición. Elegiré un estilo de Formato que sea rojo para que se adapte a la situación. Para terminar, basta con hacer clic en el botón Hecho botón

Esta configuración se realiza una sola vez y actualiza automáticamente el formato aunque se produzcan cambios futuros en los datos.

En este ejemplo he utilizado una condición directa. También puedes cambiar los colores de las celdas haciendo uso de una fórmula. Para ello, sólo tiene que asegurarse de que el valor en el Formatear celdas si El campo desplegable es La fórmula personalizada es, y, a continuación, introduzca la fórmula en el campo siguiente.

Ejemplo 2: Formato condicional basado en otra celda

Ahora que ya sabes cómo usar el formato condicional, veamos cómo cambiar el color de una celda basándonos en el valor de otra celda diferente. Utilizaré el mismo ejemplo que antes. Esta vez, sin embargo, el supervisor del almacén quiere que se resalte el ID del Producto en lugar de la Cantidad.

Para ello, siga los mismos pasos que antes, pero seleccione La fórmula personalizada es del Formatear celdas si campo desplegable. A continuación, escriba la fórmula basada en la celda de la columna B. En este ejemplo, he escrito =B2<=20.

Ahora he conseguido el mismo resaltado de celdas con bajo inventario, pero se resalta el ID del producto en lugar de la cantidad.

Ejemplo 3: Formateo con varias reglas

No tiene por qué limitarse a una sola regla cuando utilice el formato condicional. Añadir varias reglas de formato condicional puede ayudar a diseccionar visualmente los datos aún más.

En este ejemplo, un profesor de quinto curso quiere destacar a los alumnos cuyas notas están por debajo o por encima de un umbral. En concreto, el profesor quiere que los alumnos con una media inferior a 70 aparezcan en rojo, y los alumnos en la franja de 90, en verde.

Para ello, debe añadir dos reglas distintas. El proceso para cada regla es muy similar al del ejemplo anterior.

Como desea que los nombres se resalten en función de las puntuaciones medias, resalte los nombres como el Aplicar al intervalo, y añada las puntuaciones escribiendo la regla en el campo La fórmula personalizada es de la sección Formatear celdas si campo desplegable

Una vez que haya escrito la primera regla, pulse Hecho y luego Añadir otra regla. Las fórmulas que utilicé en las reglas aquí son =F2<70 and ="F2">=90.

Ejemplo 4: Formato basado en varias celdas

También puede utilizar el formato condicional basado en múltiples celdas, todo dentro de una regla de formato. Para ello, puede utilizar las funciones OR o AND.

En este ejemplo, el profesor quiere tener en cuenta cada asignatura individual en lugar de la media. Si un alumno tiene una nota inferior a 60 en cualquier asignatura, su nombre debe resaltarse en rojo para que el profesor pueda ver rápidamente quién tiene dificultades.

En este caso, el método es similar al de los otros ejemplos, ¡espero que ya te hayas acostumbrado! La diferencia es que en el La fórmula personalizada es de la sección Formatear celdas si campo desplegable, tipo =OR(B2<60,C2<60,D2<60,E2<60).

Cambiar el color de una celda según el valor de otra celda

Para cambiar el color de una celda según el valor de otra celda, usa una fórmula personalizada que apunte a esa otra celda. Resulta útil cuando trabajas con datos que dependen mucho unos de otros o con muchos datos dinámicos. Aquí tienes un segundo ejemplo, con una lista de comerciales. Supongamos que quiero resaltar en rojo los nombres del equipo de ventas que tienen menos de 10 clientes. En este ejemplo, el formato de las celdas del equipo de ventas depende del número de clientes.

1. Selecciona el intervalo de celdas al que quieres dar formato (aquí, los nombres de A2:B45) y ve a Formato > Formato condicional.

2. En "Reglas de formato", selecciona "La fórmula personalizada es" en el menú desplegable. Añade la fórmula que crea la condición correcta y recuerda hacer referencia a la otra celda. En este ejemplo, mi fórmula será =C2<10, escrita para la primera fila del intervalo. Como ves, la fórmula hace referencia a la columna "Number of clients".

3. Cambia las preferencias de "Estilo de formato" y haz clic en "Hecho".

4. Como ves, los nombres de mi equipo de ventas que tienen menos de 10 clientes ahora aparecen resaltados en rojo.

Resaltar una fila entera según una celda

Para colorear toda la fila y no solo el nombre, selecciona todas las columnas de la tabla (por ejemplo, A2:D45) en Aplicar al intervalo y añade un signo de dólar delante de la letra de la columna: =$C2<10. El $ hace que todas las celdas de la fila miren la columna C, así que cada fila se vuelve roja cuando su propio número de clientes es inferior a 10.

Cómo usar una fórmula personalizada en el formato condicional

Usar una fórmula personalizada en el formato condicional es realmente útil. Te permite crear reglas más personalizadas que dan exactamente el resultado que buscas, y además sirve para otros objetivos importantes, como verás más adelante en este artículo. Puedes usar distintas funciones, como la fórmula IF (SI) para el formato condicional, que te ayudan con objetivos más complejos. Primero, veamos cómo usar la fórmula personalizada para crear reglas de formato.

En este ejemplo, quiero resaltar en naranja todas las ventas totales entre 4.000 $ y 8.000 $. Para hacerlo rápido, basta con una fórmula AND (Y) sencilla.

1. Selecciona el intervalo de datos al que quieres dar formato y ve a Formato > Formato condicional.

2. En el menú desplegable de "Reglas de formato", selecciona "La fórmula personalizada es". En el campo de abajo, escribe la fórmula que necesitas para tu objetivo. En este caso, para resaltar las ventas totales entre 4.000 $ y 8.000 $, tengo que añadir la siguiente fórmula AND: =AND(D1>4000,D1<8000).

3. En "Estilo de formato", indica el formato de las celdas que cumplen tu regla. En este ejemplo, lo pondré en naranja. Cuando termines, haz clic en "Hecho".

Como ves a continuación, todas las ventas totales entre 4.000 $ y 8.000 $ tienen el fondo de celda en naranja.

Referencias relativas y absolutas en una fórmula personalizada

Google Sheets lee una fórmula personalizada como si estuviera escrita para la primera celda de Aplicar al intervalo, y luego la ajusta para cada una de las demás celdas, igual que cambia una fórmula cuando la copias hacia abajo en una columna. Por eso los ejemplos de esta página usan =B2 o =C2 y no una columna entera.

El signo de dólar bloquea la parte de una referencia que no debe moverse:

• =$C2 fija la columna y deja que cambie la fila. Úsala para colorear filas enteras.
• =C$2 fija la fila y deja que cambie la columna.
• =$C$2 hace que todas las celdas apunten a la misma celda, como un valor objetivo que escribes una sola vez.

Tampoco necesitas una función IF dentro de una fórmula personalizada. La regla se aplica siempre que la fórmula devuelve TRUE, así que =D2>5000 hace lo mismo que =IF(D2>5000,TRUE,FALSE). Nuestra guía de funciones IF en Google Sheets explica los casos en los que IF resulta útil. Las fórmulas personalizadas también pueden marcar entradas repetidas con COUNTIF, como se explica en cómo resaltar duplicados en Google Sheets.

Cómo usar el formato condicional con varias condiciones

A veces querrás aplicar más de una condición a tus datos. El formato condicional de Google Sheets te permite crear y aplicar varias condiciones a tus datos con mucha facilidad. En este ejemplo, supongamos que quiero resaltar dos cosas: el comercial con más ventas totales y el que tiene menos. Así puedes comparar dos columnas de datos a la vez. Para ello, también necesitamos una fórmula personalizada para cada regla. Primero, creamos la condición del total de ventas máximo. Después, creamos otra condición para el total de ventas mínimo.

1. Selecciona el intervalo de celdas al que quieres dar formato y ve a Formato > Formato condicional.

2. Crea tu primera regla con la fórmula personalizada. En este ejemplo, configuramos la condición del total de ventas máximo con la fórmula personalizada =D:D=max(D:D) y cambiamos el color a verde. Haz clic en "Hecho".

3. Ahora haz clic en "+ Añadir otra regla".

4. Crea tu segunda regla con la fórmula personalizada. En este ejemplo, configuramos la condición del total de ventas mínimo con la fórmula personalizada =D:D=min(D:D) y cambiamos el color a rojo. Haz clic en "Hecho".

5. Como ves, después de aplicar varias condiciones, ahora sé que Melanie fue la primera en ventas totales y Ameline, la última.

Qué regla gana cuando las reglas se solapan

Google Sheets comprueba las reglas de un intervalo de arriba abajo, y la primera regla que se cumple decide el aspecto de la celda. Si una celda cumple dos reglas, solo recibe el formato de la que está más arriba. Para cambiar el orden, pasa el cursor sobre una regla en la barra lateral Reglas de formato condicional y arrástrala hacia arriba o hacia abajo con el controlador que tiene a la izquierda.

Si necesitas las dos condiciones en una sola regla, combínalas con AND u OR, como en el ejemplo 4 de arriba.

Formato condicional basado en otra hoja

Una fórmula personalizada no puede apuntar directamente a una celda de otra pestaña, así que una fórmula como =D2<Targets!B1 no funcionará. En su lugar, envuelve la referencia en INDIRECT:

=D2<INDIRECT("Targets!B1")

Así se colorea cada valor de ventas totales que está por debajo del objetivo guardado en la celda B1 de la pestaña Targets. Para comparar cada fila con la fila correspondiente de la otra pestaña, construye la dirección con ROW: =D2<INDIRECT("Targets!B"&ROW()). Obtén más información sobre la función en nuestra guía de la fórmula INDIRECT.

Las fórmulas personalizadas tampoco pueden traer valores de otro archivo. Si los valores con los que comparas están en otra hoja de cálculo, primero llévalos a una pestaña. Sheetgo, una plataforma de automatización de datos con IA, puede mantener esa pestaña sincronizada con el archivo de origen de forma programada.

Reglas integradas y escalas de colores en Google Sheets

Hay muchas formas de usar el formato condicional en Google Sheets, según el tipo de datos o lo que quieras conseguir.

Los ejemplos de esta sección usan una lista de comerciales con su número de clientes y sus ventas totales. Con el formato condicional, puedo resaltar valores clave, como las ventas o el número de clientes más altos o más bajos, resaltar las ventas totales que están dentro de un intervalo o incluso encontrar contenido duplicado. Primero, veamos paso a paso la forma más básica de usar el formato condicional en Google Sheets.

El formato condicional de Google Sheets tiene dos opciones: "Un solo color" y "Escala de colores". Veamos cómo usar las dos.

Reglas de un solo color

En este ejemplo, quiero resaltar en rojo todas las ventas totales inferiores a 5.000 $.

1. Abre el archivo de Google Sheets con tus datos. Selecciona las celdas a las que quieres aplicar la regla de formato condicional (por ejemplo, 'D1:D45') y ve a Formato > Formato condicional.

2. El menú aparece en la parte derecha de la pantalla. Asegúrate de estar en la pestaña "Un solo color". Como ves, el intervalo de celdas ya está definido.

3. Define la regla de formato. En el menú desplegable de "Formatear celdas si", elige la condición que debe activar la regla. Por ejemplo, si quiero resaltar todas las ventas totales inferiores a 5.000 $, selecciono la opción "Menor que". En el campo "Valor o fórmula" de abajo, escribo '5000'.

4. En "Estilo de formato", indica el formato de las celdas que cumplen tu regla. En este ejemplo, ponemos el color de fondo de la celda en rojo. Cuando termines, haz clic en "Hecho".

5. Las reglas de formato condicional se aplican a tus datos. Como ves, todas las ventas totales inferiores a 5.000 $ aparecen ahora resaltadas en rojo.

Con los mismos pasos puedes crear condiciones para valores mayores. Solo tienes que cambiar el desplegable "Formatear celdas si" a "Mayor que" y añadir tu valor.

El texto contiene y otras condiciones integradas

Las reglas de un solo color no se limitan a números. El menú desplegable Formatear celdas si también comprueba texto y fechas:

• Está vacía y No está vacía
• El texto contiene, El texto no contiene, El texto empieza por, El texto termina en y El texto es exactamente
• La fecha es, La fecha es anterior a y La fecha es posterior a
• Mayor que, Menor que, Es igual a, Está entre y sus variantes
• La fórmula personalizada es, para todo lo que la lista no cubre

Por ejemplo, para marcar todos los pedidos con retraso, selecciona la columna de estado, elige El texto contiene, escribe Late y elige un color. Cualquier celda que incluya esa palabra cambia de color, aunque también contenga otro texto.

Escalas de colores

Ahora supongamos que quiero aplicar una escala de colores a la columna 'Total sales'. Con esta opción, el fondo de cada celda toma un color o un tono distinto según su valor. En este ejemplo, quiero las ventas totales más bajas en rojo y las más altas en verde.

1. Igual que en la sección anterior, selecciona el intervalo de celdas y abre el formato condicional. Esta vez, asegúrate de haber seleccionado la pestaña "Escala de colores".

2. En la sección "Vista previa", haz clic en la escala de colores y elige la paleta que prefieras. Para mi objetivo, elijo la escala de rojo a verde.

3. Después, elige los valores y colores de Punto mínimo, Punto medio y Punto máximo. En el menú desplegable de cada sección, elige el valor en el que quieres basar los colores. Para cambiar los colores, haz clic en los iconos del bote de pintura y selecciona el color que prefieras. Cuando termines, haz clic en "Hecho".

4. Las reglas de formato condicional se aplican a tus datos. Como ves, los valores de ventas totales más bajos están en rojo y los más altos, en verde.

Cómo editar, copiar o eliminar reglas de formato condicional

Para cambiar una regla, selecciona cualquier celda de su intervalo, ve a Formato > Formato condicional, y haz clic en la regla en la barra lateral. Edita el intervalo, la condición o el estilo y, después, haz clic en Hecho.

Para copiar reglas a otro intervalo, copia una celda con formato, selecciona el nuevo intervalo y usa Editar > Pegado especial > Solo formato condicional. Las referencias relativas de una fórmula personalizada se desplazan a la nueva ubicación, mientras que las referencias con $ se quedan fijas.

Para eliminar una regla, pasa el cursor sobre ella en la barra lateral y haz clic en el icono de la papelera.

¡Enhorabuena! Ha utilizado con éxito el formato condicional para proporcionar pistas visuales que facilitan el proceso de identificación de datos.

Obtenga más información sobre la alternancia de colores en la siguiente entrada del blog: ¿Cómo crear una lista de colores alternos en Google Sheets?

¿Trabajas en Excel? Las fórmulas personalizadas funcionan igual allí. Esta guía de formato condicional en Excel te lo explica.