Forum Discussion

Copper Contributor

Oct 20, 2018

need help showing multiple values based on a drop down

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 th...

Silver Contributor

Oct 20, 2018

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 https://www.youtube.com/watch?v=iIKqeaFvltQ.

You also can use Power Query (aka Get & Transform in Excel 2016 and above) to do so, and this is demonstrated https://www.youtube.com/watch?v=LMLuzmgziYo.

Hope that helps

Products Table.xlsx13 KB

darronsmom

Copper Contributor

Oct 22, 2018

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

Wyn Hopkins
MVP
Nov 02, 2018
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
Haytham Amairah
Silver Contributor
Oct 23, 2018
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!