Break Excel table row down into sub-categories

Question

I have some data I am trying to graph into some charts, and am unsure how to layout the data.

I have a list of 'creations' and a count of how often they are used. However some creations, such as 'Soap', is made up of different types of soap, such as 'Glitter', 'Bug', and 'Rainbow'.

I would like to make a table that understands this sub-grouping, and can create charts accordingly.

Attached is how I have done it manually, where I use a separate table for Soap, and then I SUM that table for the 'Soap' row on the main table on the left.

Ideally I would like something similar to what is shown, however I also want to break all the creations down in the same way I have done it with Soap, without making a whole lot of different tables.

Any ideas or help would be much appreciated!

Answer 1

Before I start: Do you know what's worse than a pie chart? Two (or more) pie charts. And Pie-in-pie must be the worst combination of them all, because the reader cannot really see how the slices in the smaller pie relate to a slice in the bigger pie. So, word of the wise: use column or bar charts.

On to your topic:

You may want to look into pivot tables. If you use the main category and the sub category nested in the pivot table rows, then you can drill into the categories with a click on the + icon.

In your data source you enter the sub categories as in the screenshot below and build your pivot table/chart.