Tuesday, August 28, 2007

Excel doesn't make it easy, but does make it possible

AKA How to get Sum Totals to show up on a stacked bar chart.

I discovered this last week and used it for a client chart this week.

If you have a stacked bar chart, you can include data labels for the data series, but in addition, yoHow to get Sum totals above stacked bar chartsu'd like to have the total value of the bar shown above the top edge of it. By default, there's no way to do this in excel. However, you can quickly set it up yourself.

  • In your data, create an extra column or row called "Total" (however you have formatted your data, vertically or horizontally)
  • When you create the chart, include the "Total" data and make sure it's the last set of values in the series
  • Once the chart is created, you'll have the "Total" values as a huge extra bar on top of your bar charts. Now the quick fixing.
  • Right click on the data series for "Total", and select "Format Data Series". Set both the Border and Area formatting to "None". Close the dialog box.
  • Right click one of the data labels that's sitting out there in blank space now for "Total", and then select "Format Data Labels".
  • Click on the Alignment tab, and then look at Label Position. Change it to "Inside Base".

Voila! You now have the sum of the stacked bar chart showing up right above your data.

4 comments:

Anne said...

This is exactly what I need to do but in PowerPoint and the Excel instructions don't work. HELP!!

Consultant Ninja said...

Anne-

This works in PowerPoint as well. Add an additional row of data for the total amount. Chart it as a stacked bar. Right click the total data series, choose the Data Labels tab, and click values. Then change the data series format to no fill, no border.

Then right click on the data labels and move it down.

Philippe - Montréal said...

works also in Access.

thanks for the hint!

Anonymous said...

Works even better if you change the "Total" series to a line graph. Just select the "Total" series in the chart and go Chart Menu > Chart Type > Line - this way you don't distort the axis.

Tuesday, August 28, 2007