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

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:

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:

Hope this helps you create better charts in Excel!
31 comentários:
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
This does not work in Excel 2007. Anyway you could provide these instructions for that?
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!
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. :)
This is great!!!
Works great! Fast and easy. Good job
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
Very helpful, thanks very much!
Thanks, this is helpful.
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.
Pretty cool workaround
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,
You would have to sort the columns individually to get that.
Wonderful Idea! Worked beautifully and I retained my status as Official Office Computer Nerd.
This saved my day. Thanks a lot!
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!
It's hard to try to find the cause of the problem without the file...
I'm new to this,can and how I post a file?
Thanks
Linda
I Linda. Sorry for the late response. Here you can't post files. Regards, João
No worries, thanks very much for getting back to me!
Linda
Great information. Thank you!
You sneaky beautiful bastard
Thanks! You saved me so much time not having to fiddle around with text boxes.
Really help full
very use full.. good post
How do you do this is you are working from a pivot table instead of a normal table?
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
Very helpful! Thank you!
I do not like Excel. I find it easier to manually download a ready-made template with chart and paste it into your presentation. So much easier. I also advise you to do so. On this site you will find ready-made templates http://charts.poweredtemplate.com/powerpoint-diagrams-charts/ppt-powerpoint-education-charts/0/index.html.
just information we only provide information for those who need it
A. posisi berhubungan agar cepat hamil
B. makanan dan minuman agar cepat hamil
C. panduan agar cepat hamil
D. cara agar cepat hamil
E. cara agar cepat hamil setelah selesai haid
F. cara alami untuk segera mendapat kehamilan
I thank you for the information and articles you provided
# sistim reproduksi wanita
# cara memperlancar menstruasi
# ciri ciri pubertas pada perempuan
Post a Comment