Adding Totals Data Labels to an Excel 2007 Stacked Bar Chart

1. Adding Totals Data Labels to an Excel 2007 Stacked Bar Chart Robert Rosen

2. In an Excel 2007 stacked bar graph, you can use the <add data labels> feature to show the numeric values of each component in the stack, like this…

3. …but what if you want to show the total value of the entire stack, like this? Excel doesn’t have a “show totals” option • Answer: use a Trick!

4. Step 1: Add Data Labels Click on the border of the chart to select it, then Chart ToolsLayoutData Labels (in this example, we select “Center”) Dots show when chart is selected

5. Step 1 (contd.)

6. Step 2: Add a totals column to the source data (if not already existant)

7. Step 3: Add a new data series to the chart for the totals Right-click in the chart to bring up this menu, then click “Select Data”

8. Step 3 (contd)

9. Step 3 (contd)

10. Step 3 (contd.)

11. Step 4: Change the totals series to a line graph Select the Total series and right-click to bring up this menu, then click “Change Series Chart Type”

12. Step 4 (contd.) Select a Line graph

13. Step 4 (contd.)

14. Step 5: Move the labels to above the series Select a label and right-click to bring up this menu, then “Format Data Labels”

15. Step 5 (contd.) Select “Label Options”, then for Label Position, “Above”

16. Step 6: Hide the line Select the line and right-click to bring up this menu, then “Format Data Series”

17. Step 6 (contd) Select “Line Color”, then “No line”

18. Step 6 (contd)

19. Step 7: Delete the legend entry Select the Totals legend text and right-click to bring up this menu, then “Delete”

20. Step 8: Congratulate yourself on your success!