"Dynamic" pie chars?

Markstar · #1 09-15-2015, 07:25 AM

Hi,

so I'm facing what I think is a common problem, but despite using Google I could not figure how to create the pie charts that I want.

What I want is a usual expense sheet with 3 columns: item, category, value:

Code:

Item    Type    Value
Apple    Fruit    1
Banana    Fruit    2
Kiwi    Fruit    3
Banana    Fruit    2
Vanilla    Flavor    7
Apple    Flavor    2
Soda    Misc    5
Broom    Misc    30
Soda    Misc    5
Cumin    Flavor    42

Now I want "dynamic" pie charts, depending on the number of categories. In the attached example, I have 3 categories, so I would want 4 pie charts:

1 overview chart, comparing the different categories (so in the example the pie chart shows 3 sections: Fruit, Flavor, Misc)
3 charts for each category, showing the items but still combining them if applicable (e.g. for Misc, the pie chart would have only 2 segments; Soda and Broom, split 25%-75%)

I have solve the first one, though I think it is not very elegant, by having an extra cell for each category and using SUMIF() to add the corresponding values.

But I have no idea how to get the others to work when it is unknown what is going to be entered under "Item". Do I have to use helper cells or is there a way to accomplish this in the pie chart wizard directly? I've tried for a while but can't get it to work, so any input would be greatly appreciated!

Thank you in advance for you help!

gebobs · #2 09-15-2015, 08:24 AM

I used a couple of pivot tables. The one on the Total tab gives the totals for each Item. The one on the By Item tab breaks it down by each. The chart can be filtered for each item either on the chart or on the pivot. If you don't want to bother with that, you'll have to create separate pivots and charts for each.

The file is in .xlsx format. Not sure what will happen if I save to .xls. Let me know if you can't open it.

Markstar · #3 09-15-2015, 10:43 AM

Quote:

Originally Posted by gebobs

I used a couple of pivot tables. The one on the Total tab gives the totals for each Item. The one on the By Item tab breaks it down by each. The chart can be filtered for each item either on the chart or on the pivot. If you don't want to bother with that, you'll have to create separate pivots and charts for each.

The file is in .xlsx format. Not sure what will happen if I save to .xls. Let me know if you can't open it.

Wow, that looks really nice!

I don't quite understand [the steps] how you got to the end result, so it will take me some time to grasp what you are talking about.

Thank you for your time! I'll write again once I have a better understanding of all the things I don't understand (particularly pivot tables).

gebobs · #4 09-16-2015, 10:02 AM

Pivot tables are fairly automated in my version, Excel 2010. Pretty easy and a powerful tool.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Error: "Changes made were lost...reconnect with server", when switching "from" field	randhurrle	Outlook	2	02-25-2015 06:51 PM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas	jocke321	Word VBA	2	12-10-2014 11:27 AM
When composing an e-mail how do I add "Page Layout" and "View" tabs to the ribbon	CensorTilSin	Outlook	1	12-11-2013 12:05 PM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM
Outlook Web Access: How to load the "Microsoft Dynamic Html Editing Control" Add-On?	tomtom67	Outlook	1	08-31-2009 12:23 AM