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.xlsx199.1 KB Sorting Values - Completed.xlsx
199.4 KB
Quick reference
Sorting Values
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
- Click More Options…
- Re-check the AutoSort checkbox
- Change the manual sort to Ascending/Descending as appropriate
- Click the drop down and select the values field
- 00:04 Now we've already seen how we can sort based on our row labels or
- 00:07 our column headings.
- 00:09 But what if we want to sort based on values.
- 00:11 For example, maybe we want to see our largest sellers at the top of the list.
- 00:15 As you'd expect, this is pretty easy to do, you can just right click and
- 00:18 say Sort > Largest to Smallest on the Grand Total column.
- 00:22 And notice that it sorts both our food and
- 00:24 our alcohol into the largest to smallest groups.
- 00:27 So that's pretty easy, that's the Grand Total column.
- 00:31 What happen if we did Grand Total row?
- 00:33 Let's go right-click over here, and say Sort > Large to Smallest.
- 00:37 Notice that it's now changed the order of our sales to the largest sales
- 00:41 on the left-hand side based on our Grand Total.
- 00:43 And we've moved our weeks into the order 3, 4, 2, 5,
- 00:46 1 because week three has the largest sales.
- 00:50 That's interesting.
- 00:51 But what if we wanted to do something different?
- 00:52 I'm going to resort this back into the proper week order
- 00:55 by going to smallest to largest, we'll toss the previous sort.
- 00:58 And now what I'd like to know is can I sort my data within these subcategories so
- 01:03 that's actually showing the largest of sales based on week 1 sales.
- 01:07 So let's go right-click, Sort > Largest to Smallest.
- 01:11 And notice that it's no longer sorted in the Grand Total order.
- 01:14 It's now sorted in the order for that individual column for both Food and
- 01:18 for Alcohol.
- 01:19 What if I now wanted to do something slightly different?
- 01:23 I can see that Burgers had the largest sale in week 2.
- 01:26 What if I wanted to sort my data for my weeks in order of my burger sales?
- 01:31 Well, the challenge is, when I go right-click and I do something like a Sort
- 01:34 > largest to Smallest, it's going to sort vertically, not horizontally.
- 01:39 So what I need to do for that is go to More Sort Options.
- 01:43 And inside the More Sort Options, you'll notice it is sorted largest to smallest,
- 01:47 but in here, I can also change it to go from left to right.
- 01:50 And at that point, it sorts it into a different order.
- 01:53 Now, at this point, what you would expect to happen is if I were to change something
- 01:57 in the underlying source data and I were to refresh the pivot table, it would
- 02:00 automatically go back and apply the sorts to get everything organized correctly.
- 02:05 So for example, let's go back and say, let's sort this largest to smallest.
- 02:09 So now we have our Burgers sorted and our order for
- 02:12 our different categories over here and we're largest to smallest.
- 02:15 But if when I update this thing,
- 02:17 I don't want to see the order of these categories change.
- 02:20 I know what they were, and even if the values over on the side here change so
- 02:25 that something else, maybe sandwiches, pops to the top, I don't want it to.
- 02:28 I want it to stay in this particular order.
- 02:30 How do I lock that down?
- 02:32 To do that,
- 02:33 what we're going to do is we're going to actually right-click on our category.
- 02:37 And we're going to go to Sort and we're going to choose More Sort Options.
- 02:42 Inside here we're going to now move into More Options.
- 02:46 And what you'll see is that we have the ability to actually turn off the automatic
- 02:50 sorting of the pivot table.
- 02:52 So the key thing here is we can set everything and
- 02:54 get it all sorted the way that we want to begin with.
- 02:56 And then we can turn off the automatic resorting algorithm.
- 03:00 So now I can say OK, and I can say OK.
- 03:03 And at this point, you're going to see that something strange happened.
- 03:07 My Food stayed sorted the way I expected it to, but my Alcohol changed.
- 03:11 This is the only way that you can actually go now and say, I'd like to see this
- 03:17 one sorted from smallest to largest and show Food largest to smallest.
- 03:21 Alcohol in the other order, which is pretty interesting.
- 03:26 Now, at this point if I were to go and update something in my data,
- 03:30 let's say that I was to come back over here and we're going to go and
- 03:33 put in 40,000 items inside our coffee here.
- 03:36 Or 400,000, we're going to come back and we're going to refresh things.
- 03:40 Well, we can see is that our sales have not resorted the pivot table.
- 03:43 So we're happy with the way that's actually working because we wanted to
- 03:46 keep things in this particular order.
- 03:47 But then at some point, you may look at this and go, wait a minute, but
- 03:50 now I want to get it back to where I expect it to be.
- 03:52 How do I reset this so that the automatic sorting does take place?
- 03:57 Unfortunately, it's not as easy as you might think.
- 03:58 You can't come back and right-click and say Sort, go into your More Sort Options,
- 04:03 and then just say let's go with ascending.
- 04:05 It doesn't work that way.
- 04:06 Unfortunately, we need to go back first into More Options,
- 04:10 turn off the sort automatically, or turn it back on, rather.
- 04:14 And then what we need to do is, in this case,
- 04:17 we want it descending based on the actual sales dollars.
- 04:20 At this point, it will now put it back into the manual sort order and
- 04:23 you'll notice that it's both applied them to both the Food and the Alcohol again.
- 04:27 So it's no longer separate.
- 04:29 So that's the tricks on how we play with both sorting vertically and horizontally.
- 04:33 There's lots of different options as well as locking it down to say, don't update
- 04:37 when the values go, and restoring it back to automatic updates as well.
Lesson notes are only available for subscribers.