Stacked bar chart with data from multiple columns – How would you do it?

Hello everyone!  Thanks for being a fan.  I am such a fan of Excel, that meeting MrExcel this week threw me off my schedule.  So I didn’t have time to finish Part 2 of the Scroll Bar tutorial.  I will post that early next week, so make sure and come back and check it out.

In the meantime, I wanted to get you this “How would you do it?” so that you can think about and try it over the weekend.

I saw this question in a forum recently and I thought my solution worked well.  You may want to use this technique in your next company Excel dashboard chart.  So try the following:

1) Read over the question and request.

2) Imagine this was a request from your executive team.





3) Copy the data to your own spreadsheet.

4) Fool around with creating your own chart replicating the request.

5) Post a comment below on how your solution

6) Come back on Monday and see the post and video on how I solved it and if it matches yours.

7) Leave a comment and tell me how you did or how I could have done it better.



We will all learn together.  Enough chat, read on and get to it:

User question:

 

“I have data in this format

ABC
1Project NameProviderSpend
2APPLEFRUIT$ 73,460.68
3BURGERFAST FOOD$ 226,331.56
4HOTDOGFAST FOOD$ 328,968.64
5CHOCOLATE BARCONFECTIONARY$ 11,611.08
6CARROTVEGETABLE$ 74,283.04
7CABBAGEVEGETABLE$ 93,143.96
8ONIONVEGETABLE$ 74,283.04
9PEPPERVEGETABLE$ 70,799.56
10GARLICVEGETABLE$ 48,601.80
11BREADCARBOHYDRATE$ 23,220.60
12ROSEMARYHERB$ 159,088.80

I want to present it so that it looks like this (although obviously in real life would be in proportion to the numbers!):Stacked Column Categories Sample





I’m really struggling – can anyone help? I would be very grateful.”

 

The requirements seem pretty straight forward to me, let me know in the comments if you have any questions, but have at it.  Also, thanks for all your great comments and emails I am receiving.  You are the best!

Steve=True





5 COMMENTS

  1. This one seemed pretty straight forward. I made a set of columns next to the original data, they are set up as follows:

    Fruit Fast Food Confectionery Vegetable Carbohydrate Herb

    Each column had an IF formula like this: =IF($B2=F$1,$C2,NA()). Which says IF ([Provider Name] = [Column Name], [Spend Value], NA ()). In English it means, if the value in the provider column matches the new column name, and then give me the Spend amount, if not, return a #NA.

    Then I made a stacked column chart based on the new table. I added data labels for the series and not the value, which labeled each block on the chart with the Project Name (Apple, Burger, Hotdog, etc…). Then I added a little formatting to the chart and got rid of the legend, and there you have it, a chart that looks just like the example provided.

  2. How about a pivot table on the data and then a basic chart off the pivot table. For the picot table the Axis is Provider, Values is Sum of Spend and Legend Fields is Project Name. Add data labels and format the data labels to series name and you are good to go.

    • Jake, thanks for the comment. Awesome solution! I don’t do many pivot table charts, I should take a look. Can you send me a picture of the final chart? I shot you an email with my email address.

      Steve=True

LEAVE A REPLY

Please enter your comment!
Please enter your name here