Formato condicional para crear un gráfico de barras 100% apiladas en excel

Supongamos que tiene datos de ventas durante 12 meses para tres productos (P1, P2 y P3). Ahora desea crear un gráfico de barras 100% apiladas en Excel para cada mes, con cada producto resaltado en un color diferente.

Algo como se muestra a continuación:

Ahora vamos a crear este gráfico:

En primer lugar, vamos a llenar la tabla de ventas con números aleatorios del 10 al 100 utilizando la función ALEATORIO.ENTRE, así quedaría la fórmula para la primera celda que en este caso es C4:

=ALEATORIO.ENTRE(10;100)

Luego, debe calcular el porcentaje que representa cada producto del total mensual.

Para hacer esto, primero crea tres columnas auxiliares (cada una para P1, P2 y P3) para todos los 12 meses. Ahora simplemente calcula el % de valor para cada producto. He usado la siguiente fórmula:

=(C4/SUMA($C4:$E4))*100)

Una vez que tenga estos datos en su lugar, vamos a sumergirnos de inmediato para hacer una tabla apilada:

Seleccione 100 columnas y establezca el ancho de su columna en 0.1.
Seleccione estas 100 celdas en la primera fila de datos (K4: DF4) en este caso.
Vaya a Inicio à Formato condicional à Nueva regla.
En el cuadro Nuevo diálogo de reglas de formato, haga clic en la opción ‘Utilice una fórmula que determine las celdas para aplicar formato’.

En ‘Editar una descripción de regla’, coloque la siguiente fórmula y establezca el formato en Azul (Botón ‘Formato’ à Pestaña ‘Relleno’)

= COLUMNAS($K$4:K4)<=$G4

Ahora seleccione nuevamente el mismo conjunto de celdas y vaya a Inicio à Formato condicional à Nueva regla à ‘Usar una fórmula para determinar qué celdas formatear’. Ahora ponga la fórmula que se menciona a continuación y establezca el formato en color verde.

=Y(COLUMNAS($K$4:K4)>$G4;COLUMNAS($K$4:K4)<=($G4+$H4))

Y finalmente, repita el mismo proceso y agregue una tercera condición con la siguiente fórmula y establezca el formato en color naranja.

=Y(COLUMNAS($K$4:K4)>($G4+$H4);COLUMNAS($K$4:K4)<= 100)

Ahora haz clic en Aceptar y obtendrás algo como se muestra a continuación:

Puedes ocultar las columnas de ayuda, y tienes tu gráfico dinámico de barras 100% apilado listo a tu servicio.