In this post, you'll discover everything you ever wanted to know, and everything you need to know, about sorting data within Excel.
There are many myths, misconceptions and common mistakes when it comes to sorting data in Excel.
I want to walk you through all of these scenarios and make sure you don’t fall into the same traps that trip up so many people.
You’ll discover how to sort on a single column or multiple columns.
And even how to sort by value, colour, or icon.
Moving beyond the basics, you can create a custom sort sequence which means that rather than being restricted by an alphabetic or numerical sort sequence you can prioritise and list data in the order that you beed.
Discover you how handle duplicate data by sorting a list and putting all your duplicates at the top so you can then deal with them as you need to.
And to finish off you'll learn how to sort columns into order. A lot of people do this by cutting, pasting and hacking, then fixing up their mistakes afterwards. You will have a nice elegant way to get that done.
Sound good? Then let’s dive in, there's lots to cover.
1. Introducing our example table
Consider this table.
Figure 01 - Our table
This table is a contact database of tradespeople. It shows their first and last name, where they live, when the entry was logged, what car they drive, what their trade is, what their hourly rate is, and how many jobs they’ve been allocated. It’s a mixture of text, dates, and numbers. All the common types of information you probably use on a day-to-day basis.
At the moment, the data is sorted by last name.
2. How NOT to sort
Mistake #1
A common first thing that people do is to select the whole table. You don’t need to.
Selecting the entire table is a wasted step and can take some considerable time if the table is large.
Mistake #2
Even more dangerous is to select the column that you wish to sort by.
Excel will warn you by asking if you’d like to sort the table or just the column as-is, but if you don’t heed the warning and proceed, just that one column is sorted and the rest of the table stays as it was. This means data is no in the wrong place.
For example, in our table above, if you selected just the Trade column and sorted only this column, each person in the table will end up with a different trade. Don’t do it - it’s dumb!
3. How to sort the proper way
Let’s say you want to sort the table by Hourly Rate. Here’s what you do.
1. Click a single cell in the Hourly Rate column. Excel will automatically work out the table boundaries.
2. Select the Data tab, if necessary.
3. Locate the Sort and Filter group (about half way along).
4. Click the little A-Z or Z-A icons depending on whether you want the highest rates first or the lowest rates first.
Figure 02 - Little A-Z icons
That’s it.
Here's what the table now looks like. It is arranged in Hourly Rate order.
Figure 03 - Sorted by Hourly Rate
Every row stays intact, as a unit. Each person has the same trade as before, which is what you want.
Maybe you want to re-sort this list into Trade order. Let's repeat the process.
1. Click a single cell in the Trade column. Excel will automatically work out the table boundaries.
2. Select the Data tab, if necessary.
3. Locate the Sort and Filter group (about half way along).
4. Click the little A-Z or Z-A icons.
4. Multilevel sort
Often, you need to do multi-dimensional, or multi-level sort. Let’s say for example, you want to sort the list by State and then by Trade, and then for each trade, list the hourly rates into order. For this, you need to use the big Sort button.
1. Click anywhere inside the table.
2. Select the Data tab | Sort and Filter group | Big SORT button. This opens up a dedicated dialogue box with a few more options.
Figure 04 - Big Sort button
3. In the Sort By drop-dow list, choose State.
4. Click Add Level.
5. In the second Sort By drop-down list, choose Trade.
6. Click Add Level.
7. In the third Sort By drop-down list, choose Hourly Rate.
8. Leave all the other options as they are.
Figure 05 - Sorting state then trade then hourly rate
9. Click OK.
Here’s what we have.
Figure 06 - Sorted by state then trade then hourly rate
That’s a three way sort. Very easy to do.
5. Sorting by icon
You’ll notice in the last column there are coloured icons next to each number. This is created using conditional formatting.
Figure 07 - Coloured icons - before
To sort the list into a specific icon sequence (rather than value sequence).
1. Click anywhere within the table.
2. Click the big SORT button (Data tab | Sort & Filter group).
3. In the dialog, if necessary, remove any additional levels by clicking Delete Level.
4. On the single remaining level, set Sort By to ‘Jobs’.
5. Change Sort On from ‘Values’ to ‘Cell Icon’. Excel chooses one of the icons to be top position.
6. Click Copy Level so you have as many levels as you have icons (actually, the last icon will default to the bottom so you don’t need a sort level for it).
7. On the second level, change the cell icon to the next one you want in the sequence.
8. Rinse and repeat.
6. Sorting by colour (font colour or cell colour)
1. Select any cell in the table
2. Click the large SORT button (Data tab, Sort & Filter group).
3. Choose the 'Sort By' column.
4. Change 'Sort On' to Cell Colour or Font Colour.
5. Under 'Order' choose a colour and select On Top or On Bottom.
6. Click OK.
Figure 09 - Sort on cell colour7. Sorting duplicates to the top of the list
Let me take you through another example that deals with duplicate data. Often in a contact database there will be two or more entries (rows) for a particular contact because they have mistaken been entered twice or perhaps because they have moved address or got married.
Sorting duplicates requires 2 steps. First, you highlight the duplicates with a different cell colour and font colour. Then you sort the list using a colour sequence so that the duplicates rise to the top.
Once the duplicates are all in one place, you can decide what you wish to keep and what you wish to delete.
The easiest way to use the duplicate tools that Microsoft provide is to work with a single column. If you needed to search, say, full names to check for duplicates, you would first need to concatenate (join) the names.
To concatenate first name (B3) and last name (C3) there are 2 techniques:
=B3 & " " & C3
=CONCATENATE(B3, " ", C3)
Both techniques join the first name, a space, then the last name.
Here’s the finished data in our table.
Figure 10 - Full Names
In this list there are two duplicate names - Sharktooth Carter and Jimbo Wilcox. Here’s how to highlight these names.
1. Select all the full names.
2. Select the Home tab | Conditional Formatting | Highlight Cell Rules | Duplicate Values.
Figure 11 - Conditional Formatting
3. Choose a format.
4. Click OK.
Sharktooth and Jimbo are both highlighted.
Figure 12 - Sharktooth and Jimbo are highlighted
This is a small list, so in this instance the names are easy to manage, but when you have a table with thousands of rows, the duplicates could be spread far and wide.
To put the duplicates at the top:
1. Select any cell within the table.
2. Click the big SORT button (Data tab | Sort & Filter group).
3. Delete levels until there is one remaining.
4. Set ‘Sort By’ to FullName.
5. Set ‘Sort On’ to either Cell Color or Font Color.
6. Choose Red (or whatever colour you used) to Top.
7. Click OK.
All the duplicate entries are now listed at the top of the list. However, the same names may not always be grouped together. For example, they might be listed Jimbo, Sharktooth, Sharktooth, Jimbo.
Figure 14 - Duplicates are sorted to the top of the list
It’s simple enough to rectify this:
8. Click the big SORT button again.
9. Click Add Level.
10. Set ‘Sort By’ for the second level to FullName.
11. Leave ‘Sort On’ as Value.
12. Click OK.
The list is now sorting first by colour, then by value, which groups each pair of duplicate names together.
Figure 15 - Sort Fullname by cell colour then by value
Figure 16 - Duplicates are now sorted to the top of the list and grouped together
When all the duplicate entries are grouped together, it is much easier to manage. You can decide which to keep and which to discard.
If you check the log dates for the two Jimbo entries there’s one for 13 February 2016 and an older one for 12 November 2015. For Jimbo, you would delete the oldest one.
Sharktooth has one log date for July 2015 and another for October 2015. Assuming the October entry is the latest, you would then delete the older entry.
You know your data, and you’ll know what to do when you get there.
8. Sorting into a custom sequence
Sometimes it would be nice to put things into a specific order that is neither numerical or alphabetical. Perhaps you need to prioritise certain places, people, departments or class of customer.
For example, in our table, you may want to put the Plasterers first followed by the Brickies, Roofers and Plumbers. Notice how that list is not alphabetical.
To do this, you create what’s called a custom order.
1. Click anywhere inside the table.
2. Click the big SORT button (Data tab | Sort & Filter group).
3. Delete all the sort levels except one.
4. Set ‘Sort By’ to Trade.
5. Set ‘Sort On’ to Values.
6. Set ‘Order’ to Custom List.
Figure 17 - Sorting by Trade using a custom list
A new dialog is displayed.
7. On the right-hand list, type your items in the order you wish to sort them. For this example, that’s Plasterer, Brickie, Roofer, Plumber.
Figure 18 - The new custom list
8. Once you have typed them, click Add, then OK.
9. Back in the Sort dialog, click OK.
Figure 19 - Sorted by trades using custom list
9. Sorting COLUMNS into order
The final thing I want to show you in this post is how to sort your columns into order.
Most people, when asked to do this, start inserting new columns, cutting and pasting and hacking the table around until it’s right.
That’s messy.
Here is a more elegant way.
Let’s say you want to keep First Name and Last Name as the first two columns, but bring Trade to column 3 and Hourly Rate to column 4. Every other column can stay where it is.
1. Insert a blank row directly above the table (if there isn't one already). This row will be used to add a numerical sort sequence.
2. Above First Name, type ‘1’.
3. Above Last Name. type ‘2’.
4. Above Trade, type ‘3’.
5. Above Hourly Rate, type ‘4’.
6. Any column you leave unnumbered falls into its natural order.
Figure 20 - Number sort sequence on row 1
7. Select the entire table including the number row.
This is the only time you should ever do this. Remember I mentioned earlier that you should never select the whole table before sorting. However, in this instance you have to, because if you don’t, it won’t recognise that row one as part of the table, and you won’t be able to use it for sorting.
8. Click the big SORT button (Data tab | Sort & Filter group).
9. Take a quick look at the ‘Sort By’ list. It now shows numbers because it thinks the top row of the table contains the headings. We can’t use this.
Figure 21 - Sort By shows column letters
10. Click the Options button.
11. Click Sort Left to Right. This enables us to sort COLUMNS into order rather than ROWS.
Figure 22 - Choose Sort Left to Right
12. Click OK to return to the Sort dialog.
Take a look at the ‘Sort By’ list again. You’ll see it now shows Excel row numbers. What it’s asking is, within the range you selected, which of those rows contains your sort sequence?
13. Select the row number that contains the sort sequence.
Figure 23 - Set ‘Sort By’ to Row 1
14. Sort on Value, smallest to largest, as before.
15. Click OK.
The first 4 columns of the table are the ones you numbered. The remaining columns remain in the same order.
Figure 24 - The columns have been re-sorted quickly and easily
Note: The original column widths remain as they were even though they now contain new data, so you’ll need to fix the column widths accordingly. The easiest way is to click the square block between ‘A’ and ‘1’ to select the entire sheet, then double-left-click any column divider to best fit everything.
Also, if you still had the duplicates highlighted from before, the same cells are being checked even though they contain different data. It’s a bit pointless checking for duplicate trades, so either switch off the conditional formatting (Clear Rules) and/or set up the conditional formatting again on the correct cell range.
Or you could speed things up using one of these 3 options:
Figure 25 - Change the range that the conditional formatting applies to
10. Summary
I’ve covered quite a few things in this post. Let me provide a brief recap.
1. Don’t make the same mistakes many people make.
2. To sort by just one column, select a single cell and click the little A-Z or Z-A.
3. To do a multi-level sort, click in the table then click the big SORT button on the Data tab, then add and set each level as needed.
4. You can sort by value (numerically or alphabetically), by cell colour, font colour or cell icon.
5. You now have a few tools to handle duplicates.
6. You can create a brand new custom sort sequence.
7. And you can sort COLUMNS into order, not just ROWS.
That's how to sort data like the experts do.
I hope that caused a few lightbulbs to go off in your head.
10. Watch the video (over the shoulder demo)
11. What next?
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
