Basic Sorting

Quick reference

• 00:04 In this video, let's look at how to sort values in a pivot table.
• 00:08 And you'll notice that I've got a very simple pivot table built up here so far.
• 00:11 We've got week across the top.
• 00:13 We've got our sales dollars across the first row here, but
• 00:16 we don't have any row context whatsoever.
• 00:19 So I'm going to go and add one, we're going to grab Class and slide into Rows.
• 00:23 And what you'll notice is that as soon as I add my Class in here,
• 00:26 it sorts the Class in alphabetical order with Alcohol first and Food second.
• 00:31 And if I go to Category and
• 00:32 slide that under Class, it's also going to sort those in alphabetical order as well.
• 00:38 But what if I want Food to come before Alcohol?
• 00:41 It's no problem.
• 00:42 We'll right-click on one of those two items, either Alcohol or Food,
• 00:46 we'll go to Sort, and we'll choose to sort it from Z to A, and
• 00:49 this will actually put Food up top.
• 00:51 The nice thing here, if I hit the Refresh button on the pivot table,
• 00:54 it will always go through and sort these into reverse alphabetical order.
• 00:58 If I get a new category like say beverages,
• 01:00 it would then go food, beverage, alcohol.
• 01:03 So it's automatically going to put those in place.
• 01:05 Notice though that it did not sort the fields for my categories.
• 01:10 They're still in alphabetical order, not reverse alphabetical order.
• 01:13 Can I change that?
• 01:14 Sure, I can right-click, sort, Z to A,
• 01:18 and you'll notice now that it doesn't just sort the categories under Food,
• 01:23 it sorts the categories under Alcohol as well.
• 01:26 And this is a key thing here, is that this sorting is done at a field level,
• 01:30 it is not done at a specific group level within a particular sub-total.
• 01:34 What if I decide though that for whatever reason, I want to reorder this stuff and
• 01:39 I want to put Draft Beer right down near the end under Bottled Beer?
• 01:42 Well, that's obviously not alphabetical in any way.
• 01:46 So the way I do that is I can click here, and
• 01:48 when I move my mouse over the bottom or top of the border,
• 01:51 you'll see that will get a very special arrow that actually comes up.
• 01:55 It's this four pointed arrows with our air clicker pointing towards the middle of it.
• 01:58 If we left-click and
• 02:00 drag, you'll see that we can now move this into what's called a manual sort order.
• 02:05 And the thing here,
• 02:06 it's still going to maintain that order when we refresh things.
• 02:10 The thing is, though, this field is now sorted manually.
• 02:13 So at that point, if I do want to go back to A to Z or
• 02:16 Z to A, I've actually gotta get it out of manual mode.
• 02:20 How do I do that?
• 02:21 I can click any of the items inside my category field,
• 02:25 go to Sort > More Sort Options.
• 02:28 Notice that it is in manual mode so we drag and drop our items to rearrange them.
• 02:32 I can just change this back to A to Z and say OK, and now it's gone
• 02:37 back into automatic mode, so we'll to automatic sorting as you would expect.
• 02:42 Now, one more thing I want to show you is that this also works on our Columns.
• 02:47 So if I right-click and say hey, I'd like to see this one sorted with the largest
• 02:51 week first and the smallest week second, we can do that and
• 02:54 we can now go 54231 instead of the other way around.
• 02:57 So this is how sorting works in a pivot table, both for automatic A to Z, or
• 03:01 Z to A, for ascending or descending order.
• 03:04 As well as how to set up a manual sort when you
• 03:08 need things showing in a specific method.

Lesson notes are only available for subscribers.