Locked lesson.
About this lesson
Sorting the values area of a PivotTable.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Sorting Values.xlsx195.5 KB Sorting Values - Completed.xlsx
195.5 KB
Quick reference
Topic
Sorting the values area of a PivotTable.
Where/when to use the technique
When you’d like to order the values in a PivotTable in order (either ascending or descending).
Instructions
Sorting column values (including Grand Totals)
- Right click a cell in the column you wish to sort by > Sort
- To have the largest numbers at the…
- Top: Choose Sort Largest to Smallest
- Bottom: Choose Sort Smallest to Largest
- The entire PivotTable value area will be re-sorted into the order of the selected column
Sorting Grand Total rows
- Right click any value in the grand total row (except the bottom right cell) à Sort
- To have the largest numbers at the…
- Left: Choose Sort Largest to Smallest
- Right: Choose Sort Smallest to Largest
Sorting values from Left to Right
- Right click a cell in the row you wish to sort by > Sort > More Sort Options
- Change the Sort direction to Left to Right
- To have the largest numbers at the…
- Left: Choose Sort Largest to Smallest
- Right: Choose Sort Smallest to Largest
Toggling Automatic Sorting
Turning Off Automatic Sorting
- Right click one of the row label fields > Sort > More Sort Options > More Options…
- Uncheck the AutoSort checkbox
Restoring Automatic Sorting
- Right click one of the row label fields > Sort > More Sort Options
- Change the manual sort to Ascending/Descending as appropriate
- Click the drop down and select the values field
- Click More Options…
- Re-check the AutoSort checkbox
- 00:04 Now what we're gonna look at is sorting the values area of a pivot table.
- 00:09 And this is slightly different than sorting the row or
- 00:12 column labels, but equally valuable.
- 00:15 We have two ways we can sort.
- 00:16 We can sort vertically or we can sort horizontally.
- 00:19 So if I wanted to sort vertically by the Grand Total column, what
- 00:25 I would do is I would come here and say right-click > Sort > Smallest to Largest.
- 00:31 And you'll notice that all the values,
- 00:33 all the rows are re-sorted based on the Grand Total column, but notice also that
- 00:38 even though I went smallest to largest, my sub totals did not re-sort.
- 00:41 And that's good, because it allows me to sort within the sub total band.
- 00:45 So, if I wanted to do this guy here, this is a different grouping level and
- 00:49 a different sort level in our list.
- 00:52 So, I can sort ascending by alcohol and I can sort descending
- 00:58 within the actual category itself if I want to do that, which is kinda nice.
- 01:04 I can also sort by a specific individual column.
- 01:07 I don't have to use the Grand Total.
- 01:09 Let's say that we wanted to sort the entire categories level
- 01:14 in ascending order based on Week 3's numbers.
- 01:18 So I select one of the values of Week 3 Sort > Smallest to Largest.
- 01:22 It re-sorts the entire pivot table based on that logic.
- 01:25 You'll notice this column looks all completely wonky.
- 01:28 But this one is ordered very nicely, which is great.
- 01:32 If I go to the very bottom and
- 01:34 I right-click on one of these cells in the Grand Total row.
- 01:37 And I do a Sort from largest to smallest, it actually sorts horizontally.
- 01:44 It says, well, there's no point in sorting vertically because you're in
- 01:46 the Grand Total row.
- 01:47 So that may not be what you want.
- 01:50 So we can sort smallest to largest, or largest to smallest rather or
- 01:53 the other way around.
- 01:54 Here's smallest to largest, where we're actually growing things up.
- 01:56 Probably doesn't make sense if you've got week numbers across the top,
- 01:59 but you never know.
- 02:00 It might if you have some certain logic for doing that.
- 02:04 What about trying to sort horizontally inside the values area?
- 02:09 Maybe I want to see my burger sales so
- 02:12 that my largest burger sale is on the left-hand side here and goes this way.
- 02:16 That's the most important thing to me.
- 02:19 Well, the way that we do that is we right-click and we go to Sort.
- 02:25 We go to More Sort Options.
- 02:26 This one's a little bit more hidden, but you'll notice that we can actually change
- 02:30 this from left or set it to left to right, large to smallest.
- 02:34 This is already left to right because I was in the Grand Total row, but
- 02:38 if I now say left to right, there we go.
- 02:41 All right, what about controlling automatic sorting?
- 02:45 Right now you'll notice that Sandwiches is below Entrees.
- 02:48 It's sorted in order of Grand Total.
- 02:51 We'll just prove that right now.
- 02:52 Sort > Large to Smallest.
- 02:54 There we go.
- 02:55 So if we went to our our data and
- 02:58 we increased our sandwich revenue by 20,000 or to $20,000 and
- 03:02 went back and re-sorted it, we would expect Sandwiches to jump above Entrees.
- 03:07 And it does, so sorting is automatic right off the bat.
- 03:10 What if I didn't want that?
- 03:11 What if I wanted these values to not change?
- 03:15 We can do that too, but we have to right-click on a row label to do this.
- 03:20 So now we'll go to Sort, we'll go to More Sort Options.
- 03:24 In this case, instead of doing descending by sum of amount,
- 03:28 I need to click the More Options button and change it so
- 03:31 that it does not sort automatically every time the report is updated.
- 03:36 Now we can say OK.
- 03:37 And then we can say OK again just to clear it in.
- 03:40 And now, if I go back and reset my sales revenue back to $4.50,
- 03:46 go back into the report.
- 03:48 Right-click and Refresh.
- 03:50 You'll notice that we are definitely not re-sorting our report, and that's great.
- 03:56 But now I've decided, you know what,
- 03:57 maybe I really do wanna sort this thing automatically every time it updates.
- 04:00 So what I'm gonna do is I'm gonna right-click,
- 04:02 I'm gonna say Sort, I'm gonna sort from largest to smallest, there we go.
- 04:06 We've got our entrees in a good place, and now we're gonna go back and
- 04:09 just check that it worked.
- 04:11 So back to Sandwiches, back to 20,000,
- 04:15 back to our report, and right-click and Refresh.
- 04:20 Oh, we've got a problem.
- 04:22 This did not re-sort, so it's still stuck in Manual mode.
- 04:25 To get it out of Manual mode takes a little bit of work.
- 04:28 In order to do this, we need to go back into the row label.
- 04:34 Go back to More Sort Options.
- 04:36 You'll notice that it's Manual.
- 04:38 Now, we really wanna change this to descending,
- 04:40 but we have to do something first.
- 04:42 Before we do that, we need to go into More Options and
- 04:45 we need to recheck the AutoSort button.
- 04:48 Now we can say OK, now we can take it out of Manual mode,
- 04:52 but it's not good enough to just click Descending,
- 04:54 we have to change it to Descending by the sum of our amount.
- 04:59 Now we can go back into More Options again and just check,
- 05:01 yeah, it's doing it by the grand total, that's cool,
- 05:03 it's not gonna be doing it by a specific column, we'll say OK and OK.
- 05:09 It re-sorts for us automatically.
- 05:11 That looks good.
- 05:12 Let's go back and test it.
- 05:14 We'll go back, put 4.50 in here.
- 05:18 Back over to Report 2, right-click, and Refresh.
- 05:22 Perfect, it's working, so setting it into Manual Sort mode is relatively easy.
- 05:27 Getting it back out can be a little bit more work.
Lesson notes are only available for subscribers.