Oct 19 2018
05:12 PM
- last edited on
Jul 12 2019
11:15 AM
by
TechCommunityAP
Oct 19 2018
05:12 PM
- last edited on
Jul 12 2019
11:15 AM
by
TechCommunityAP
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
Oct 19 2018 08:33 PM
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
Oct 22 2018 08:46 AM
Oct 22 2018 08:30 PM
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!
Nov 02 2018 04:30 PM
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