How to Add Dynamic Totals to a Stacked Column Chart in Excel
(Note: Suitable for users of Excel 2016, 2019, 2021, and Excel for Microsoft 365.)
Objective
Add dynamic totals to a stacked column chart.
Dynamic Totals Explained
When we use a stacked column chart to display data, we can add data labels to each ‘stack’ to show the value the stack represents. However, what if we want to see the total at the top of each stack?
Showing the total at the top makes the chart easier to read, and it also means we can delete the vertical axis, ensuring the chart looks cleaner and simpler.
Adding totals to stacks isn’t obvious in Excel. It requires a little bit of ‘out of box’ thinking. We also want to ensure that the totals are dynamic and update accordingly if the data in the chart changes.
In this article, we will walk through the process of creating dynamic totals for stacked column charts.
Related reads:
How to Generate Datasets in Excel with Functions and Utilities
Dynamic Arrays: How to Use the Excel SEQUENCE Function and Unstacking Records
How to Use the AGGREGATE Function in Excel
Create a Stacked Column Chart
Let’s start by creating a stacked column chart using some sales data for Q1 – Q4 for the years 2018 – 2021.
- Select the data in the table.
- From the Insert tab, in the Charts group, click the drop-down next to Column/Bar Chart.
- Choose the Stacked Column chart from the 2-D Column galley.
Format the Stacked Column Chart
Let’s tidy the chart up a little by applying some formatting.
Remove Gridlines
We can remove the gridlines from the chart to give it a cleaner appearance.
- Click on the gridlines to select them.
- Press the Delete key.
Move the Legend
We can change the position of the legend.
- Click on the chart.
- Click the + to the right of the chart to open up the Chart Elements panel.
- Click the arrow next to Legend.
- Select Top from the list.
Add a Chart Title
It’s a good idea to add a chart title so everyone knows what data the chart represents. Chart Titles are just text boxes so we can edit them in the same way.
- Click in the text box that says Chart Title.
- Type ‘Sales by Year and Quarter’.
Add Data Labels
Data Labels show the value that each ‘stack’ in the bar represents.
- Click on the chart.
- Click the + to the right of the chart to open up the Chart Elements panel.
- Click the arrow next to Data Labels.
- Select Center from the list.
Suggested reads:
What-If Analysis: How to Create One and Two-Variable Data Tables in Excel
How to Calculate Mean in Excel
How to Use Excel FORECAST Function
Add Dynamic Totals to a Stacked Column Chart
Now we have some basic formatting applied to the chart, we can add the dynamic totals. We want these totals to show at the top of each stack. Currently, the totals for each year don’t appear in the data we used to create the stacked column chart.
The first thing we must remember is that anything we want to add to the chart must be a series in our data.
Let’s start by adding a total row to our data. We can do this using a basic SUM calculation for each year.
- Click in cell B8.
- Type ‘Total’ and apply bold formatting.
- Click in cell B9.
- Type =SUM(B4:B7)
- Press Enter.
- Using the fill handle, drag the formula across to complete the totals for the remaining years.
NOTE: Notice the green triangle in the corner of each total cell. This usually denotes an error, issue or warning. In this case, Excel is letting us know that there is a number next to the cells we selected in the calculation that hasn’t been included in the formula. This refers to the year in row 3.
We can ignore this error.
- Select the total cells.
- Click the yellow warning triangle.
- Select Ignore Error from the menu.
Now we have the totals in the table, we need to add this new data as a series in our chart.
- Click on the chart.
- Right-click the mouse.
- Click Select Data.
- In the Legend Entries (Series) area, click Add.
We need to give our new series a name. We are going to call this series ‘Total’. As we have this in a cell, we can link to the cell.
- Click in Series name.
- Click cell A8 which contains the text ‘Total’.
Now we can add our series values.
- Click in Series values.
- Select cells B8:E8.
- Click OK and OK again.
Notice what has happened to the chart. The new series has been added as another stack. Effectively, the totals are showing as a stack in the chart, which isn’t quite correct. We want them to show as a number at the top of each stack instead.
To achieve this, we can change the stack to a marker by changing the chart type for the new series.
- Click on the ‘Total’ stack.
- Right-click the mouse.
- Click Change Series Chart Type.
- Scroll to the bottom of the Choose the chart type and axis for your data series section.
- Click the drop down-arrow next to Total.
- Choose Scatter from the gallery.
- Click OK.
The stack has now been replaced by a marker at the top of each stack. Excel has automatically added data labels to the marker to reflect the total.
Currently, the markers and totals are not in the right position. They are too close to the top stack making them hard to read so we need to make some further changes.
- Click on any of the data labels showing the totals.
- Right-click the mouse.
- Click Format Data Labels.
- From Label Options, in the Label Position area, click Above.
The data labels now appear higher up.
- Select the data labels.
- From the Home tab, in the Font group, apply Bold formatting.
Now we have our totals in the correct position; the markers are no longer required. We can remove these by changing the fill and outline color. The markers will still be there but will be invisible to the eye.
- Select the markers.
- From the Format tab, in the Shape Styles group, click the drop-down next to Shape Fill.
- Click No Fill.
- From the Format tab, in the Shape Styles group, click the drop-down next to Shape Outline.
- Click No Outline.
We can now turn off the vertical axis to make the chart look cleaner, amend the legend, and change the chart colors.
- Click on the vertical axis.
- Press the Delete key.
- Click on the ‘Total’ label in the Legend twice.
- Press the Delete key.
- Click on the chart.
- From the Chart Design tab, in the Chart Styles group, click the drop-down next to Change Colors.
- Choose a different palette from the gallery.
If the data in the table changes, the chart will update and the totals will move dynamically with the stack.
Also read:
3 Different Ways to Move Chart to New Sheet in Excel
How to Use Color Scales in Excel
How to Use Custom Number Formatting in Excel
Please visit our free resources center for more high-quality Excel and Microsoft Suite application guides.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT taught Excel and other business software for over ten years. You can access 150+ IT training courses for a low monthly fee.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
