Sorting Values

Quick reference

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