¿Cómo aplicar formato condicional en una tabla dinámica en Excel?
Las tablas dinámicas y el formato condicional son dos características muy útiles de Excel. Pero, ¿qué ocurre con el formato condicional de las tablas dinámicas? En este artículo, le explicaremos los trucos para aplicar el formato condicional en las tablas dinámicas de Excel sin tener ningún problema.
Tabla de contenido
1. ¿Qué es el formato condicional de la tabla dinámica?
2. ¿Cómo configurar el formato condicional para tablas dinámicas?
3. Añadir iconos en las tablas dinámicas
4. Aplicación de barras de datos a las tablas dinámicas
5. Añadir escalas de colores a las tablas dinámicas
6. Fórmulas personalizadas para el formato condicional de las tablas dinámicas
7. Mantener el formato condicional en una tabla dinámica después de actualizarla
8. ¿Cómo modificar una regla de formato condicional de tabla dinámica?
9. ¿Cómo eliminar una regla de formato condicional de una tabla dinámica?
10. Conclusión
En primer lugar, es importante entender que es un poco diferente aplicar formato condicional a las tablas dinámicas que a los rangos normales. Así que, antes de empezar, subrayaremos los principales aspectos básicos para no tener problemas en el camino.
1. ¿Qué es el formato condicional de una tabla dinámica?
Empecemos por definir la tabla dinámica.
Una tabla dinámica es una herramienta de resumen de datos que se utiliza con frecuencia en Excel y otros programas de hojas de cálculo. Se utiliza para organizar y reorganizar los datos de un conjunto de datos grande y detallado en un formato que sea más fácil de entender, analizar y sobre el que elaborar informes.
Recuerde que las tablas dinámicas crean rangos dinámicos.
Por otro lado, el formato condicional se utiliza para aplicar automáticamente formato a las celdas de su hoja de cálculo basándose en los datos que contienen. Así pues, el formato condicional funciona con criterios específicos.
Con los rangos normales, es fácil aplicar el formato condicional. Pero cuando se trata de tablas dinámicas, necesitará formatos dinámicos, ya que las tablas dinámicas están diseñadas para realizar análisis flexibles y dinámicos con datos de gran tamaño.
2. ¿Cómo configurar el formato condicional de las tablas dinámicas?
La aplicación del formato condicional a las tablas dinámicas implica conocimientos tanto de tablas dinámicas como de formato condicional. Vamos a explicarlo con instrucciones paso a paso:
Paso 1: Seleccione una celda en el área Valores
En primer lugar, debe seleccionar una celda de la columna en la que desea añadir el formato condicional.
Paso 2: Aplicar formato condicional
Ahora vaya a Inicio > Formato condicional en el menú superior, y configure sus reglas.
Por ejemplo, tenemos una tabla dinámica de diferentes productos e ingresos por ventas con un corte de cuatro ramas diferentes. Y, queremos resaltar los dos productos más vendidos.
Así que seleccionamos Reglas superior/inferior en el menú y configuramos nuestras opciones con el número superior y el estilo de formato:
Así, en el ejemplo anterior hemos utilizado una regla preestablecida incorporada del formato condicional de Excel. Pero también puede utilizar una personalizada, que veremos más adelante.
Paso 3: Configurar las opciones de formato
Cuando aplique el formato condicional, encontrará un pequeño icono llamado Opciones de formato. Ahora haga clic en este icono.
El icono le dará las opciones sobre su formato condicional. Como ve arriba, hay tres opciones:
- Celdas seleccionadas: Esto aplicará el formato sólo a la celda que hayamos seleccionado.
- Todas las celdas que muestren valores de «Suma de Ingresos»: Esta opción aplicará el formato a toda la columna bajo el encabezado «Suma de Ingresos». Esto incluirá también el Gran Total.
- Todas las celdas que muestren valores de «Suma de ingresos» para «Código de producto»: Esto no incluirá el Gran Total al rango de formato.
Si seleccionamos la segunda opción, verá que el formato se aplicará también al Gran Total, lo que normalmente no deseamos.
Por lo tanto, para excluir los totales generales o parciales de nuestro rango, debemos seleccionar los Valores para grupo específico de nuestra tabla dinámica.
Pero también hay algunos casos en los que se desea aplicar el formato a los Grandes Totales. Por ejemplo, si estamos analizando un cambio anual o una media, es posible que también queramos ver el formato en los grandes totales. Veamos este ejemplo a continuación en la sección de iconos.
3. ¿Cómo añadir iconos a la tabla dinámica?
Los iconos también son una función de formato preestablecida de Excel. Así que, para añadir iconos a sus tablas dinámicas, vaya a Inicio > Formato condicional > Conjuntos de iconos y seleccione el grupo más adecuado para usted.
En la imagen anterior, estamos analizando los Cambios Anuales en las ventas de un grupo de Productos. Así que también incluiremos los Grandes Totales al grupo:
Ahora, también vemos el valor iconizado del Gran Total.
4. ¿Cómo aplicar barras de datos a las tablas dinámicas?
Las barras de datos también son herramientas de formato preestablecido muy útiles para realizar comparaciones rápidas entre distintos valores.
En primer lugar, para obtener una vista de análisis más decente, le recomendamos que añada la columna Valor dos veces a su diseño pivotante y le dé a ésta un nombre diferente, como Barra de datos, Comparación, etc.
En la imagen inferior, hemos añadido la rúbrica Ingresos dos veces al área de valor y la hemos denominado Barra de datos.
En segundo lugar, vaya a Inicio > Formato condicional > Barras de datos o abra el cuadro de diálogo Regla de formato.
Aquí, excluiremos la fila Gran total seleccionando la tercera opción para el rango aplicado y, a continuación, configuraremos nuestras preferencias de estilo:
En la imagen inferior, seleccionamos un Relleno degradado de color azul. Puede utilizar un Relleno sólido, añadir bordes o cambiar la dirección del var según sus necesidades.
Por lo tanto, le recomendamos que no incluya filas de Gran Total o Subtotal en este caso, ya que arruinará la configuración de su barra de datos. Porque su comparación no tendrá sentido contra la fila total.
5. ¿Cómo aplicar escalas de color a la tabla dinámica?
Las escalas de colores le permiten crear mapas de calor en función de los valores dados y se encuentran entre las herramientas de visualización de datos más comunes.
Para añadir escalas de color a sus tablas dinámicas, vaya a Inicio > Formato condicional > Escalas de color. Puede seleccionar una preconstruida de la lista y luego cambiar el rango desde las Opciones de formato.
O bien, puede añadir directamente una nueva regla y hacer todos los arreglos en la ventana de formato:
De nuevo, tenga cuidado al seleccionar el rango. Debe aplicar su formato a todas las celdas bajo los encabezados específicos.
A continuación, seleccione Escala de 2 colores o Escala de 3 colores en el Estilo de formato.
Si lo desea, también puede cambiar aquí los colores, el percentil y los valores mínimo y máximo.
Ahora tiene una escala de colores dinámica en su tabla dinámica.
6. ¿Cómo aplicar una fórmula personalizada para el formato condicional de la tabla dinámica?
Además de las opciones de formato incorporadas, también puede añadir reglas personalizadas a su tabla dinámica.
En primer lugar, vaya a Inicio > Formato condicional > Nueva regla.
A continuación, organizaremos de nuevo nuestro rango para aplicar nuestro formato y establecer una regla personalizada basada en fórmulas.
Por ejemplo, en la imagen anterior, hemos resaltado los valores de ventas superiores a 1.000.000 $ con una cantidad de ventas inferior a 500. Este es un ejemplo sencillo de reglas personalizadas en formato condicional de tabla dinámica.
7. ¿Puedo mantener el formato condicional en una tabla dinámica después de actualizarla?
Sí, por supuesto que puede.
Si aplica el formato condicional en los rangos definidos de la tabla dinámica, no en los rangos de celdas normales ni en las columnas o filas, no perderá sus reglas de formato.
Pero si selecciona toda la columna y aplica las reglas de formato, entonces:
- Aplicará el formato condicional no sólo a la tabla dinámica, sino también a toda la columna.
- Cuando utilice los filtros o las rebanadoras, perderá el formato.
- Cuando actualice su archivo, el formato desaparecerá.
- Si cambia el diseño de su tabla dinámica desde el menú lateral, no podrá conservar el formato condicional.
Por ejemplo, en los ejemplos anteriores aplicamos nuestro formato a los rangos pivotantes, no a las referencias de celdas normales. Así tenemos un formato dinámico. Incluso si cambiamos las preferencias de la tabla pivotante, nuestra regla seguirá rellenándose:
Cambiamos las rebanadoras y los productos destacados entre los 2 más vendidos se actualizan en consecuencia.
¡Este es el misterio de los formatos dinámicos!
¿Y si elimino del diseño el rango pivotante con un formato condicional?
Si elimina un rango de su tabla dinámica, también se eliminará la regla de formato condicional que se aplica a ese rango.
Por lo tanto, si desea realizar un análisis dinámico diferente, siempre puede hacer una copia de su hoja de tabla dinámica para no masificar la hoja anterior.
8. ¿Cómo modificar una regla de formato condicional de tabla dinámica?
Siempre puede editar sus reglas desde el cuadro de diálogo Formato condicional.
Para ello, vaya a Inicio > Formato condicional > Gestionar reglas y, tras seleccionar la regla correspondiente, haga clic en la pestaña Editar regla.
Aquí podrá cambiar el rango, el estilo y los criterios para modificar las reglas del formato.
9. ¿Cómo eliminar una regla de formato condicional de una tabla dinámica?
Para borrar una regla de formato condicional de una tabla dinámica, haga clic en cualquier celda de la misma y vaya a Inicio > Formato condicional > Borrar reglas y Borrar reglas de esta tabla dinámica.
Esto borrará todas las reglas de la tabla dinámica seleccionada. Si desea borrar una de las muchas reglas, entonces irá a Gestionar reglas y hará clic en el pequeño botón Borrar regla del Gestor de reglas de formato condicional.
10. Conclusión
En resumen, hemos explicado cómo configurar reglas de formato condicional en tablas dinámicas, incluyendo barras de datos, conjuntos de iconos, escalas de colores, así como criterios personalizados.
Para las tablas dinámicas, el punto clave es definir los rangos para aplicar sus reglas de formato. Siempre debe utilizar los rangos de la tabla dinámica, no las referencias de las celdas normales. De este modo, mantendrá su formato con las rebanadas, los filtros, la actualización, los cambios de diseño o incluso los cambios de datos.
¡Espero que este post le ayude a realizar mejores presentaciones de análisis de datos!
Lecturas recomendadas:
Formato condicional en Google Sheets: Guía completa con ejemplos
¿Cómo hacer que sus archivos Excel tengan MUCHO mejor aspecto?
Puestos relacionados
