By Bob Umlas, Excel MVP

In this blog, I’ll discuss sorting, likely beyond features you knew were available! We’ll start with this screenshot of a file:

It needs sorting. There are many ways to sort data.

There are 3 ways to access sorting from the Data tab:

1–

2–You can right-mouse click on a cell and select Sort:

and this leads to another flyout with more options:

3–Then there are the sort options from the Home tab, way over on the right side (and includes options for filtering, at the bottom half):

Let’s look at these different options.

The A-Z from the Data tab will sort the contiguous range of cells based on the active cell. So, if I wanted to sort the original screenshot by the first column, Country, all I need do is click in any one cell in that column and click the A-Z tool (or select the entire contiguous range of cells), and the beginning of the data would look like this:

Had I clicked the Z-A button, it’d look like this (I’ve scrolled down a bit so you could see then change in column A):

You can see that row 143 has a different country, UK, and that’s being sorted in reverse order alphabetically, after the US. Canada shows up in row 238:

(By the way, sorting is case insensitive, but there’s an option to be case sensitive which I’ll show later).

OK, here’s the first trick I’m going to assume you didn’t know: If you wanted to sort the region within the country, you could simply click one cell somewhere in column B, click the A-Z tool (on the Data tab), click in column A, click the A-Z tool, and you get this:

The trick is to click in the reverse sequence of the order you want the columns sorted. Here, we wanted column A to be the major sort, then within column A, we want column B sorted. So, we click a cell in column B, A-Z tool, a cell in column A, A-Z tool. I’ll do it again, clicking a cell in column D then C then B then A, each time clicking the A-Z tool:

Cool?

OK, the next icon in the Data tab is also the main sorting tool access:

This brings up the following dialog:

Here are the various values:

The first option is to sort by Country, Region, Company, etc. Excel reads the headers in the first row. How did it know the first row was headers? Because of that checkbox  you see at the top right, “My data has headers.” If this is unchecked, you would see this dropdown instead:

OK, so if you wanted to sort Region within Company, you would select Country as the major sort, then you click on the Add level button at the top, then select Region from the dropdown:

You could continue this process for all the columns, and if you make a mistake you could click the Delete Level button. The Copy Level does just that – copies the level which is the active one.  To see which level is “active” you can see the slight grey background behind it. In the above illustration, Region is the active level. You can use it as a starting point to then only change one item. For example, in the following dialog, the first level was copied to produce the second one, and now all that needs changing is the color in the Order dropdown. This is slightly easier than re-entering all the fields.

You can sort one level A to Z, and another Z to A, etc.

The third dropdown had an option for Custom List. Why choose a custom list to sort by? Well, if you have month names as text (as opposed to real dates formatted as text), then if I sort Jan thru Dec, I’d see this:

Alphabetical, but not very useful! So, you would do this:

Then the result is what you’d hope for:

Let’s take a closer look at the Sort On the dropdown, usually “Values”. As you may have noticed, you can also sort on Cell Color, Font Color, or Cell Icon.

Here’s the dialog for sorting cell color in column B and then font color in column D:

The Order column in the dialog shows all the various color possibilities that exist in the column:

Region:

Department:

Let’s sort the yellow region (cell color) then a red department (font color):

Or how about Yellow region followed by Brown region:

(Notice Region is there twice to accomplish this sort. Also, notice I could choose “On top” or “On Bottom”):

One more, then we’ll move on!

Same as above, but alphabetical for each as well, and then numerically, descending:

Which yields:

The sort by cell Icon is for when you used any symbols from the icon sets of conditional formatting. A quick example where I used conditional formatting of 3 icon sets in column E:

Now I can sort by the icon set:

Here’s the result after scrolling down a bit:

Okay. One more button we haven’t looked at, the Options button at the top. This brings up:

See? I told you we’d show you how to do a case-sensitive sort! There’s the checkbox at the top to do that.

Usually, you sort top to bottom, but you can sort left to right. Suppose I want to reorder these columns so that I have Company, then Region then Department. First, insert a row at the top, and enter the sort sequence you want to see!

I also need to select all the cells (pressing Ctrl/A) will do it) then bring up the sort left to right feature shown above from the Options button, and I’m presented with this dialog:

The sort by dropdown shows Row 1, Row 2, etc. I want to sort row 1 left to right (blanks sort to the bottom in a top-to-bottom sort or to the right in a left-to-right sort. Here’s the result:

Now I can delete row 1 and the columns are sorted as I wanted.

Last tip. Double spacing data. Or triple-spacing, or more!

Enter the value 1 in a parallel range in an empty column, and select the blank cell to the right as well:

Now double-click the fill handle (dark square in the bottom right corner of the selection):

Press Ctrl/c (copy), then ctrl/down arrow (takes you to last value), then down arrow (takes you to the empty cell below the last value), then ctrl/V (paste), and here’s the bottom of the data I was using:

Now you have 2 rows with a 1, two with a 2, etc. Click on one of these cells in column F and click the A-Z tool and presto!:

It’s double spaced! The blank rows all got sorted into place. Clear column F and you’re all set!

You can do this left-to-right as well. Here is a sequence of screenshots as a sample to insert 2 columns between each column (then we’re done!):

Copy thru 1 thru 5 and paste twice:

Bring up the left-to-right sort options then choose to sort on row 1:

Then delete row 1 and you’re done!!

Happy sorting!

About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest-running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).

Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box