Locked lesson.
About this lesson
Creating custom sort orders for 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.
Custom Sorting.xlsx198.8 KB Custom Sorting - Completed.xlsx
198.8 KB
Quick reference
Custom Sorting
Creating custom sort orders for a PivotTable.
Where/when to use the technique
When you have data that needs to be sorted in an order other than ascending/descending.
Instructions
Creating a custom list
- Go to File > Options > Advanced > General > Edit Custom Lists
- Select NEW LIST
- To create a manual list
- Type in your values manually, one per line
- To import a list
- Click in the Import list from cells box
- Click the grid picture
- Select your list
- Click Import
- Click OK to return to Excel
- Refresh the PivotTable
Caveats
- Custom lists are user and computer specific!
- If your recipient doesn’t have the same list set up, it will un-sort upon refresh!
Ignoring custom lists
- Right click the PivotTable > PivotTable Options > Totals & Filters
- Uncheck the checkbox next to Use Custom Lists when sorting
Removing a custom list
- Go to File > Options > Advanced > General > Edit Custom Lists
- Select the list you wish to remove
- Click Delete
- Click OK to return to Excel
- Refresh the PivotTable
- 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.