Sorting Values

Quick reference

• 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.