Something to share on ranking/sorting figures in a pivot talbe (not a question)

[fred]

My colleague sent me a pivot table with


1. multiple columns of monthly data (deparment, Names, Cost center names, cost center numbers, a few columns of spending by months) and a total columns

2. multiple rows of data with a subtotal (spending by individual within a department, then a grand total).


I was asked on

1. how to rank by department

2. how to rank by individual within the same department without making any changes on the source data worksheet.


While the first question was easy to fix, the second question took me about 2 minutes to fix (it felt like 2 hours though as she's about to present the data in a meeting).


My solution was:

First, I ranked the department subtotal from largest to smallest. Then on the "Row label" I removed the "Department" as one of the criteria (crazy, right?), leaving me with only individual data on the pivot table. Next I simply re-rank the column subtotal from largest to smallest. Add "Department" back to "Row Labels" and whaala! A pivot table displaying the monthly showing the biggest spender on the top and display the name in ranking by biggest spender within the "Department" on top.


Please let me know if there is a simpler way to do this cos it'd be awesome to learn more than less. Thanks!

 

[Guest]

Hi,


What you did sounds the simplest method to me, but I can't see why you needed to remove the Department field and then put it back again.


Pivot Table Row Fields are sorted left to right, so if Department was to the left of Individual, the PT will sort by Department, then by Individual within each Department.


Yard

 

[fred]

The problem was that when I received the file, "Department" was already in the pivot table with subtotal by department. And when we try to sort from largest to smallest, pivot only allows us to sort by subtotal level of department from largest to smallest. There is no secondary sorting from subtotal department down to individual level.


Note that the only column on the pivot table with a subtotal is the deparmental level reflecting several months of data, ie. no subtotal on the monthly columns, Excel gives us only the choice of ranking by Department name column and total columns. There is no choice for us to rank within the same total column.