Custom Sorting

Quick reference

• 00:04 In this video we're going to look at setting up customs sort orders that can
• 00:08 be applied every time we build a pivot table.
• 00:10 For example, right now I have alcohol first and food second.
• 00:13 I want to see that in the other order.
• 00:15 So food always comes before alcohol.
• 00:17 I can manually re-drag and arrange it, we know that but I'm going to have to do that
• 00:21 every single time I create a pivot table and that seems like old news.
• 00:25 So what about my alcohol items?
• 00:28 Well, right now draft beer comes in alphabetical order after Cooler/Ciders.
• 00:31 I want to show up after canned beer but before my coolers and ciders.
• 00:36 And for my food items, I want all these food items to be showing up in this order,
• 00:39 not the alphabetical order that's actually happening right now.
• 00:42 So if this is something I'm going to do regularly and I don't want to do this
• 00:46 manually all the time, how do I have an option to make that happen?
• 00:49 It turns out we have something called custom lists.
• 00:52 Now to find those, I'm going to go to the File tab, we're going to go down to
• 00:55 options, and we're going to look at the advanced Excel options.
• 00:58 We're going to scroll all the way down to the very end because that's where we find
• 01:03 this option here under General for Edit Custom Lists.
• 01:07 So, I'm going to edit my custom lists and
• 01:08 you'll notice there's a few custom lists that you're probably familiar with.
• 01:11 This is why dates actually sort in correct order.
• 01:15 Well, how would I make myself a new list?
• 01:18 Well, here's what I'm going to do.
• 01:19 Let's just wipe this out first and
• 01:22 we'll start by making myself a list that has food and alcohol.
• 01:26 Now, I have got these two items set up, I can click Add and
• 01:31 it's going to add those in order.
• 01:33 But typing all of these items is going to be a lot of work.
• 01:37 So why don't I try and actually do this?
• 01:39 We'll grab this list here,and say Import.
• 01:44 That brings all this in for us nicely.
• 01:45 We can say Add.
• 01:47 And then I'm going to grab the next list that I want as well where we could also
• 01:52 say Import which puts it all in a nice place and add those as well.
• 01:56 Naturally, if you ever wanted to delete a list you could come back here select it
• 02:01 and press the delete button.
• 02:03 At this point, I've got my three list setup.
• 02:05 I'm going to now go and say, OK.
• 02:08 And then we'll say Ok again to complete our options, and nothing's really changed.
• 02:14 No big deal.
• 02:15 Let's grab our data and we're going to go Refresh.
• 02:18 And just like this we can see food shows up first, alcohol second,
• 02:22 notice we've got bottled canned, draft beer,
• 02:24 then cooler ciders just like the list that we actually set up here.
• 02:28 And our food is also all showing in the correct order.
• 02:31 And this is the real secret for actually working with a pivot table and
• 02:35 getting our order set up correctly.
• 02:37 But it does have some drawbacks and this is a challenge as well.
• 02:41 Number one, you've got to set up the list manually for every single computer you're
• 02:45 working on because this is something that is stored in an option for
• 02:48 that person on that computer.
• 02:49 So that's one bummer.
• 02:50 You kind of have to go back and make that work in each case.
• 02:54 So if I set this up in this particular order and I refresh my pivot table and
• 02:57 I sent it out to someone else and they refresh it and
• 02:59 they don't have the same custom list,
• 03:01 unfortunately they're going to get it refreshing into the default order.
• 03:05 That's kind of a shame.
• 03:07 There's also a thing here that this always applies to the pivot tables where I've set
• 03:10 up this list now.
• 03:10 But what if I don't want it to?
• 03:12 What if I want to see something different?
• 03:14 I want it to go back to alphabetical order.
• 03:16 Well I could obviously override it but, here's the secret.
• 03:20 We're going to go into our PivotTable Options, and
• 03:22 we're going to go to Totals and Filters.
• 03:24 What we'll do is we'll uncheck the box, for Use Custom List when sorting and
• 03:29 we'll say, OK.
• 03:30 And now, you'll see that it actually ignores the custom lists altogether and
• 03:34 goes back to the default order that we actually had in place.
• 03:37 Of course for this pivot table that kind of defeats the purpose, so
• 03:40 I'm going to go back and set it in because I want your completed example file to
• 03:44 obviously have it set exactly the way that we want.
• 03:47 Although the challenge, remember,
• 03:49 you are going to need to set up these custom lists yourself.
• 03:51 And that's the big key to remember custom lists are per user.
• 03:56 So you've got to go and make them work for yourself, but once you've got them, man do
• 03:59 they make making your pivot table easier.

Lesson notes are only available for subscribers.