need help showing multiple values based on a drop down

[darronsmom]

Hi all,

I need to send out a spreadsheet where a user can see a product, product category, and all subcategories for that item (see attached for what I have in mind).  I think a pivot table is the best way to get this, but I can't figure out how to get more than just the product and the main category  (in my sample, that would be the Apple and Fruit.  I don't know how to show the subcategories in the same column or row as the product.  I'm not super picky about the format, but it does need to operate that way.  I have about 50 products, split between 5 categories, and 8 subcategories for each.  How can I get them to to show in a nice neat format?

Thanks in advance

[#2]

Hi Marlise,

To be able to summarize this data in a Pivot Table, you must fill down the values in columns: Product and Category as the below screenshot.

To learn how to fill down quickly, please watch this video.

You also can use Power Query (aka Get & Transform in Excel 2016 and above) to do so, and this is demonstrated here.

Hope that helps

[#3]

Hello Haytham, Thank you for your response. I am actually not looking for the sum, or count- I'm looking for the product to display the way it is in my attachment. If a product is an apple, I need it to display both Fruit and Red and Green. I don't care if it's in the same column or different columns, but I need to display both subcategories. Does that make sense? Thanks Marlise

[#4]

Hi Marlise,

Sorry, but it's still not clear enough!

Anyway, can you provide a sample of your original data which include all the categories and subcategories?

But, if you like to report the data to someone else, it's common to report a summary of this data as I did in the previous reply using Pivot Table, not the raw data itself!

[#5]

Hi Marlise

Bit late to this conversation but I think the trick you need is to go to Design > Report Layout > Show in Tabular form

Then next to that button is Subtotals > Do not Show