Pivot table with multiple levels to sort by?

I have several categories of data to sort. I want to sort them within a pivot table.

Categories to sort from in the report filter area at the top (names of fields changed to protect my company's privacy):

1.) National managers
2.) Regional managers
3.) Local managers
4.) Local sales firms
5.) Local sales agents
6.) Local plan customer service representatives.

Then, there will be about ten row labels, displayed from left to right. Here's an example:

REPORT FILTER 1
REPORT FILTER 2
REPORT FILTER 3
REPORT FILTER 4
REPORT FILTER 5
REPORT FILTER 6

ROW LABEL 1 ROW LABEL 2 ROW LABEL 3 ROW LABEL 4 ROW LABEL 5 ROW LABEL 6

So this is what I want to be able to do:

When you select a name from "report filter 1," (in this example "national managers," you then only see the plans overseen by regional managers managed by that national manager. if you go further and select report filter 3 (local managers), you only see the plans of that local manager. And so on.

When you select, say, "National Manager A," "Regional Manager B," and "Local Manager C", you see in the pivot table below ONLY the plans that fit ALL THREE filters. You can then also use filters on the row labels to sort the data by, say, dollar amount or contract number (two of the row labels).


I've been told this would be much easier in Access, but my boss is insistent I use Excel.

Ideas? thanks for your help!

Here is an example. Obviously this is fake data :-)

What I'd like to do is when you select "National Manager," you see only the regional managers under that national manager. When you select a regional manager, you see only the branch managers under that regional manager.

Then, whatever you have selected in the report fields, you only see the contracts under those managers. Also, you should still be able to use filters to sort by total asset value, contract number, fee change percentage, etc.

I hope that makes more sense.

Thanks for helping!!

I have never used slicers before. I do not know if I can save it as .xlsx. Can anyone with Excel 2010 do this? I do have Excel 2010. I read about slicers and thought that might be the solution, but when I tried to implement them I couldn't figure it out. Advice? Thanks!

That would definitely work! Is there a way, however, to keep the report filters as drop-down lists? If not that's OK- I do have leeway with the specific appearance of the pivot table- but since this is being used by hundreds of managers and agents across the US it would be better if it's in a drop-down list (in case they don't know how to clear filters in the slicers). Basically I need to make this as user-friendly as possible-- all those managers are going to use this to find and sort their specific plan data.

Also, can you explan HOW you made these slicers, so I understand the process? Thanks so much!

Also, when I try to filter Total Asset Value, bond portion, stock portion, etc from largest to smallest, I am unable to do this. How might this be possible? They need to be able to sort their plans by specific dollar amount data. Thanks

Can you explain how you did the slicers? Currently, I have a spreadsheet with filters, but I can also submit this to my boss as an alternative in case they like it better and think it will be user-friendly enough.

The code required- I am assuming it would be too complicated to try on my own? I'm willing to learn if you think it's possible for someone who has never programmed (besides coding with HTML and JavaScript).

Thanks

I did get as far as inserting the slicers, but couldn't figure out how to make them sort properly. Remember I've never used slivers before- could you be more specific in how you created them, and how you made them such that when you click on the national manager's name, for example, that you only see the district managers associated with that national manager, and when you select a branch manager, you only see the branch managers associated with that regional manager?

As to the code, this is sample data-- There are actually six headings I need in the real spreadsheet (possibly seven), with differene names from these (the real titles are company-specific and I need to protect the company's privacy), and there are at least ten row headings in the actual table of data (ten pieces of data I want displayed when you select the district manager, regional manager, etc that you want).

Also, it would be ideal that you also see the categories from the pivot table report filter headings (district manager, regional manager, etc) displayed below in the table alongside all the other ten pieces of data. So If you are looking at the plans of District Manager A/Regional manager A/Branch manager A (all selected), you still see that info down below in the table. Does that make sense? It's hard to explain I think.

Your answer was very thorough, but as a beginner with slicers/pivot tables I have a few clarifying questions:

1.) If I use slicers, are you saying I don't use any report filter headings at all? i.e. i don't have the District Manager, Regional Manager, and Branch Manager dragged into the report filter field in the field headings tool from the pivot table menu?

2.) I've never used macros. I know the concept, but I've never tried them in practice.

3.) Where do I find the "events?" I've never filtered by event.

I think with these three questions addressed I can figure out how to do this.

Thanks for being so thorough and helpful by the way!!

Thanks! I look forward to trying this!! One more Q:

If I have more than 3 headings (which I will), would I modify "For n = 1 to 3" to read "For n = 1 to 6" (for 6 headers?)


I am so excited to try using code in Excel for the first time :-) Thanks for your patience and help with this! I'd like to learn to program in the near future, and this is nice practice

Oh perhaps I didn't explain right. I will have more than three report filter columns. I.e. there are more kinds of managers than three managers. Either 6 or 7. So I'm wondering if I just change the "For n = 1 to 3" to "For n = 1 to 6" or "For n = 1 to 7" to fit the number of report filter columns I have?

I am so thankful so far to the help from this forum- everyone seems wonderful, and the time questions are answered is so fast!