How to build a 100% stacked chart with percentages

In a previous video, we built a 100% stacked column chart, and added data labels to show actual amounts in an abbreviated custom number format.

The result is a chart that shows a proportional breakdown of each quarter by region.

Looking at the chart, you might wonder how to show the actual percentages in each bar?

This isn't hard to do, but it does take a little prep work.

Unlike a pie chart, which has a specific option to show percentages, a 100% stacked chart does not have this option.

But there is an option to pull values from other cells.

What this means is that we need to build our own formulas to calculate percentages, then pull these results into the data labels.

To start off, I'll copy and paste the whole table and remove the values.

Now to get the right percentage, I need to divide each value in the table above by the total value in the same row, which represents one quarter.

In other words, I need to divide C5 by the sum of C5 to F5.

I need to lock this reference carefully...I want the rows to change, but columns need to stay fixed as the formula is being copied across the table. F4 three times will do the job.

Now when I copy the formula throughout the table, we get the percentages we need.

To add these to the chart, I need select the data labels for each series one at a time, then switch to "value from cells" under label options.

Now we have a 100% stacked chart that shows the percentage breakdown in each column.

Whenever you create these kind of helper calculations for a chart, take care with the Switch Column/Row button. If I use it here to group sales by region instead of quarter, the chart looks fine, but the percentages are no longer correct.

To fix this problem, I would need to update the formulas in the helper table.