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
| A | B | C | |
|---|---|---|---|
| 1 | Project Name | Provider | Spend |
| 2 | APPLE | FRUIT | $ 73,460.68 |
| 3 | BURGER | FAST FOOD | $ 226,331.56 |
| 4 | HOTDOG | FAST FOOD | $ 328,968.64 |
| 5 | CHOCOLATE BAR | CONFECTIONARY | $ 11,611.08 |
| 6 | CARROT | VEGETABLE | $ 74,283.04 |
| 7 | CABBAGE | VEGETABLE | $ 93,143.96 |
| 8 | ONION | VEGETABLE | $ 74,283.04 |
| 9 | PEPPER | VEGETABLE | $ 70,799.56 |
| 10 | GARLIC | VEGETABLE | $ 48,601.80 |
| 11 | BREAD | CARBOHYDRATE | $ 23,220.60 |
| 12 | ROSEMARY | HERB | $ 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!):
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
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.
Pete, great solution. Similar to mine but better! I love the formula. Great Job
Steve=True
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
That is a good way to do it! You end up with the same results, but without the need for a formula or another table. Good work.