Percentages in stacked chart.

AntBlabby8

Board Regular
Joined
Apr 18, 2002
Messages
197
I am trying to create a stacked chart in Excel for Item A and Item B. Together, they are 100% of sales. I am showing, 2003-2007. Each Item for each has its amount of sales in $ that I need to show, so I have chosen the Data Table option, below each stack. The problem is that I want the Data Labels to be PERCENTAGES. So that it will show 40% in one and 60% in the other and so on. When I go to choose PERCENTAGE from Data Labels in Chart Options, it is clouded out, it won't let me do it. Does anyone know a workaround for this? I am on a deadline and this is the last piece I need to finish my presentation. Argh!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
One workaround is to do your percentage calculations in two helper columns on your worksheet, and make THESE the source data for your chart, rather than the original data.

To set a % format, display the data labels, right click on them to format them, and on the Number tab, choose percentage.
 
Upvote 0
I don't totally understand. If we use the rows with percentages as the source data (and we do have those in rows right below the sales amounts), then how will the Data Table display the $ amounts? Is there a way?
 
Upvote 0
Sorry, I missed the bit about a data table - oops !

Perhaps use my earlier suggestion, and create the data table manually below the chart, rather than having it as an integral part of the chart.
 
Upvote 0
Oh, we can't do that. We have to have them together as they get linked into a Powerpoint Presentation. Plus, the data is dynamic, coming in from another program (Khalix), so it's always changing. I was wondering if there is a way to do it with a Secondary Axis, but can't figure out how. Like TWO STACKS OF TWO next to each other, then on the second one, do the percentages for the Data Point, make the pattern and lines invisiable. Just can't figure out how to do that, or if it would work. :)
 
Upvote 0
If you're willing to go down that route, consider using TWO charts, one on top of the other.

Make one use the source data, and the other use the percentages.
On the percentages one, make most things invisible, including the chart area, plot area, and so on, and set it so that it has no data table.
This might be a bit fiddly to set up, but it can be done, I've just done a basic version to test it.

By the way, when I said insert a data table manually, I didn't mean a text box, I meant a group of cells that would be immediately below the chart, that could contain formulas that pick up the relevant numbers, thus making it dynamic.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top