Hi, this is Wayne again with a topic “Sorting by Custom Lists in Excel”.
In this Excel video, I will show you how to sort by custom lists in Microsoft Excel. Let’S get started so here I am in an Excel spreadsheet. It’S an employee list, and many of you probably already know that you can sort your data based on the alphabet. You can select, for example, a cell in a particular column, and here on the Home tab in the editing group.
You can click sort and filter and I’m going to sort A to Z and that quickly, all of these names and all of the data is resorted in this case based on the first name of these people. So that’s very nice and if you would like to learn all about that kind of sorting, please watch my other videos on sorting. Of course, I could also do a sort from Z to A, but in this article, let’s focus on this option: custom sort. What, If, instead of sorting based on the alphabet at or numbers from smallest to largest or largest to smallest, What? If? Instead I want to sort based on month, if I select cell C2, for example, and go to sort and filter, I could sort A to Z. But then the data is sorted based on the alphabet.
I get April 1st then August then December. That’S not what I want. I want it to be in the order of the months of the year in a case like this just select a cell in the column that you want to sort and here on the Home, tab home ribbon in the editing group. You can click here on sort and filter and choose custom sort now it is also possible and sometimes better to go here to data and then choose sort.
So let’s do it that way. So I get this nice dialog box that pops up – and this will help me walk through the custom sort that I want to do notice that Excel has figured out that I want to sort by review month. That’S correct sort on Cell values, that’s fine and then over here on the right.
What’S the order, I don’t want the A to Z. I don’t want Z to A. I want custom list now by default.
Excel gives us four custom lists, and one of them is perfect January through December, in order I’ll just select that and click OK and I’ll click. Ok again and now all of my data in this spreadsheet has been resorted based on the review month of each of these employees. So that’s fantastic.
It works beautifully for the data that I have here. I’M going to switch now from my employee list spreadsheet to my toy inventory, spreadsheet and this time with this spreadsheet, I don’t want to sort the data based on numbers. I don’t want to sort it based on the alphabet.
Instead, I want to sort it based on the status of this inventory. Is it in stock? Is it not in stock? How could I do this if I simply do a typical sort, it’s going to sort based on the alphabet, A to Z or Z, to A I don’t want to do that. Instead, I’m going to set up my own custom list, not one of the four predefined lists, so I’m going to click here on file and I’ll just go down here to options, and I get this Excel options pop up I’ll go here to Advanced and then I’ll. Just scroll down almost all the way down to the bottom.
Here we have the general category. Now we can click edit custom lists and it gets me into that same dialog box that we looked at earlier. But here I can add my own new list, I’m just going to click here in this box, where I can list entries we’re going to start with in stock and then I’ll tap enter low stock enter and out of stock. Now I’ll click add and okay, I’ll click, OK again and I’ll, just click.
Anyone where in column D I’ll go to the data tab, data ribbon in the sort and filter group I’ll just click on sort. Now this time it’s asking me to specify what column am I going to sort by and I’ll just choose status, sort down cell values? That’S fine! Instead of A to Z, I’ll switch it to custom list and there’s in stock low stock out of stock. I select it and choose: OK, click, OK, again and now all of my data in this spreadsheet is sorted based on the stock status of the inventory. Let me show you one more trick associated with these custom lists and sorting using the custom lists here at the right.
We have a column for location. I would like to do a similar sort to what I did here in column D, but this time, instead of creating that list manually watch what I can do, I’m just going to click and drag to highlight all of the possible options and then I’ll hold Ctrl And and tap C to copy that range of data, and I’m just going to paste it over here to the side, maybe right here, Ctrl V, to paste now that I’ve pasted that in on the side and it’s selected, I can just go here to data and On the data ribbon in the data tools group there’s an option to remove duplicates. If you don’t see the words remove, duplicates just look for this symbol here and I’ll click; click, OK, click, OK, again, and now I have a list without duplicates. It’S just every location that is possible for these toys to be stored in next.
I would want to put them in the order that I would want them to be sorted into when I use my custom list to sort. So let’s say that warehouse 1 is the most important of the storage areas, I’m just going to click and drag to put it here. Maybe Warehouse 2 is the second most important and then storage room, a storage room B and then maybe the yellow room and then storage. Room C is the least important.
Now that I have them in the order that I want I’m just going to make sure that they’re still highlighted by clicking and dragging on them and then I’ll go here to file options, Advanced I’ll scroll down, there’s General edit custom lists and because I already have That range of data with unique locations in this case I already have that selected. I can just click import and notice that now I have a new custom list and the items are exactly in the order in which I arrange them. In my spreadsheet I can click. Ok, click! Ok again, and now I don’t need this list anymore.
I can just click and drag to highlight it and here on the Home, tab in the editing group. I’M just going to go here to this symbol and clear all: okay, let’s try it out. I’M going to click anywhere in column e and I could go to data sort, but let’s look at the same step but accessed here on the Home tab home ribbon when you click on sort and filter custom sort. This time, though, I don’t want to sort based on status, I want to sort based on location sort on Cell values and then I’m going to switch this to custom list, Warehouse, one, Etc, click, OK, click, OK and all of my data is resorted reordered based on The location of that toy in the inventory, so in this article we’ve looked at a couple of different ways of setting up custom lists that you can use to sort your data. This technique can be very helpful when you need to order your spreadsheet by something other than a number from low to high high to low or the alphabet, A to Z, Z day again, if you want to learn more about sorting in Microsoft Excel. Please watch my other videos on that topic, but for now I hope you found this tutorial to be helpful.
If you did please like follow And subscribe subscribe and when you do click the Bell so you’ll be notified. When I post another video, if you’d like to support my channel, you can do that by clicking the thanks button below the video or you can support me through my patreon account or by buying Channel merch and you’ll, see information about those options in the description below The video .