Adding Percentages to the Stacked Column Chart
To display percentages instead of volumes on the stacked chart, we first need to calculate them by building another data table. Duplicate the table of volumes we used to create the chart in the first place, and enter a formula to calculate the percent of total for each region in each year.
In this example, the formula for the first region and first year is as follows:
The $ locks the Total row for the volume table so that we can drag the formula across and down to complete the data set. When you are finished, the percentage table should look like this:
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!