Forum Discussion
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 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
4 Replies
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
- 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
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
