Ejemplos de Formato Condicional en Excel – Un Enfoque Avanzado para Usuarios Expertos
La mayoría de los usuarios de hojas de cálculo están familiarizados con la función de formato condicional de Excel como herramienta de análisis de datos. Encontrar duplicados, resaltar números, dar advertencias son algunas de las aplicaciones más comunes de esta herramienta. Pero hoy iremos un paso más allá para presentarle algunos usos únicos y avanzados del formato condicional.
Tabla de contenido
Introducción: ¿Qué podemos hacer con el Formato Condicional en Excel?
1. Formato de tablas con números de fila flexibles
2. Resaltar duplicados con la fórmula Countif
3. Formato condicional con reglas múltiples
4. Juego del tres en raya con el formato condicional de Excel
5. Otros Ejemplos Avanzados
Conclusión
¡Empecemos!
Breve introducción: ¿Qué podemos hacer con el formato condicional en Excel?
En su forma más simple, el formato condicional puede cambiar el color de una celda en función de su valor. Por ejemplo, un informe financiero podría utilizar el rojo para resaltar los gastos que superan un determinado umbral, o el azul/verde para indicar las áreas rentables.
Otro ejemplo, dar colores rojo, amarillo y verde para cualquier escala de Bajo, Medio y Alto.
Sin embargo, las capacidades del formato condicional van mucho más allá de los cambios de color. Puede utilizarse para crear barras de datos, escalas de colores e incluso conjuntos de iconos que representen visualmente las variaciones de los datos. Esto no sólo mejora la legibilidad de los datos, sino que también permite a los usuarios detectar tendencias y valores atípicos de un vistazo.
1. Formateo de tablas con números de fila flexibles
Un aspecto vital del formato condicional es su naturaleza dinámica.
A diferencia del formato estático, las reglas condicionales se actualizan automáticamente a medida que cambian los datos. Esto significa que el formato evoluciona a medida que se introducen nuevos datos o se modifican los existentes, lo que garantiza que la representación visual esté siempre actualizada.
Pasemos ahora a una aplicación práctica que utilizamos a menudo en nuestras plantillas. Se trata de formatear dinámicamente una lista de números, incluyendo el resaltado de la suma total al final de la lista. El siguiente ejemplo muestra una lista con formato dinámico. Cuando cambiamos el número de elementos de la lista desde el pequeño recuadro naranja de la izquierda, el formato se actualiza automáticamente. Además, tenemos la SUMA total al final de cada lista.
Esto tiene un aspecto increíble, ¿verdad?
¿Cómo conseguir esta flexibilidad?
En nuestro ejemplo, independientemente de cuántos números se generen, siempre quiero que la última fila, que contiene la suma, tenga un formato distinto con texto en negrita y amarillo. Para conseguirlo, establecemos una fórmula que comprueba si el número de fila de una celda es mayor que el número de fila máximo identificado anteriormente.
Aquí, es esencial hacer que la referencia de la fila sea dinámica eliminando el signo de dólar de la parte de la fila de la referencia de la celda. Este ajuste permite que la fórmula evalúe cada celda individualmente.
Por último, queremos eliminar los bordes de ciertas celdas, lo que requiere un ajuste adicional de la fórmula.
Examinando detenidamente el resultado y ajustando la fórmula según sea necesario, podemos asegurarnos de que el formato se comporta según lo previsto. Esta función dinámica es especialmente útil en cuadros de mando e informes en los que el control de los datos en tiempo real es crucial.
2. Resaltar duplicados con la fórmula Countif y el formato condicional de Excel
Normalmente, la mayoría de la gente utiliza la función estándar de «valores duplicados » para resaltar valores específicos y, aunque funciona bien, tiene algunos inconvenientes. Por ello, le mostraremos un método alternativo para identificar valores duplicados.
He aquí nuestra lista de ejemplo:
Intentaremos encontrar aquí los números de identificación duplicados. Podemos utilizar la función estándar de resaltado de duplicados.
¿Cómo encontrar duplicados con formato condicional?
Vaya a Formato condicional > Valores duplicados y seleccione el tipo de formato.
Tenga en cuenta que también puede resaltar valores únicos con este método. También le permite hacer formatos personalizados además de los ya preparados que ofrece.
El resaltado estándar está bastante bien, pero no funcionará en las versiones anteriores de Excel por problemas de comptabilidad. Además, se trata de un método prefabricado y no es fácil de modificar. Pero si tiene varios criterios, entonces necesitará algo más.
Por ello, a menudo utilizamos fórmulas para detectar los valores duplicados, lo que permite una mayor flexibilidad, como comparar columnas específicas en busca de duplicados.
Por ejemplo, para resaltar los ID duplicados, utilizaríamos una función COUNTIF. Esta función comprueba si un recuento es mayor que uno, lo que indica que hay un duplicado.
Después de establecer esta regla, puede que necesitemos ajustar las referencias de las celdas para garantizar la correcta aplicación del formato condicional.
3. Formato condicional de Excel con reglas múltiples
Además, el formato condicional puede personalizarse para adaptarse a criterios complejos. Pueden establecerse reglas avanzadas mediante fórmulas, lo que permite estrategias de visualización de datos matizadas y específicas. Este nivel de personalización lo convierte en una herramienta indispensable para cualquiera que trabaje con grandes conjuntos de datos, desde analistas financieros hasta profesionales del marketing.
Pongamos un ejemplo con varias reglas. En la tabla siguiente tenemos tareas, estado de las tareas y fechas de vencimiento. Queremos destacar los plazos vencidos. Pero también queremos que si el estado de una tarea es Hecho o En espera, no queramos ningún resaltado.
Así que aquí necesitaremos una fórmula compleja.
La fórmula anterior primero da una condición de que la fecha sea mayor que la fecha de hoy, y luego da otra condición de que la siguiente celda no sea Hecho o En espera. Por último, combina estas dos condiciones con la función AND.
4. Juego del tres en raya con el formato condicional de Excel
He aquí otro ejemplo con el juego Tic-Tac-Toe. Queremos dar colores rojos cuando alguno de los jugadores consiga hacer una fila de 3 elementos horizontal, vertical o transversalmente.
Primero tendremos que hacer un cálculo para identificar los conjuntos de 3 elementos. Puede utilizar la función COUNTIFS para hacer la fórmula. Básicamente comprueba si el recuento de elementos es igual a tres y no está vacío, y da «W» para la situación WIN.
Ahora es el momento de utilizar esta W como regla de formato condicional. Si la celda inicial es igual a W, entonces la fila o columna se coloreará de verde.
Ya está.
Como consejo adicional, puede hacer que el color de la fuente de las celdas W sea el mismo que el del fondo, para que el usuario no las vea en el diseño.
5. Otros ejemplos avanzados
Ahora, le daremos algunos ejemplos y casos prácticos más avanzados utilizando el formato condicional. Éstos proceden de nuestras plantillas ya preparadas.
Matriz RACI
Hemos creado una matriz de responsabilidades con formato condicional. Cuando el usuario selecciona la categoría de asignación para cada tarea y cada persona, la plantilla da los colores automáticamente:
– Este es el panel de control de la plantilla de matriz de responsabilidades Someka –
Si también se pregunta, de dónde vienen estos colores, se lo damos como ajuste al usuario. Esto hace que la plantilla sea dinámica. El usuario puede cambiar los nombres de las categorías en función de los colores dados:
Vista del tablero kanban
La vista Kanban es una de las aplicaciones más singulares del formato condicional. En primer lugar, creamos las fórmulas complejas en el backstage y, a continuación, creamos la tabla Kanban con colores:
– La sección principal de la plantilla de tablero Kanban de Someka –
En esta plantilla, todas las fórmulas son totalmente dinámicas, lo que permite modificar el estado y las prioridades de cualquier tarea. Por ejemplo, si se actualiza el estado de una tarea en una sección, se refleja en toda la plantilla, acompañado de cambios en el formato.
Las reglas de formato condicional en los encabezados de la vista Kanban se basan en la longitud del contenido de la celda. Por ejemplo, si una celda no está vacía, se formatea en azul.
Para el cálculo de prioridades, utilizamos una tabla para determinar el nivel de prioridad y luego aplicamos el formato correspondiente a cada nivel de prioridad. Las prioridades altas tienen un fondo rojo, mientras que las bajas tienen un fondo verde.
Como puede ver, el formato condicional permite una amplia personalización.
Cuadros de mando de KPI con formato condicional de Excel
Otro magnífico ejemplo de formato condicional. Los usuarios de cuadros de mando de KPI suelen querer ver los resultados buenos y malos de un vistazo. Aquí puede utilizar iconos o mapas de calor de colores para subrayar los resultados malos y buenos.
– Esta es la sección de análisis de nuestra plantilla de panel de indicadores clave de gestión –
En primer lugar, calculamos los índices Real frente al año anterior y Real frente a los objetivos y, a continuación, aplicamos un formato condicional con iconos diminutos. Esto proporciona una visualización de análisis limpia pero potente.
Hoja de ruta
Ahora también daremos un ejemplo de nuestro gráfico Hoja de ruta.
– Plantilla Excel de hoja de ruta de la cartera Someka –
Esta plantilla crea principalmente una hoja de ruta a partir de una lista de objetivos. Los colores proceden de las divisiones. Damos realces con formato condicional. De nuevo, los colores son dinámicos y se configuran en el área de ajustes.
Elementos visibles / invisibles
Si desea que algunos elementos de su archivo sean invisibles a menos que se cumplan determinadas condiciones, el formato condicional es la mejor forma de utilizarlo. En nuestra Plantilla Excel del Plan de Acción, los botones de navegación de cada objetivo sólo se hacen visibles cuando escribimos un objetivo.
Así, si el área del objetivo está vacía, el botón de navegación de Ir al objetivo no es visible para el usuario. Esto es inteligente, ¿verdad?
Seguimiento del PTO del equipo
Si desea crear un seguimiento de PTO para su equipo, puede crear vistas de aspecto agradable con el formato condicional de Excel.
Por ejemplo, puede crear vistas Gantt para ver a todo el equipo de un vistazo:
– Esta es la sección de seguimiento del equipo de la plantilla Leave Tracker de Someka –
En el ejemplo anterior, puede ver qué empleados están de vacaciones y con la lectura de colores puede entender las vacaciones de cada uno.
Otro buen ejemplo sería colorear el calendario anual de un empleado concreto:
– Esta es la sección de seguimiento de empleados de la plantilla Excel de gestión de permisos de Someka –
Aquí se trata de colorear los días que el empleado seleccionado está de baja. De nuevo un diseño muy dinámico, ya que el usuario puede seleccionar cualquier empleado del menú desplegable y el calendario se actualiza automáticamente.
Hacemos este tipo de cálculos en una hoja aparte y codificamos cada tipo de permiso con un único número. Así, cuando cambia el empleado, los números se actualizan y el cuadro de mandos también cambia automáticamente.
Matriz de competencias del empleado
Otro uso muy común del formato condicional es la matriz de competencias.
– El tablero de mandos de la plantilla Excel de matriz de competencias de Someks –
Aquí puede ver el mapa de calor general de su reserva de talentos. También se trata de un conjunto muy básico de reglas de formato. Lo crucial aquí es utilizar el mínimo número de reglas con una configuración inteligente para evitar que su archivo se ralentice.
Estructura EDT
Otra característica destacable del formato condicional es su capacidad para crear visualizaciones sencillas pero eficaces, como un gráfico parecido a una estructura de desglose del trabajo. Esto se consigue enteramente mediante el formato condicional, que permite que el gráfico cambie dinámicamente de aspecto en función de diversas condiciones.
– El área del gráfico EDT de la plantilla Excel de matriz de responsabilidades –
Por ejemplo, puede resaltar funciones o personas concretas implicadas en un proyecto. La versatilidad de estos filtros permite su uso simultáneo o su aplicación singular, en función de las necesidades.
Una aplicación especialmente interesante que queremos destacar consiste en ajustar dinámicamente los bordes entre grupos en función del número de entradas dentro de cada grupo.
Por ejemplo, si se añade una nueva tarea a un grupo específico, como Q4, y se etiqueta, digamos, «Marketing», el formato se ajusta automáticamente. Los bordes entre los grupos se desplazan hacia abajo a medida que se añaden más tareas, lo que garantiza una representación visual fluida y adaptable.
Formularios dinámicos para empleados
Por último, también puede crear formatos de formulario dinámicos con formato condicional. Esto permite al usuario crear su propia plantilla de formulario.
A continuación se muestra un ejemplo de formulario dinámico que hemos creado para los departamentos de RRHH. Los encabezados del formulario son seleccionados por el usuario. Y todos los bordes y colores de fondo se ajustan automáticamente según la elección.
– Esta es la sección de formularios de la plantilla de base de datos de empleados de Someka –
Las opciones de encabezado aquí provienen de los encabezados de la base de datos de empleados. Así, el responsable de RRHH puede seleccionar cualquier encabezado de información para incluirlo en el Formulario de Empleado
Conclusión
El formato condicional de Excel es una potente herramienta utilizada en la visualización y el análisis de datos, que permite a los usuarios identificar rápidamente tendencias, anomalías y patrones en un conjunto de datos. Esta función, ampliamente disponible en programas de hojas de cálculo como Microsoft Excel y Google Sheets, permite aplicar reglas de formato específicas a las celdas en función de los datos que contienen.
La esencia del formato condicional reside en su capacidad para convertir los datos brutos en información visualmente atractiva, facilitando su comprensión y análisis. Hemos intentado darle aquí algunos consejos avanzados y ejemplos de aplicación para que se adentre en esta potente función.
En resumen, el formato condicional no consiste sólo en hacer que las hojas de cálculo parezcan más atractivas. Es una funcionalidad crítica que mejora la comprensión de los datos, ayuda en la toma de decisiones e impulsa la eficiencia en el análisis de datos.
Lecturas recomendadas:
Tablas dinámicas de Excel: La guía más completa con auténticos consejos profesionales
¿Cómo prevenir y resolver problemas de discrepancia de datos en Excel?
Puestos relacionados
