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!
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!