Preserving grand total sorting with a value filter in place.

September 3, 2010 8:16 AM

Is there a way to preserve grand total sorting in an Excel Pivot table when I've got a top 5 value filter on one of the columns?

I've got a pivot table with the following data (in left-to-right column order): customer group, customer ID, project number, employee(s) involved in the project, and in my data field is total revenue from the project.

There are multiple customer IDs per customer group, and multiple project numbers per customer ID. There are also multiple employees for each project number. I am sorting each column in descending order of total revenue.

When I add employee name, it gives the breakdown of revenue by employee. If I attempt to show only the top 5 employees, however, the overall project number sort changes, because it is now sorting based on the top 5 revenue total rather than grand total.

I don't need to see the grand total number -- in fact, I don't need to see the revenue total at all, but I would like my project numbers sorted by grand total, while only showing the top 5 employees involved.

Any thoughts/suggestions/magic solutions would be greatly appreciated.

I should note I'm very adept at copying and pasting VBA code into the editor, but as far as coming up with it myself, well, that's just not in my wheelhouse.

posted by undercoverhuwaaah to Computers & Internet (1 answer total)

I would do this in your data rather than in your pivot. You could add a column that indicates the RANK (function) of each Employee, then include Rank in your pivot and limit to 1 through 5.

I guess it depends on how much manual analysis is acceptable, because it would take 2 or 3 steps to identify the Rank.
posted by RobinFiveWords at 10:35 AM on September 3, 2010