Conditional Formatting to Create 100% Stacked Bar Chart in Excel

A colleague asked me if there was a way to create a dynamic in-cell 100% stacked bar chart in Excel for three product sales.

As usually is the case, there were thousands of rows of data. There is no way she could have used Excel in-built charts as that would have taken her ages to create charts for each set of data points.

This got me thinking, and fortunately, conditional formatting came to the rescue. I was able to quickly create something neat that fits the bill.

Creating a 100% Stacked Bar Chart in Excel

Suppose you have sales data for 12 months for three products (P1, P2, and P3). Now you want to create a 100% stacked bar chart in Excel for each month, with each product highlighted in a different color.

Something as shown below:

How to create this:

First, you need to calculate the percentage breakup for each product for each month (I was trying to make a 100% stacked chart remember!!).

To do this, first create three helper columns (each for P1, P2, and P3) for all the 12 months. Now simply calculate the % value for each product. I have used the following formula:

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

Once you have this data in place, let’s dive in right away to make the stacked chart

  1. Select 100 columns and set their column width to 0.1.
  2. Select these 100 cells in the first data row (K4:DF4) in this case.
  3. Go to Home –> Conditional Formatting –> New Rule.
  4. In New Formatting Rule Dialogue box, click on ‘Use a formula to determine which cells to format’ option.
  5. In the ‘Edit the Rule Description’ put the following formula and set the formatting to Blue (in ‘Fill’ tab)
=COLUMNS($K$4:K4)<=$G4

  1. Now again select the same set of cells and go to Home – Conditional Formatting – Manage Rules. Click on New Rule tab and again go to ‘Use a formula to determine which cells to format’ option. Now put the formula mentioned below and set the formatting to green color.
=AND(COLUMNS($K$4:K4)>$G4,COLUMNS($K$4:K4)<=($G4+$H4))
  1. And finally again repeat the same process and add a third condition with the following formula and set the formatting to orange color.
=AND(COLUMNS($K$4:K4)>($G4+$H4),COLUMNS($K$4:K4)<=100)
  1. Now click Ok and you would get something as shown below:

Hide the helper columns, and you have your dynamic 100% stacked bar chart ready at your service.

Now it’s time to bask in the glory and take out some time to brag about it

Add a Comment

Your email address will not be published. Required fields are marked *