Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT.
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.
The process for creating these ranks is ridiculously simple. Just,
- Insert a pivot table from your data
- Add level and detail fields to row labels area (in this case, we shall add Department and Student to row labels area)
- Add field on which you want to rank to values area (ie Score)
- Right click on value field and go to “Show values as” > “Rank…”
- Set up base field as the lower level (ie Student in our case)
- Bingo, your ranking problem is sorted.
Here is a quick demo of how to use ranking feature in Pivot Tables.
Download Example Workbook:
Click here to download workbook with the few pivot table ranking scenarios. Check it out to learn more.
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.
11 Responses to “Use Alt+Enter to get multiple lines in a cell [spreadcheats]”
@Chandoo:
One more useful trick.......
In a column you have no. of data in rows and need to copy in the next row from the previous row, no need to go for the previous rows but entering Alt + down arrow, you will get the list of data, (in asending order), entered in the previous rows...
This is another great tip. I use this all the time to make sense of some *very* long formulas. As soon as the formula is debugged I remove the break.
Great tip Chandoo!
I use this feature often and it has even gotten the, "how did you do that" response.
Thanks!
@Ketan: Alt+down arrow is an awesome tip. I never knew it and now I am using it everyday.
@Jorge, Tony: Agree... 🙂
[...] Day 1: Insert Line Breaks in a Cell [...]
how can we merge a two sheet.
excellent idea. Chandoo you are genious
Hi chandoo,
I have used ctrl+enter to break the cell. But I did not get the result.
Please tell me how can i break the cell in multiple lines.
Hi, Ranveer,
Its not Ctrl+enter to break the cell, use Alt+Enter to make it happen.
hi Chandoo....
how we can use Alt+Enter in multiple rows at the same time please reply hurry i have lot of work and have no time and i m stuck in this. 🙁
Alt+J worked once 🙁
So I found another more reliable way:
=SUBSTITUTE(A2,CHAR(13),"")
Where A2 is the cell that contains the line breaks which the code for it is CHAR(13). It will replace it with whatever inside the ""