Cómo Crear Gráficos de Excel (Columna o Barra) con Formato Condicional

Este tutorial demostrará cómo crear gráficos de Excel con formato condicional en todas las versiones de Excel: 2007, 2010, 2013, 2016 y 2019.

El formato condicional es la práctica de asignar formato personalizado a las celdas de Excel -color, fuente, etc.- basado en los criterios especificados (condiciones). Esta función ayuda a analizar datos, encontrar valores estadísticamente significativos e identificar patrones en un conjunto de datos determinado.

Pero, por desgracia, Excel no ofrece ninguna herramienta integrada para aplicar formato condicional a los gráficos de Excel.

Sin embargo, con un poco de magia de hoja de cálculo, ningún problema es insuperable. En este tutorial, aprenderás a crear un gráfico dinámico con formato condicional en el que los puntos de datos trazados se formatean automáticamente en función de sus respectivos valores reales.

Como ejemplo, eche un vistazo a este gráfico de columnas, que desglosa el rendimiento de un gerente de ventas durante un período de ocho meses. Observe cómo el color de la columna se ajusta automáticamente en función de su valor subyacente:

 

Introducción

Considere este conjunto de datos de ejemplo que contiene datos de rendimiento de ventas:

 

Antes de empezar, mueva el conjunto de datos de forma que tenga tres filas vacías encima de la tabla con los datos sin procesar. Las filas se utilizarán para establecer reglas de formato condicional.

Paso #1: Preparar los Datos del Gráfico.

Para que las cosas funcionen, es necesario añadir algunos datos adicionales del gráfico a la ecuación. Para su comodidad, eche un vistazo al aspecto que deberían tener los datos del gráfico al final de la fase de preparación:

 

Nuestro primer paso es definir los valores límite (condiciones) que deberían dividir los valores reales en múltiples categorías, haciendo posible graficarlos como series de datos separadas.

En la captura de pantalla anterior, por ejemplo, se puede ver que las cifras de ventas de un mes determinado tienen que estar comprendidas entre 90.000 y 150.000 dólares para considerarse satisfactorias.

Para crear las reglas, utilice las tres filas vacías situadas encima del conjunto de datos:

• Fila 1 (Etiqueta): Use estas celdas para etiquetar las reglas de formato.
• Fila 2 (Valor Mínimo): Los valores de esta fila definen el límite inferior de cada condición.
• Fila 3 (Valor Máximo): Del mismo modo, estos valores establecen los límites superiores.

 

Con este método, puede crear tantas reglas de formato condicional como desee, sólo asegúrese de que los rangos de valores no se superpongan.

A medida que avanza en la configuración de su gráfico, diseñe elementos de leyenda de gráfico personalizados que proporcionen contexto al gráfico. Introduce esta fórmula en C4 y cópiala en D4 arrastrando el controlador de relleno:

=C1&": Desde "&TEXTO(C2; "$#.##")&" hasta "&TEXTO(C3; "$#.##")

La fórmula crea una etiqueta dinámica de aspecto ordenado basada en las reglas de formato condicional establecidas previamente. La función TEXTO formatea los valores como moneda. Pero si tu tipo de datos es diferente, aplica esta fórmula en su lugar:

=C1&": Desde "&TEXTO(C2; "#.##")&" hasta "&TEXTO(C3; "#.##")

O ésta cuando trabaje con porcentajes:

=C1&": Desde "&TEXTO(C2; "#%")&" hasta "&TEXTO(C3; "#%")

 

Por último, encuentra los valores del gráfico. Escribe la siguiente fórmula en C5, cópiala en C12 y en las celdas correspondientes de la columna D:

=SI(Y(C$2<$B5;$B5<=C$3);$B5;NOD())

La fórmula compara un valor real dado en la columna B con los valores límite especificados con la ayuda de las funciones SI e Y. Si el valor está dentro del rango, se copia en esta columna. En caso contrario, la fórmula devuelve el error #N/D para evitar que el mismo valor se represente varias veces.

 

Paso #2: Configurar un Gráfico de Columnas.

Una vez reunidos todos los datos del gráfico, configure un  gráfico de columnas simple , o un gráfico de barras como alternativa:

1. Resalte todos los datos del gráfico excepto las columnas que contienen los valores reales y las reglas manteniendo pulsada la tecla Ctrl(A4:A12 y C4:D12).
2. Vaya a la pestaña Insertar.
3. Seleccione “Insertar gráfico de columnas o barras”
4. Elija “Columnas agrupadas/Barras agrupadas”

 

Como resultado, obtendrá un gráfico con puntos de datos trazados según las reglas de formato condicional, tal y como se había prometido:

 

Paso #3: Modificar los Valores de Superposición y Ancho de Espacio.

Antes de terminar este tutorial, solucione el problema de las columnas mal colocadas causadas por los valores vacíos invisibles (los errores #N/D) trazados en el gráfico.

Haga clic con el botón derecho del ratón en cualquiera de las columnas y seleccione “Dar formato a serie de datos” en el menú contextual que aparece.

 

En el panel de tareas, cambie la posición y la anchura de las columnas:

1. Cambie a la pestaña Opciones de serie.
2. Cambie “Superposición de series” a “100%”
3. Cambie “Ancho del Rango” a “60%”

 

Paso #4: Ajuste la Combinación de Colores.

Por último, añada los toques finales. Sin cerrar el panel de tareas Formato de Series de Datos, modifique el esquema de color del gráfico:

1. Vaya a la pestaña Relleno y línea.
2. En “Relleno”, elija “Relleno sólido”
3. Haz clic en el icono “Color de relleno” y elige el verde de la paleta de colores.

 

Una vez allí, vuelve a colorear las otras series de datos, cambia el título del gráfico y ¡tu gráfico totalmente dinámico con formato condicional estará listo!