Custom Sort Order In Numbers

While it is easy to sort by numerical or alphabetical values, there is no simple way to sort with a custom order like High, Medium and Low. But you can use simple SEARCH or MATCH formulas to do a custom sort if you need it.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Let's take a look at creating a custom sort order in Numbers. 
MacMost is brought to you thanks to a great group of more than 2000 supporters. Go to MacMost.com/Patreon. There you could read more about it. Join us and get exclusive content and course discounts.
So it is fairly easy to sort in Numbers as long as the values you want to sort with are in an order that makes sense. In other words, numeric or alphabetic order. What if it is something different? For instance, here I've got a table and it has orders and each has an order ID, a cost, and a priority. Now if I want to sort by order number I can just simply click here next to the column Header and a Context Menu gives me the ability to sort ascending or descending. So, for instance, I can sort by order number. I can also go to Organize and then Sort over here and then choose to Add a Column and say sort by Order Number. But what if I want to sort by Priority instead. I can sort ascending but notice that this gives me alphabetical order here, High, Low, Medium or descending would be the opposite. But I, of course, want to get High, Medium, and Low. How do I get Numbers to sort it that way? I want to show you two methods. Both of them require you to add an extra column for this.
So I'm going to get the Context Menu for Column B here and add a column after the priority column and I'll call this Priority Sort. What I'm going to do here is I'm going to have a number that corresponds to how things will be sorted. So, for instance, for Medium I want 2, for Low I want 3, and for High I want 1. Now I could just go and type these numbers for each one of these. Or perhaps I could have just decided to not use High, Medium, and Low to begin with and used 1, 2, and 3 for the priority values. That would have made it easy. But instead I want to have a formula in this column that will give me values where I can sort so the priority ends up being in the right order. 
The first formula I want to show you is just going to use a simple Search function. So, I'm going to type the equals key to enter the formula entry mode and I'm going to type Search. Then left parenthesis. If I search here for the Search function I can read about it here at the bottom. I can see the first parameter is the string value to find and the second is the string value to search. So, the one I want to find is this one. What I want to search is a special string that's going to be made up of all three of these words in the order I want them. So High space Medium space Low. I could use commas instead of spaces or dashes or anything I want. Then that's my formula right there. It's pretty straight forward. It's just going to find the position of this word in this string. So, for Medium it's going to find that at position 6. I'm going to select this formula here and Copy it with Command C. Double click here on the column Header and then Paste. That will paste it in all of the cells of that column excluding the Header cell. So now I can see for High it finds that at the first position, all the Mediums are found at the sixth position, and Low is found at the thirteenth position. This corresponds to the first, sixth, and thirteenth character in this string. 
So now I've got something here where I can sort by this column. I can simply click the column Header and I can say Sort Ascending and it's sorts these properly which means I now have High, Medium, and Low sorted like this. Now if I change one of these values, like this one to Medium, notice it changes the value there and now I can resort by this column and everything is put in the order correctly. 
Let's go back to how we had things done originally. If I use Organize and then Sort and then say Add Priority Sort as the sort column then you can see it sorts it like that. Now we can click in the Context Menu for Column C and hide the column. It's still there but if I were to change something, like this, I don't see the change because Column C is hidden. But if I click the Sort Now button you could see the change is now reflected here. So I can have Column C completely hidden and just use the right sidebar to sort and make changes as needed. Click the Sort Now button and it will resort everything. 
Now there are problems with this method. The first problem is that this formula here only really allows for a small number of different values. It works great if it is just high, medium, or low, or red, green, blue. Something like that. But if you want to have a lot of them then this is going to get very long and then, of course, you have to remember to change it you have to Copy and Paste it to all the cells in Column C. Also it won't work very well if you have values, for instance, like Medium dash High as like an interim value here. It's going to find all mediums as matching the sixth character whether it is medium high or medium. So that means you need pretty much unique single words to work. 
Instead let's look at another way to do it. I'm going to keep this Priority Sort column here but I'm going to delete everything in it. Instead I'm going to create a Table that holds all of the sorting values. I'm going to do that on a new sheet so it is just out of the way. So I'm going to click the Add A Sheet button here. Then with the default table I'm going to go and remove the Header and Footer row and column there. I'm just going to have it be one column wide and just 3 cells high. I'm going to put the values here. High, Medium, Low.  I'm going to call this the Sort Order Table and then I'm going to go back here to the sheet and instead of using the Search Command I'm going to use Match. If I look up Match here you'll see it's Search For and Search Where. Search For is this and where's it going to search....well I'm going to click over to Sheet 2 and then click the column here. So A. You can see it says the Sort Order Column A. Then I'm going to close the parenthesis there. Click the Accept Changes and Close Button. It goes back to Sheet 1. 
So you can now see correctly that it identifies that Medium is the second cell here. If I Copy and double click on C there and Paste in you can see it gives me 1, 2, and 3 for everything. All the High as 1. All the Mediums are 2. All the Lows are 3. So now I can click here and sort Ascending or I can go to Organize, Sort, and Add the Priority Sort Column like that. Now it sorts everything in the right order. This works really well if you need to have a lot of values here. I'm only showing three in this example but I could have thirty or three hundred values and it will still work just as well. It also works very well if their values kind of overlap, like having a High dash Medium or a Medium dash Low value here. It also allows me to very easily change the order of things. So, if I were to put Low first and High last I could see that the values of have all changed here. If I click Sort Now it resorts everything. So it would be easy if you had a long list here and you need to change the order in which everything appeared. You could very easily update this Sort Order Table and then resort this. Of course you can keep this column hidden the entire time and it will still work just the same. 
Also note that if you want to have a secondary sort element you can. So, for instance, here under Organize Sort I've got Sort By, Priority Sort Ascending but I can add the order number as a second value to sort in Sort Ascending. So now, for instance, all the Mediums have the order number as the second thing to sort by. 
So that's how you can sort the rows in a Numbers Table at any custom order you like. Hope you found this useful. Thanks for watching.

Comments: 5 Responses to “Custom Sort Order In Numbers”

Jeff Harmed

10 months ago

Nifty work. You could also use lookup and refer to another column in the reference table. If you use popups in the first table, there could be problems if popups are updated.

Rose Enney

8 months ago

I reorganized my spreadsheet and my date column disappeared. How do I get it back? Is it gone for good?

Gary Rosenzweig

Rose: First, if you still have the document open, try simply using Undo multiple times to go back to before your mistake. Second, try using File, Revert To, to go back to a previous version before your mistake.

Haeley Kyong

5 months ago

On my Numbers, 'Sort Ascending' and 'Sort Descending' is in active. What can I do? Thank you,

Haeley: Are you talking about when you click the letter at the top of the column? Is there any data in that column? There must be some data there to sort.