First I want to say I am sorry to those waiting to see the dynamic chart scroll bar tutorial. My project launched this week and it has taken a lot of time. I should have part 2 out next week.
In the mean time, I wanted to send you off to the weekend with a Friday Excel Challenge. Here is the post from an analyst in an Excel user forum. Read through the description, copy and paste the data below into an Excel Spreadsheet and create a chart that satisfies the requirements. Post your solution in the comments below. Then come back on Monday to see how I solved this issue. Good luck!
% and Number within 100% Stacked Chart?
Is it possible to list both a numeric value and its percentage within each block of a 100% stacked chart? For example let’s say I’m trying to show how many Apples, Oranges, and Bananas (Y) that Sandra, Susie, Jamie, Jorge, and Alberto each have (x). If Sandra has 3 apples 1 orange and 1 banana, can I show within each block of her stacked chart 3 (60%), 1 (20%), and 1 (20%) respectively as opposed to only a number or only a percentage? Thanks a lot for any help!
Here is your sample data:
Also, don’t forget to subscribe to my blog and video channel so that you are sure to get the next post delivered directly to your email inbox.
Here is how I made my chart…
I copied the set of provided data into my workbook, and then made an identical table next to it to show the relative percentages. Assuming my original data table started in cell A1 and my new data table starts in cell F1, my new data table values have formulas like this: =B2/SUM(B$2:B$6) and the values are formatted as a %. This gives me the percent of the total each person has of each fruit type.
Next I made a 100% stacked column chart from the new data table. Then I copied (CTRL+C) the original data table and pasted it into my chart as a new series…right click on the chart and select paste, the new values will appear in your chart.
This added them to the stacks, which is not what I wanted…so I moved the new information to a 2nd axis. This causes it to overlap the original chart exactly. I added data labels so now both % and values show. I had to move the labels around a little so they did not overlap. I also formatted my chart to get rid of the chart junk…I removed the grid lines, removed the axis lines, and changed both vertical axis labels to white text to hide them. As a final touch, I changed the series colors to match the fruit…yellow for bananas, orange for oranges, and red for apples.