Sorting In Excel: The Ultimate Guide
Assign a 'primary' menu

Sorting in Excel - The Ultimate Guide

Sorting - Everything you need to know

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.

Feature Download: There are a lot of sorting techniques discussed in this post so we have created  2 great takeaways for you. You can watch a free video demonstrating everything discussed below and you can download a free PDF cheat sheet. That way you have a printable reference you can always refer to!

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.

Our 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.

First - Here's 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!

1. Sorting The Proper Way - What You SHOULD Do

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. Figure 02 - Little A-Z icons

    Figure 02 - Little A-Z icons

  5. Click the little A-Z or Z-A icons depending on whether you want the highest rates first or the lowest rates first.

That’s it.

Figure 03 - Sorted by Hourly Rate

Figure 03 - Sorted by Hourly Rate

Maybe you want to re-sort this list into Trade order. Here’s what to do.

  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.

Every row stays intact, as a unit. Each person has the same trade as before, which is what you want.

2. Multi-Level 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 Data tab | Sort and Filter group | Big SORT button. This opens up a dedicated dialogue box with a few more options.
  3. Figure 04 - Big Sort button

    Figure 04 - Big Sort button

  4. In the Sort By drop-dow list, choose State.
  5. Click Add Level.
  6. In the second Sort By drop-down list, choose Trade.
  7. Click Add Level.
  8. In the third Sort By drop-down list, choose Hourly Rate.
  9. Leave all the other options as they are.
  10. Click OK.
  11. Figure 05 - Sorting state then trade then hourly rate

    Figure 05 - Sorting state then trade then hourly rate

Here’s what we have.

  1. The states are grouped.
  2. For each state the trades are grouped.
  3. Then for each trade, the hourly rates are listed in order.
  4. Figure 06 - Sorted by state then trade then hourly rate

    Figure 06 - Sorted by state then trade then hourly rate

That’s a three way sort. Very easy to do.

3. 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

Figure 07 - Coloured icons - before

You can sort the list into a specific icon sequence rather than value sequence. It’s very easy.

  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’. The red icon defaults to top position.
  6. Click Copy Level so you have as many levels as you have icons (actually, the last colour 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 in the sequence.
  8. Rinse and repeat.
  9. Figure 09 - Sorted by cell icon

    Figure 08 - Sorted by cell icon

4. 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 column to sort by.
  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. 
  7. Figure 13 - Sort on cell colour

    Figure 09 - Sort on cell colour

5. 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

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.
  3. Figure 11 - Conditional Formatting

    Figure 11 - Conditional Formatting

  4. Choose a format.
  5. Click OK.

Sharktooth and Jimbo are both highlighted.

Figure 12 - Sharktooth and Jimbo are 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.
  8. Figure 13 - Sort on cell colour

    Figure 13 - Sort on cell colour

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

Figure 14 - Duplicates are sorted to the top of the list

It’s simple enough to rectify this:

  1. Click the big SORT button again.
  2. Click Add Level.
  3. Set ‘Sort By’ for the second level to FullName.
  4. Leave ‘Sort On’ as Value.
  5. 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 15 - Sort Fullname by cell colour then by value

Figure 16 - Duplicates are now sorted to the top of the list and grouped together

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.

6. 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.
  7. Figure 17 - Sorting by Trade using a custom list

    Figure 17 - Sorting by Trade using a custom list

  8. A new dialog is displayed. On the right-hand list, type your items in the order you wish to sort them. Here, that’s Plasterer, Brickie, Roofer, Plumber.
  9. Figure 18 - The new custom list

    Figure 18 - The new custom list

  10. Once you have typed them, click Add, then OK.
  11. Back in the Sort dialog, click OK.
  12. Figure 19 - Sorted by trades using custom list

    Figure 19 - Sorted by trades using custom list

7. 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 is not one there 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.
  7. Figure 20 - Number sort sequence on row 1

    Figure 20 - Number sort sequence on row 1

  8. 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.
  9. Click the big SORT button (Data tab, Sort & Filter group).
  10. 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.
  11. Figure 21 - Sort By shows column letters

    Figure 21 - Sort By shows column letters

  12. Click the Options button.
  13. Click Sort Left to Right. This enables us to sort COLUMNS into order rather than ROWS.
  14. Figure 22 - Choose Sort Left to Right

    Figure 22 - Choose Sort Left to Right

  15. Click OK to return to the Sort dialog.
  16. 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? Select the row number that contains the sort sequence.
  17. Figure 23 - Set ‘Sort By’ to Row 1

    Figure 23 - Set ‘Sort By’ to Row 1

  18. Sort on Value, smallest to largest, as before.
  19. 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

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:

  1. Copy the current cell range and Paste Special the Formats to the new cell range. This would transfer the conditional formatting across.
  2. Use the Format Painter (does the same as point 1).
  3. Select the current cell range, go to Conditional Formatting and Manage Rules. Change the cell range that the formatting applies to.
  4. Figure 25 - Change the range that the conditional formatting applies to

    Figure 25 - Change the range that the conditional formatting applies to

Let's Wrap

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 Data tab, and 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.

So that's how to sort data like a pro.

I hope that caused a few lightbulbs to go off in your head. 

Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.

Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature
Jason Morrell blog signature
Cheat Sheet - Sorting - everything you ever wanted to know

FREE CHEAT SHEET & VIDEO

Watch the video to see it done and download a printable PDF cheat sheet of all the key steps

Office Mastery is the digital arm and official blog of Two Rivers Software Training
Copyright © 2018 Two Rivers Software Training. All Rights Reserved Privacy Terms Contact
Like us on facebook Follow us on Twitter Watch us on YouTube Connect with Jason on LinkedIn
>