Conditional Rank, the easy way [quick tip]

Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?

Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.

Two level (or multi-level ranking) with Pivot Tables

Let’s say you have data like this and you want to know what the department level rank of each student.

Download Example Workbook:

Do you use pivot tables for ranking?

I use them all the time for such complex, filter driven ranking in my reports. While we could use formulas to get similar results, I think Pivots shine when it comes to ranking, as they naturally resolve ties (without complex deduplication logic) and give results in short time. The only problem is we need to refresh them whenever there is a change in data. But it is a small price to pay for avoiding complex formulas.

What do you think? Do you use pivot tables for ranking your data? Share your stories in the comments section.

Also, check out our Pivot Table Central for all things pivot, right from basics to ninja-level stuff.