En este tutorial te voy a mostrar cómo crear formatos condicionales avanzados en Google Sheets que tomen en cuenta valores de otras hojas o incluso de archivos diferentes. Esto es súper útil cuando quieres resaltar datos importantes basados en criterios que están en otro lugar, como en una hoja de configuración o en otro archivo. Además, aprenderás a usar funciones como INDIRECTO y IMPORTRANGE, que son la clave para saltar las limitaciones normales de los formatos condicionales en Google Sheets.
Si prefieres ver este tutorial en video, te los dejo a continuación para que sigas paso a paso y no te pierdas ningún detalle.
¿Qué son los formatos condicionales y para qué sirven?
Los formatos condicionales son una herramienta que te permite cambiar automáticamente el estilo de las celdas (como el color de fondo, la fuente, o poner negrita) cuando se cumple cierta condición. Por ejemplo, puedes hacer que todas las celdas con valores menores a un número se pongan en rojo para que se destaquen. Esto ayuda a identificar rápidamente datos que requieren atención, como estudiantes con notas bajas o productos con poco inventario.
Pero, ¿qué pasa si quieres que el formato dependa de datos que están en otra hoja o incluso en otro archivo? Ahí es cuando necesitas formatos condicionales avanzados con fórmulas personalizadas.
Cómo crear formatos condicionales avanzados paso a paso
1. Aplicar un formato condicional básico
Primero, vamos con lo básico para que entiendas el proceso:
- Selecciona el rango de celdas donde quieres aplicar el formato.
- Ve al menú
Formatoy eligeFormato condicional. - Escoge una condición, por ejemplo
Menor que, y escribe un valor, digamos4. - Define el estilo (como un fondo rojo) y haz clic en
Hecho.
Esto ya te permite resaltar datos básicos, pero ahora vamos a complicarlo un poco para que puedas usar datos de otras hojas o archivos.
2. Formato condicional basado en otra hoja con fórmula personalizada
Google Sheets no permite usar referencias directas a otras hojas en las reglas predeterminadas, pero puedes usar una fórmula personalizada que haga esa referencia.
Por ejemplo, si quieres que una celda en la hoja actual se resalte si es menor al valor que está en la celda B1 de la hoja Configuración, haces lo siguiente:
=B2 < Configuración!$B$1Es importante fijar la referencia con los signos $ para que no cambie cuando se aplique el formato en varias celdas.
3. Usar INDIRECTO para referencias cruzadas en formatos condicionales
Aunque la fórmula anterior funciona en muchos casos, Google Sheets a veces no permite usar referencias directas a otras hojas dentro de formatos condicionales. La función INDIRECTO es la solución para esto.
La fórmula queda así:
=B2 < INDIRECTO("Configuración!B1")Lo que hace INDIRECTO es convertir el texto que representa una referencia en una referencia real que Google Sheets puede evaluar dentro del formato condicional.
4. Conectar formatos condicionales con otros archivos usando IMPORTRANGE
Si los valores que necesitas para el formato condicional están en otro archivo de Google Sheets, puedes traer esos datos con IMPORTRANGE.
La sintaxis básica es:
=IMPORTRANGE("URL_del_archivo"; "NombreHoja!Rango")Por ejemplo, si quieres traer el rango B1 de la hoja Configuración de otro archivo, primero debes importar ese rango en una celda de tu archivo actual, y luego referenciar esa celda en tu formato condicional.
Recuerda que la primera vez que uses IMPORTRANGE te pedirá permiso para conectar los archivos.
5. Crear reglas condicionales con múltiples condiciones
Muchas veces quieres que el formato se aplique solo si se cumplen varias condiciones al mismo tiempo. Para eso usas las funciones Y y O combinadas en la fórmula personalizada.
Ejemplo para aplicar formato solo si el valor es menor que un límite y además es mayor que cero:
=Y(B2 < Configuración!$B$1; B2 > 0)Esto evita que se apliquen formatos a celdas vacías o con valores no deseados.
Ejemplo práctico: resaltar estudiantes con baja asistencia según idioma
Imagina que tienes un archivo con una hoja llamada Datos donde están los estudiantes y su asistencia a clases, y otra hoja llamada Configuración donde defines el mínimo de asistencia requerido para cada idioma (francés, inglés, etc.).
Quieres que en la hoja Datos se resalten los estudiantes que no cumplen con el mínimo de asistencia según el idioma.
Para esto, en la hoja Datos, selecciona la columna con las asistencias y crea un formato condicional con esta fórmula personalizada:
=SI.ERROR(B2 < INDIRECTO("Configuración!B1"); FALSO)Así, cualquier valor en la columna B que sea menor al mínimo definido en Configuración!B1 se resaltará automáticamente. El SI.ERROR evita que aparezcan errores si alguna celda está vacía o con datos inválidos.
Limitaciones y recomendaciones para formatos condicionales avanzados
- Cuando usas
IMPORTRANGE, los cambios en el archivo origen pueden tardar en reflejarse, así que ten paciencia si no ves la actualización inmediata. - Las fórmulas complejas en formatos condicionales pueden hacer que tu archivo se vuelva lento, especialmente si tienes muchos datos. Trata de optimizar las fórmulas y limitar el rango donde aplicas el formato.
- Evita usar referencias circulares o fórmulas que dependan de celdas que también están siendo formateadas condicionalmente para evitar errores.
Conclusión
En resumen, con estas técnicas puedes llevar tus formatos condicionales en Google Sheets a otro nivel, usando datos de otras hojas o archivos para tomar decisiones visuales en tus tablas. Funciones como INDIRECTO e IMPORTRANGE son tus aliadas para superar las limitaciones básicas y personalizar tus hojas según tus necesidades.
Recuerda siempre probar tus fórmulas paso a paso y ajustar las referencias absolutas para que el formato se aplique correctamente en todo el rango. Así podrás tener reportes visuales que te ayuden a tomar mejores decisiones rápidamente.
Si quieres profundizar más en el uso de formatos condicionales y fórmulas personalizadas en Google Sheets, te recomiendo mi curso completo en la academia JuanSGuzman, donde vemos estos temas con más ejemplos y casos prácticos. Inscríbete aquí.
Si quieres aprender a usar Google Forms y Google Sheets desde cero, te invito a tomar un curso gratis de 2 horas.