Custom Sorting

Quick reference

• 00:05 Sometimes when we're working inside an organization, we have specific orders
• 00:10 that we'd like our categories or different fields sorted in that are not alphabetic.
• 00:15 And we're always going to want them sorted in that order,
• 00:18 no matter what PivotTable we create.
• 00:20 And there's a solution for dealing with that too,
• 00:22 although there's a little bit of a gotcha as well, so we'll explain all this.
• 00:26 Now if we look at this particular listing right here, I've got bottled,
• 00:30 beer can, beer cooler, ciders and draft beer.
• 00:33 And the key that we've got is the coolers and
• 00:34 ciders is not actually in the order that I want.
• 00:38 I'd like to have that show up after draft beer in every PivotTable that I create.
• 00:42 So this is the order that I'm actually looking to create my Alcohol items in.
• 00:47 Likewise, I'll have a custom order that I like the present my
• 00:50 food in whenever I create a PivotTable as well.
• 00:52 It starts with breakfast, then goes into the lunch items of burgers, sandwiches,
• 00:55 soups, and salads.
• 00:56 Then follows through to the dinner items and food modifiers and
• 00:59 non-alcohol beverages.
• 01:00 You can see that that's not even close to what we have over here.
• 01:04 So here's how we go about actually doing this inside a PivotTable,
• 01:09 actually inside every PivotTable that we ever create.
• 01:13 We go to the file menu, and what we're gonna do is we're gonna go to options,
• 01:18 and this may look a little different in Excel 2010, but the steps are the same.
• 01:22 Now we go and we click on advanced.
• 01:26 Then we're going to scroll all the way down to the bottom of the options.
• 01:30 You're looking for under general, edit custom lists.
• 01:35 When you open that up you'll get this custom list box.
• 01:38 What we wanna do is click on, NEW LIST.
• 01:43 And rather than, well we could type each of the individual entries in here,
• 01:47 Bottled Beer, enter, Canned Beer, enter.
• 01:50 But that's a lot of work, especially when I've already got my stuff in cells in
• 01:53 the order that I wanna see them.
• 01:55 So what I'm gonna do is I'm going to click inside this box that says
• 01:58 Import List From Cells.
• 02:00 And we're gonna click the little picture of the grid here and
• 02:05 I'm gonna grab this list and then hit Enter.
• 02:08 And at this point, it's got the cell reference that I'm looking for, so
• 02:12 we'll say import.
• 02:14 And that looks pretty good.
• 02:15 Bottled beer, canned beer, draft beer, coolers, ciders, liquor and wine.
• 02:18 Excellent.
• 02:19 You'll notice that when I did that it created me a new custom list over
• 02:23 here as well.
• 02:24 And notice that this is maybe why the sort orders for days of the week
• 02:29 are showing up correctly is because these are actually a custom list and
• 02:32 the applies to every PivotTable you create.
• 02:35 Let's do a new list, again.
• 02:37 And this time we'll go and
• 02:38 we'll grab the other range of data and create a second list.
• 02:42 And Enter, Import.
• 02:45 There we go, breakfast, burgers, sandwiches.
• 02:47 Now if I wanted to move these around, or
• 02:50 let's say that I had another category in here that we also had, I don't know,
• 02:55 another item that isn't in this current data set called Signature Items.
• 03:00 I could just hit Enter and I could add more items to this list as well.
• 03:04 We'll say Add.
• 03:05 And if I ever decided I also wanted to delete a list, I could come back here,
• 03:09 select it, and press Delete.
• 03:11 We'll now say OK.
• 03:15 And then we'll say OK again.
• 03:18 And at this point when I right click and refresh my PivotTable, watch the labels.
• 03:23 They all re-sort into the order that I wanted to use here.
• 03:26 Now, here's the kicker.
• 03:29 At this point, what I did is I saved this workbook and
• 03:33 I uploaded it into the GoSkills site.
• 03:36 And that's the workbook that you can download to look at.
• 03:39 And when you look at it, it will show in the original state.
• 03:42 Not this state, even though I saved it that way.
• 03:46 And the reason being is because the settings that are actually being used here
• 03:51 are specific to my user id on my computer.
• 03:55 This is not gonna go organizational wide for you.
• 03:58 If you want everybody in your organization to sort the same way you do,
• 04:02 they have to create the same custom list.
• 04:05 And when you go to the computer down the hallway,
• 04:07 if your settings aren't roaming with you,
• 04:09 you have to create the list there too because it's not in that computer.
• 04:13 Which is a little bit of a pain.
• 04:15 All right, now what if we decide that, for whatever reason, that we wanna get rid of
• 04:20 the custom list, the sorting for alcohol, not interested in using that anymore.
• 04:25 Well, I go back to file, back to options, back to advanced,
• 04:30 scroll all the way to the bottom, go to edit custom lists,
• 04:35 find the one that I had for my list entries of alcohol,
• 04:39 delete, it tells me it'll be permanently deleted, OK.
• 04:44 Say OK, OK one more time, right click, refresh and
• 04:48 now it's back sorting in a normal manner.
• 04:53 So, that;'s how we setup, modify and delete custom lists for
• 04:57 sorting the way we want in every PivotTable we create
• 05:00 because this will apply to any PivotTable you create at all on your system.

Lesson notes are only available for subscribers.