FREE CHEAT SHEET
Watch a video demonstration of every sorting technique and download a PDF cheat sheet of all the key points
ENTER YOUR DETAILS FOR IMMEDIATE ACCESS
In this post, I want to talk through everything you ever wanted to know, and everything you need to know, about sorting data within Excel.
There’s lots of myths, misconceptions, wrong ways of doing things and common mistakes. 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. 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.
I’ll show you how handle duplicate data. 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 I’ll show you 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.
Consider this 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.
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.
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!
Let’s say you want to sort the table by Hourly Rate. Here’s what you do.
Maybe you want to re-sort this list into Trade order. Here’s what to do.
Every row stays intact, as a unit. Each person has the same trade as before, which is what you want.
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.
Here’s what we have.
That’s a three way sort. Very easy to do.
You’ll notice in the last column there are coloured icons next to each number. This is created using conditional formatting.
You can sort the list into a specific icon sequence rather than value sequence. It’s very easy.
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.
In this list there are two duplicate names - Sharktooth Carter and Jimbo Wilcox. Here’s how to highlight these names.
Sharktooth and Jimbo are both 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:
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.
It’s simple enough to rectify this:
The list is now sorting first by colour, then by value, which groups each pair of duplicate names 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.
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.
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.
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.
The first 4 columns of the table are the ones you numbered. The remaining columns remain in the same order.
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:
I’ve covered quite a few things in this post. Let me provide a brief recap.
So that's how to sort data like a pro.
I hope that caused a few lightbulbs to go off in your head.
Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.
Cheers. Here's to your learning and success. Enjoy the rest of your day.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.