Creating a Pivot table from data with Sub columns/headers

[taketad]

Having a hell of a time figuring this one out. I'm far from an advanced user, but I'm typically pretty comfortable.

I've got some data with several different categories and need a pivot table to reflect that, such that I can filter by category of both row and column. For example: (pardon the crude attempt at formatting)

[------][----][--------- Vegetables --------][---------- Fruit---------][----------Meat------]
[State][City][Lettuce][Cucumber][Pepper][Apple][Orange][Peach][Beef][Pork][Poultry]
[OH ][Cinc]
[OH ][Tole]
[OH ][Colu]
[OH ][Clev]
[CA ]
[CA ]
[NY ]
[NY ]

Ideally, I'd be able to apply the typical filter by State, but also be able to apply a filter for Food Type, which would allow me to select "OH" and "Vegetables", or what have you.

Thanks!

Oh, and I'm using Office 2016. That's probably important to mention.

[MarvinP]

Hi taketad and welcome to the forum,

See if this attached file helps explain what you want. You need to drag two headers to the columns box in the Pivot.
Pivot with two columns.xlsx

[taketad]

Thanks!

Unfortunately that's not really what I was looking for. Instead of nested categories, is it maybe possible to create sets of fields? Ultimately, all I really want is to be able to select "Vegetables" and see the data for each individual column that's categorized as a Vegetable, just like I can do with rows. I didn't think it'd be this difficult!

[MarvinP]

Hey taketad,

I'm not sure I understand what you have or want. Read / watch all these to see how excel works best with TABLES of data:

https://support.office.com/en-US/Art...c-30c5121fb05e