🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Custom Sorting

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
198.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
Login to download
  • 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.

Basic Sorting
03m:24s
Sorting Values
05m:37s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement