Pivot tables are lovely. But sometimes they are hard to work with. Let’s say you are analyzing some HR data and want to see number of weeks worked in each hour classification. You have data like this:
And you want this.
Except, there is a teeny tiny problem.
The sort order on the classification is all messed up.
Here is a quick fix to get custom sort order on your pivot table row labels.
OK, I lied, There are actually two ways to do this.
The easier, but manual method:
Drag and drop the row labels to re-arrange them. Pivot table will remember this order even when you refresh. Of course there is a downside. In case you add some new values in the row label area, they will be at the bottom.
The smarter, but slightly longer method:
- First define the sort order in a list. Let’s say this list is in $I$3:$I$12
- Now, add an extra column to the original data.
- Using MATCH formula, find the order of each row label (in our case, classification) in the sort order list. Assuming classification is in D3, use =MATCH(D3, $I$3:$I$12, 0)
- Create a pivot table with data set including sort order column.
- Add sort order column along with classification to the pivot table row labels area.
- Add the usual stuff to values area.
- Set up pivot table in tabular layout.
- Remove sub totals
- Finally hide the column containing sort order.
- Your new pivot report is ready.
Good news for people with Excel 2013 or above:
- Once you have the sort orders table, just link to your original data set thru data model.
- You can then use sort order column in the pivot report directly. No need to write MATCH() formula.
- Refer to our relationship advice for Excel 2013 or above users.
Download Example Workbook
If all of this instruction is sort of tricky for you to follow, click here to download the example workbook.
How do you deal with pivot table sorting problems?
If my data is in Power Pivot, I rely on the excellent sort by feature. It is a god send. But when my data is in Excel (or I can’t use Power Pivot), I rely on the approach outlined in this post.
What about you? How do you deal with messed up sort order problems in your pivot tables? Please share your tips in the comments.
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 ""