Formato condicional en Google Sheets

Formato condicional avanzado en Google Sheets

El formato condicional es una herramienta muy útil para resaltar las celdas y filas, teniendo en cuenta el valor que tengan

Hoy vamos a hablar de como aplicar el formato condicional en Google Sheets para una o varias celdas, así como en filas enteras, dependiendo del valor de alguna de las celdas.

Entre otras cosas, veremos que es el formato condicional, como aplicarlo en Google Sheets, que condiciones y que formatos se pueden usar y sugerencias de en que ocasiones es recomendable usarlo.

Por último veremos casos avanzados para resaltar celdas basadas en otras, y filas enteras.

¿Qué es el formato condicional?

El formato condicional, como su nombre lo indica, es una manera visual de identificar celdas, filas o columnas que cumplan con ciertas condiciones. En palabras más claras, lo podemos definir así:

El formato condicional es un formato simple que se aplica a una celda si cumple con cierta condición.

Veamos la frase anterior por partes:

El formato condicional es un formato simple

El objetivo del formato condicional es “marcar”, identificar o alertar sobre ciertas condiciones. Por lo tanto no está diseñado para formatos complejos. Lo único que podemos cambiar en un formato condicional es:

  1. Resaltar
    1. Negrita
    2. Cursiva
    3. Subrayado
    4. Tachado
  2. Color
    1. Fondo
    2. Texto

Si queremos hacer otro tipo de cambios, deberíamos usar macros

El formato condicional depende de una condición

Como bien lo dice su nombre, el formato se va a aplicar, solo si se cumple cierta condición.

Entre las condiciones que podemos incluir, se encuentran:

  1. Que haya un valor en la celda (o no)
  2. Que haya algún texto (empezando, terminando, contenido o exacto)
  3. Una fecha (antes de, después de, exacta)
  4. Un número (exacto, menor, mayor, entre, diferente a)
  5. Una fórmula

En este último es donde nos podemos poner creativos, y empezar a penar en condiciones más complejas

El formato condicional se aplica a una celda

Todas las condiciones citadas (menos la fórmula), solo sirven para la celda en la que se aplica.

Si quisiéramos aplicarlo a toda una fila, tenemos que ponernos creativos.

Entendiendo las formulas avanzadas en formato condicional

Para poder entender mejor las formulas dentro de los formatos condicionales, debemos aclara unas cosas:

La fórmula que incluyamos en nuestro formato condicional, debe ser una función o formula que devuelva VERDADERO o FALSO, si no no va a funcionar.

Cuando devuelva VERDADERO, el formato condicional se va a aplicar, cuando devuelva FALSO, no.

Algunas funciones que devuelven verdadero o falso, son:

  • =ISDATE(A1)
  • =ISTEXT(A1)
  • =ISNUMBER()
  • =ESBLANCO()
  • =ESERROR()
  • =ESFORMULA()

Pero podemos tomar casi cualquier fórmula y “convertirla” en una que devuelva VERDADERO o FALSO.

Esto lo podemos hacer con la ayuda de los operadores de comparación (>,<, =, <>, <=, >=)

Por ejemplo:

  • =A1=A2
  • =B1<15
  • =C1<>”Colombia”
  • =SUMA($A$1:$A$20)>100
  • =BUSCARV(A1;H1:M;3;0)=5

Es muy importante entender esto, porque estas son las fórmulas que vamos a incluir en las reglas avanzadas de nuestros formatos condicionales.

Por eso una buena práctica, antes de incluir una fórmula en los formatos condicionales, sobre todo si no estamos acostumbrados, es primero hacer la fórmula directamente en Sheets, y ver que nos dé los resultados que queremos: VERDADERO cuando cumple la condición, FALSO cuando no.

¿Cuando aplicar formato condicional?

El formato condicional se aplica cuando quiero mostrar o alertar al usuario (que puedo ser yo mismo, o alguien más) de situaciones específicas.

A continuación algunos ejemplos de situaciones donde el formato condicional puede ser útil.

  1. Para señalar visualmente el crecimiento (o decrecimiento) de algún indicador en el tiempo (de ventas, de usuarios, de visitas, etc). Como una especie de semáforo
    1. Si el crecimiento es mayor a 0, a 10% a 20%, o alguna meta específica, puede estar en verde.
    2. Si no hay crecimiento, o es menor al 10%, por ejemplo, puede no tener ningun formato especial
    3. Si hay un decrecimiento mayor al 10%, podría estar en rojo.
  2. Para señalar el cumplimiento de objetivos.
    1. Si cumple el objetivo, un verde claro
    2. Si cumple el objetivo, sobrepasándolo dos o más veces, es un verde oscuro,
    3. Si no cumple el objetivo, está en amarillo
    4. Si el porcentaje de cumplimiento del objetivo es realmente bajo, está en rojo
  3. Para ver fácilmente los usuarios que cumplan ciertas características.
    1. Resaltar los estudiantes que tengan una nota mayor a X
    2. Resaltar los clientes mayores a cierta edad
    3. Resaltar las sucursales de cierta ciudad.
    4. Etc.
  4. Para ver el cumplimiento de cronogramas o tareas
    1. Si la fecha limite ya pasó, está en rojo
    2. Si estamos muy cerca de la fecha límite, esta en amarillo.
    3. Si queda mucho para la fecha límite, no tiene color

¿Cómo se aplica el formato condicional en Google Sheets?

El formato condicional tiene tres componentes:

  1. El intervalo al cual aplicarlo
  2. El tipo de condición
  3. La condición que se evaluará (depende del tipo de condición)
  4. El formato a aplicar

Escoger el intervalo

Para aplicar un formato, lo primero es escoger el intervalo, lo cual se puede hacer de la siguiente manera:

  1. Seleccionar un grupo de celdas
  2. Ir al menú de Formato Condicional de alguna de estas dos maneras
    1. Ir a Formato > Formato Condicional o,
    2. Dar click derecho y escoger “Formato Condicional”

Lo anterior te abre el panel de Formato Condicional en el lado derecho de la pantalla:

Pero no te preocupes ya que después puedes cambiar el intervalo en el panel, en la sección “Aplicar al Intervalo”

Escoger la condición

Luego ponemos la condición o la regla de formato.

Los tipos de condiciones pueden ser:

  1. La celda está vacía o no
  2. Coincide exacta o parcialmente con algún texto.
  3. Es igual, anterior o posterior a alguna fecha
  4. Es mayor, menor, o igual a un numeró específico (o está entre un rango de números)
  5. Una fórmula personalizada (Aquí es donde se pone divertido y donde se abren las posibilidades).

Dependiendo del tipo de condición, tenemos un nuevo campo, que es donde ponemos el valor numérico, o de texto o fórmula. Es decir la condición exacta.

En los únicos que no aplicaría este campo es cuando la condición sea que la celda esté vacía (o no).

Escoger el formato

Por último, escogemos el formato que se va aplicar, si se cumple la condición.

La verdad es que no se muy flexible el formato, lo único que podemos aplicar es:

  1. Color de letra
  2. Color de fondo
  3. Estilo de texto (Negrita, cursiva, tachado, subrayado)

Y ya está. Esta es la forma más sencilla y básica para aplicar formatos condicionales. Hay unos usos específicos y avanzados que veremos ea continuación

Usando Fórmulas personalizadas en el Formato Condicional

Ahora empecemos con lo divertido que es usar fórmulas, lo que nos abre las posibilidades que nos ofrecen los formatos condicionales

Lo más importante a saber a la hora de aplicar una fórmula en el formato condicional, es que deben ser fórmulas que devuelvan un booleano. En otras palabras, deben ser fórmulas que devuelvan o VERDADERO o FALSO, nada más.

Es la única manera para que Sheets identifiqué si aplica o no el formato.

Si la fórmula devuelve un número como 5 o 6 o un valor de texto como “Pedro”, esta no va a funcionar.

Por eso una buena práctica, sobre todo si estamos empezando a familiarizarnos con los formatos condicionales avanzados es, primero escribir la fórmula y verificar que nos esté dando VERDADERO en los casos que queremos.

Otro aspecto a tener en cuenta es el de las referencias fijas o sueltas. Esto es vital para asegurarnos que el formato condicional se aplique a todo el rango

Ejemplo 1: Marcar cuando el valor sea impar

Empecemos con un ejemplo sencillo.

Vamos a aplicar un formato condicional cuando el valor sea impar .

Para esto vamos a apoyarnos en la función ES.IMPAR, la cual es booleana, por lo que funciona perfecto como introducción.

=ES.IMPAR(A1)

Primero evaluemos la fórmula en una columna auxiliar

Sabiendo que nos arroja el resultado que queremos, ahora si procedemos a llevarla al formato condicional

Para esto vas a copiar la fórmula (incluyendo el “=”)

Ahora vas a seleccionar el rango (columna A en mi caso)

Y vas al menú “Formato” y escoges “Formato Condicional”

Esto abre nuestro panel de Formato Condicional

Aquí, primero te vas a asegurar de que el intervalo sea el correcto

Como paso siguiente, vas a escoger dentro de “Reglas de Formato” la opción “La fórmula personalizada es” (Que es la última opción).

Y en el campo que dice “Valor o Fórmula” vas a pegar la formula

Y listo

Ya puedes borrar la columna auxiliar

Formato condicional de una celda dependiendo del valor de otra

Ejemplo 2: La celda de al lado es impar

Ahora vas a hacer exactamente lo mismo pero con la celda de al lado.

En este caso nuestro intervalo va a ser la columna A pero quiero evaluar la columna B

El intervalo sería A1:A1000

Y la fórmula seria =ES.IMPAR(B1)

Ejemplo 3: Formato condicional en 2 columnas con el mismo criterio

Ahora quiero seguir evaluando mi valor impar pero para dos columnas.

En otras palabras, quiero que se formateen dos columnas, con base a una tercera.

Aquí empezamos a incluir el concepto de columnas fijas, muy importante para los formatos condicionales

Es muy importante para que nos funcione el formato condicional en este caso, fijar la columna

La formula entonces seria

=ES.IMPAR($C1)

Y el intervalo seria A1:B1000

Formato condicional para filas completas

El formato condicional para filas completas en Google Sheets es una evolución del ejemplo que acabamos de ver.

Lo más importante es fijar la columna

Entonces, resumamos los pasos necesarios para incluir un formato condicional en una fila entera:

  1. Escoger el rango al cual lo vamos a aplicar. Este paso es muy importante porque dependiendo del tamaño de las columnas.
  2. Clic derecho > Formato Condicional o “Formato”>”Formato Condicional”
  3. En nuestra barra de Formatos Condicionales, en la sección “Reglas de Formato” escogemos “La fórmula personalizada es”
  4. Pegamos o escribimos nuestra fórmula
  5. Asegurémonos que la columna donde está la condición siempre tenga bloqueada la columna (Ej: $H5)

Como ves, no es tan sencillo, pero después de aplicarla un par de veces, se irá volviendo más fácil.

Ahora vamos a ver algunos ejemplos prácticos

REGLA IMPORTANTE: Asegúrense que la fila con la que escriben la fórmula es la misma fila en la que empieza el rango. Por ejemplo si el rango de la fórmula es $F2, el intervalo debe empezar en la fila 2. ¡Esto es vital!

Ejemplos de formato condicional avanzado

Que la celda sea igual a un número

Digamos que quisiéramos resaltar todos los clientes con exactamente 25 años para darles un premio.

En este caso la celda a evaluar es la “C2”, el operador es “=” y el valor sería “25”.

La fórmula a insertar en nuestro formato condicional entonces es:

=$C2=25

Que una celda sea igual a un texto

En este caso vamos a resaltar las filas donde la categoría sea Colombia

En este caso la primera celda a evaluar es la “B2”, el operador es “=” y el valor sería “Colombia”.

La formula a insertar en nuestro formato condicional es:

=$B2=”Colombia”

Que el valor de una celda este dentro de un rango

Podemos usar funciones como Y o O para poner mas de una condición.

Por ejemplo, si queremos que resalte cuando un número esté entre 1880 y 1885, lo podemos formular así:

=Y($A1>=1880;$A1<=1885)

Formato condicional con dos condiciones

Parecido al ejemplo de texto anterior, podemos resaltar cuando el valor sea Colombia, o cuando sea Ecuador.

Para esto vamos a usar la función O, así:

=O($B2=”Colombia”;$B2=”Ecuador”)

Que el valor de una celda sea mayor a una fecha

Esto funciona muy parecido al caso de los valores.

Si recuerdas, las fechas en Sheets y en Excel son números. Por ejemplo:

  • Diciembre 8 del 2015 es 42346
  • Agosto 3 de 2016 es 42585

Para ver el número de una fecha simplemente cámbiale el formato de la fecha a número.

Entonces para hacer este formato condicional, primero debes saber cuál es el valor numérico que queremos.

En mi caso quiero que resalte cuando una fecha sea mayor al 29 de Junio de 2018, es decir a 43280. Entonces la formula sería:

=$A2>43280

Que una celda empiece con una palabra especifica

Podemos empezar a volvernos más y más complejos.

Por ejemplo si quiero que mi texto siga algún patrón específico, vamos a usar la función REGEXMATCH

En este caso quiero que mi texto empiece con la palabra “El”

=REGEXMATCH(A2;”^El”)

Que el cliente este en el listado de clientes morosos

Aquí vamos a usar un BUSCARV para asegurarnos que un valor esté en una lista.

En este caso tengo mi lista de clientes, y quiero ver cuáles de mis clientes están en el listado de clientes morosos.

Como el BUSCARV me trae un valor, no me funciona

Pero sé que si el BUSCARV me trae un error, entonces es porque no esta. Con la función ESERROR podemos saber si nos trae un error

Aquí podemos entonces usar la función NO que simplemente invierte los verdaderos y falsos

En otras palabras, el ESERROR(BUSCARV()) me da verdadero cuando hay un error, y falso cuando no, como necesito es el inverso, entonces “envuelvo” las funciones en un NO, así:

=NO(ESERROR(BUSCARV($A2;$E$2:$E$13;1;0)))

Cómo copiar el formato condicional en Google Sheets

Copiar el formato condicional no es difícil

Hay dos maneras. Una es usando la brocha de copiar formato que se encuentra al lado del icono de imprimir

Para esto escogemos la celda o celdas que tienen el formato condicional, damos clic en el botón de copiar formato y seleccionamos el rango donde queremos pegar el formato

Otra opción es copiar con Ctrl+C las celdas que tienen el formato condicional, después seleccionar el rango donde quiero pegar, e ir a la opción “Editar”>”Pegado Especial”>”Pegar solo formato condicional

Conclusión

Como ves, aunque parece sencillo, el tema de formatos condicionales, tiene mucha tema para cortar. Los formatos condicionales simples son fáciles de aprender a usar y muy útiles

Por otro lado, como cualquier tema nuevo, los formatos condicionales avanzados al principio parecen muy complicados, pero poco a poco, practicando, los podemos dominar y cada vez usar condiciones más avanzadas

Por favor déjame saber si hace falta algo a la explicación, o si es claro, ya que esta es una funcionalidad bastante útil y usada.

45 comentarios en “Formato condicional en Google Sheets”

  1. HOLA JUAN QUE TAL EN EXCEL LO PUDE DESARROLLAR PERO LAMENTABLEMENTE EN SHEETS NO HE PODIDO. EL TEMA ES EL SIGUIENTE.
    CONSTRUI UNA HOJA DONDE ESTAN LOS HORARIOS POR SEMANAS ( INCLUYE TODO EL MES) PERO SE DEBE RESALTAR LAS COLUMNAS QUE SEAN DOMINGO DE COLOR ROJO Y LETRAS AMARILLAS PARA QUE VISUALMENTE LOS OPERADORES SEPAN QUE ES DOMINGO. QUE PUEDO HACER. EN EXCEL LO DESARROLLE DE LA SIGUIENTE MANERA.
    Public Sub dom()
    ‘ variable de tipo Range para hacer referencia a las celdas
    Dim obj_Cell As Range
    Dim columna As Integer
    Dim DATOS As Range

    Dim fila As Integer

    ‘Recorrer todas las celdas seleccionadas en el rango actual
    For Each obj_Cell In Range(“E2:AM67”)

    ‘ muestra el valor
    With obj_Cell
    If obj_Cell.Text = “D” Then
    ‘obj_Cell.EntireColumn.Font.ColorIndex = 6
    obj_Cell.EntireColumn.Activate
    obj_Cell.EntireColumn.Interior.ColorIndex = 3
    obj_Cell.EntireColumn.Font.ColorIndex = 6
    ‘obj_Cell.EntireColumn.Font.Color = 6

    End If
    End With
    Next

    End Sub

    CUALQUIER AYUDA SERIA GENIAL

  2. Hola Juan, cómo puedo hacer para noner un formato condicional a una celda (NO a una columna) y que cuando aplique un ordenamiento por alguna columna y se cambie el orden de la fila el formato condicionado se conserve en la nueva ubicación. Ejemplo: Si estoy en D4 y le aplico un formato condicional a esa celda para que cambie de color si es mayor a una fecha, y luego aplico un Ordenar y ahora lo que estaba en D4 ocupa la casilla D15, como hago para que el formato se aplique a la nueva ubicación D15 automaticamente y no se mantenga en la D4 que ahora lo ocupa otro registro?

    De antemano gracias por la ayuda

  3. Buenos días,
    Muchas gracias por tu trabajo. Mi duda es la siguiente.
    Tengo un formato condicional que depende del valor de la propia celda y de otra condición respecto otra celda de su columna, pero cuando copio el formato a otra celda no se cambia la referencia, es decir no me hace el formato condicional respecto a la nueva celda y respecto a la celda de su columna.
    Muchas gracias de antemano por tu tiempo.

  4. Cristian Buitrago

    Hola Juan,

    Mil gracias por compartir tus conocimientos, me han ayudado mucho tus videos. Agradeceria tu ayuda de corazon, quiero que el formato condicional me ponga el borde de toda la fila de un color, tu dices que toca con macros, me pdrias ayudar con algun ejemplo, mil gracias.

    1. Hola!

      Usa esta

      function onEdit(e) {
      var sheet = e.source.getActiveSheet();
      var range = e.range;
      var row = range.getRow();
      var column = range.getColumn();

      // Aplica el formato condicional a la fila cuando se edita la celda
      if (column == 1) { // Reemplaza 1 con el número de la columna que quieres monitorear
      var lastColumn = sheet.getLastColumn();
      var targetRange = sheet.getRange(row, 1, 1, lastColumn);

      // Reemplaza “red” con el color que deseas para el borde
      targetRange.setBorder(true, true, true, true, null, null, “red”, SpreadsheetApp.BorderStyle.SOLID);
      }
      }

  5. Hola Juan, gracias por tu enseñanza, aprendo mucho con los tutoriales.
    tengo un proyecto de lector de precios casi finalizado, solo se me presenta el problema de que es necesario que haya una única celda (A1) donde se ingresa el código de barras con el scanner y que una vez ingresado el valor, el cursor NO se desplace a otra celda, sino que siempre esté situado en dicha celda de manera inamovible. No he encontrado información al respecto. en Excel funciona simplemente con la protección de hojas y libros bloqueando la hoja y seleccionando la celda desbloqueada. Necesito lo mismo para Google sheets.
    No se si me explico bien, seria habilitar únicamente A1 para escribir y sobreescribir una y otra vez la misma celda y que nunca se desplace.

  6. Hola he buscado tantas veces y no encuentro solución solo necesito:
    Resaltar fila activa en Google Sheets = al dar clic en una celda se resalte toda la fila. (no es una hoja de excel, es google sheet.)

      1. No me funciona estimado, he escrito el código tal cual, hice el truco en la parte de información general pero nada, me sale el siguiente error:

        TypeError: Cannot read properties of undefined (reading ‘source’)
        onSelectionChange @ Código.gs:2

  7. Hola. Espero puedas ayudarme.
    Necesito pintar los duplicados en una columna comparando dos columnas de diferentes hojas, misma tabla. Se puede con formato condicional en Gsheet?
    Desde ya muchas gracias.

  8. Hola, mil gracias!!
    No podía hacer bien mi formato condicional, se marcaba otra casilla y no la que quería :c Pero con tu explicación ya logre corregir mi error y aprendí mucho más.
    Eres el mejor!!!

  9. Benjamín Gutiérrez

    GRACIAS JUAN! Tienes una muy buena página y tus explicaciones son muy claras, me ha ayudado bastante con tareas que se me han pedido en mi práctica profesional, saludos

  10. hola buenas días, como hacer que cuando seleccione una celda se active otra?
    Ejemplo si selecciono la A1 que se active la H20. como ejemplo
    y que cuando de clic en otra celda se desactive y se active con quien tenga esa correspondencia, se que es mucho trabajo porque tendría que programar cada celda, pero me gustaría saber como hacerlo. Es como seleccionar dos imágenes del mismo diseño o algo así.

  11. Hola linda tarde.

    Que hago si utilizo esta formula =$J2=”NORMOPESO” E INCLUSO =$J2=»NORMOPESO» y aun a si me marca como formula no valida

  12. Hola, tengo esta formula en un condicional, =SI.ERROR(SI(Y(BUSCARV(B12;Laborables;2;0)=”No”);VERDADERO;FALSO);FALSO) y solo me pone color a una fila entera y lo que quiero es ponerselo a toda una columna.
    Ejemplo: que las columnas B y C tengan color y otras no.
    Gracias por la ayuda.

  13. Hola, cómo podría hacer que una celda cambie de color cuando esta vacía, pero un grupo de celdas anteriores tiene datos.

    Ejemplo las celda A, B, C y E tienen datos pero la D esta vacía, quiero que la celda D cambie de color.

    Muchas gracias!

  14. Hola Juan,

    Aplicado al intervalo D:D y haciendo una referencia de fechas en la columna I:I tengo una formula personalizada para un formato condicional:

    =SUMAPRODUCTO(–($I4&”-“&$D4=$I4:$I$4&”-“&$D4:$D$4))>1

    ¿Cómo podría excluir del formato condicioanl las celdas vacías o en blanco de la columna D:D?

    ¡Un saludo!

  15. Cordial saludo,

    A pesar de tener resultado VERDADERO en la formula, el formato condicional no funciona (no da formato de color rojo que coloque), a qué se debe?

    He abierto otros libros de Excel para ensayar y sigue el problema.

    Muchas gracias por la ayuda.

  16. Hola Juan,

    Espero que te encuentres bien. He usado la formula personalizada para darle formato condicional (un color en funcion a los datos de una celda $L4) pero no funciona para algunos valores.

    Formula: =$L4=”Sold”

    * Los valores de la Columna “L” estan con validacion de datos.
    *He verificado si los valores de la celda y de la formula personalizada estan exactamente igual y si. Sabes que podria estar pasando? cual seria el error?

    Pd: He aprendido mucho con tus videos de excel

  17. buenas noches si tengo un numero en la columan a5 y otro numero en la columna n5 si son iguales como hago para resaltar solo los numeros de estas celdas con formato condicional? muy explicita la informacion

  18. hola buenas tardes saludos tengo un movimiento de salida y entrada de materiales y quisiera saber si puedo aplicar un formato condicional pero que me subraye el material que salió ósea la linea completa de donde se encuentra el material.

      1. Apreciado Juan. Si la celda me reporta el clásico error con la esquina seleccionada en rojo y el mensaje de No es Válido (La entrada ha de estar incluida en el Intervalo especificado) puedo usar ESERROR para dar formato condicional a esa celda con error?
        Lo veo un poco complicado para mí. No se si me puede ayudar.
        Gracias nuevamente

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This