Excel Charts - Add totals labels to Stacked Column chart

Let's say that I have the following table of data: 

ExcelCharts_Add_Totals1 
I can make a Stacked Column chart with 3 series, one for each Vendor. The chart will look like this: 

ExcelCharts_Add_Totals2
But I want to display on the top of each bar, the total for the Week (the sum of each Vendor). To do that, I need to add a 4th series to the chart. For that I select the chart and right-click on top of it and select Source Data. On the Series tab, on the Series, click on the Add button to add a new series. For the Values range, select the cells where the values of Total are on our table. For the Name, select the cell where we have "Total". Click OK and you will get a chart like this:
 
ExcelCharts_Add_Totals3 
Select this new series on the chart, right click and choose Format Data Series. On the Data Labels tab, Label Contains mark Value. Then on the Patterns tab put Border-None and Area-None. Click OK. Now select the labels, right click on them, choose Format Data Labels and on the Alignment tab, Label Position choose Inside Base. Click OK. You need to adjust the scale of your chart so that it looks like the original one. In this case, I changed Maximum value to 450 instead of Automatic. As you can see, the “Total” series name still shows up on the Legend. You need to select the Total legend and delete. The final result will be this something like this:

ExcelCharts_Add_Totals4 
Hope this helps you create better charts in Excel!

27 comentários:

Andy said...

Great stuff! Thanks for sharing this fantastic tutorial on creating stacked column charts in Excel. You should join the Excel conversation on Facebook at http://www.facebook.com/office

Keep up the awesome posts.

Cheers,
Andy
MSFT Office Outreach Team

Daniel Rafeedie said...

This does not work in Excel 2007. Anyway you could provide these instructions for that?

Matthew said...

Daniel

Its essentially the same. Create the new series "Total". Then click into the chart on the new series portion, and add data labels. Format the data labels inside base, then right click the total section and format data series, go into the border tab and click no fill, and voila!

hadio said...

Still the maximum value at the y-axis is stuck at the biggest number as there is the total bar. I mean in Office 2007.

Plus, we don't want to end up customize the maximum value for y-axis, in case you have other solution. :)

Aseth said...

This is great!!!

Anonymous said...

Works great! Fast and easy. Good job

Anonymous said...

Great stuff, buddy! You can do it another way which will actually work better in some instances:
Simply change the "Total" category to the secondary axis (and then do as you said with the no color, no line, etc).

Cheers!
Khalid

Anonymous said...

Very helpful, thanks very much!

Anonymous said...

Thanks, this is helpful.

Anonymous said...

There's a million ways to do anything in excel. One variation I found that gets rid of issues with the scaling, is to select the data set for the total, and then change the graph type (for just that data set) to line. You then have a stacked column and line. You then go to properties and say "no line" and "no color" and you have the same outcome shown here without dealing with the scaling.

Anonymous said...

Pretty cool workaround

Anonymous said...

Is there any way to have the vendor with the largest total always at the bottom of the stack? For example, for week1, the stack order from bottom to top would be vendor 3, then vendor 2, then vendor 1. For week2, the stack order from bottom to top would be vendor 1, then vendor 3, then vendor 2.

Thanks,

jppinto said...

You would have to sort the columns individually to get that.

Anonymous said...

Wonderful Idea! Worked beautifully and I retained my status as Official Office Computer Nerd.

DaSha said...

This saved my day. Thanks a lot!

Linda said...

Any ideas why some of my 'totals' are not displayed?

Im using to display the total figure and % on a stacked bar chart.

I have put data labels 'inside base' and the majority are displayed but randomly some are not! Not because they are extremly higher or lower than the other totals and in random positions, the data source is not in a different format or anythig either - very confused!

jppinto said...

It's hard to try to find the cause of the problem without the file...

Linda said...

I'm new to this,can and how I post a file?
Thanks
Linda

jppinto said...

I Linda. Sorry for the late response. Here you can't post files. Regards, João

Linda said...

No worries, thanks very much for getting back to me!

Linda

Anonymous said...

Great information. Thank you!

Anonymous said...

You sneaky beautiful bastard

Anonymous said...

Thanks! You saved me so much time not having to fiddle around with text boxes.

Anonymous said...

Really help full

Anonymous said...

very use full.. good post

Michelle said...

How do you do this is you are working from a pivot table instead of a normal table?

David Linker said...

An even better trick is to follow the procedure above to create the totals series. But instead of making it "invisible, change it to a "line" type as follows:

Right click on the totals series, choose "Change Series Chart Type" and select the first line graph type.
Now, right click on the line, then choose "Format Data Series" then Line Colour and choose "No Line".

Finally, choose Format Data Labels, select Label Position "Above".

Now you can leave the x-Axis as automatic scale and it will still display the total at the top of each bar