AJP Excel Information AJP Excel Information

Display Total within a stacked column chart

 
 


 

 
Using a dummy data series on the secondary axis.
Here is our data set. The actual chart data is in the range A1:D5
I have added formula to E2:E5 to store the cumulative total to be displayed.
 
A
B
C
D
E
1
 
Data A
Data B
Data C
Total
2
Qrt 1
2
4
4
=SUM(B2:D2)
3
Qrt 2
2
2
3
=SUM(B3:D3)
4
Qrt 3
3
1
2
=SUM(B4:D4)
5
Qrt 4
4
1
1
=SUM(B5:D5)
Select the range A1:E5 and use the chart wizard to create a standard Stacked column chart.
Select the data series 'Total' and via the Format Data Series dialog (CTRL+1) change the Axis to Secondary axis.
This will make the 'Total' data series appear in front of the other data series.
To finish off format the 'Total' data series to have no border and no pattern. This will allow the other data series to be seen. Also apply Value data labels.
To remove the extra data series form the legend see "Delete legend entry"
Using a dummy series on the same axis
This time our data set includes 2 helper columns. E2:E5 contains a constant value that will be used to display the data labels. F2:F5 contains formula for the cumulative total.
 
A
B
C
D
E
F
1
 
Data A
Data B
Data C
Dummy
Total
2
Qrt 1
2
4
4
2
=SUM(B2:D2)
3
Qrt 2
2
2
3
2
=SUM(B3:D3)
4
Qrt 3
3
1
2
2
=SUM(B4:D4)
5
Qrt 4
4
1
1
2
=SUM(B5:D5)
Format the 'Dummy' data series as before, setting the border and pattern to none.
Apply the values data labels.
Format the 'Dummy' data series as before, setting the border and pattern to none.
Apply the values data labels
In order to get the data labels to display there cumulative total instead of there true value we need to alter the text.
Static method:
Select the data labels and then select an individual data label. Simply edit the text in the data label.
Dynamic link method:
Select the data labels and then select an individual label. Go to the formula bar and enter '=' (without quotes) and then select the cell to be linked. This will enter the full cell address. If you want you can just type the address directly in the formula bar.
Here are 2 free add-ins to make the chore of linking data labels to cells easier.
Rob Bovey's Chart Labeler
John Walkenbach's Chart Tools
The use of a dummy series on the same axis is suitable for use with 3d stacked column charts
   
   
   


Created August 2004
Last updated 5th August 2014 


Return to main page Chart Section VBA section Fun and games section Forum files Tips section Links section Book section Site information Site Search RSS feed Top of page


Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2016 Andy Pope