Sorting data in Excel

Sorting is one of the most necessary operations with data. It makes analysis much easier, letting you:

• see the largest and smallest values of numeric ranges;
• arrange text alphabetically to find the rows you need faster;
• order events by date and time to understand their sequence and what happened first.

Excel has many sorting tools. Sometimes they’re not enough, though, so I developed a few extra sorting procedures in my !SEMTools add-in. More on them at the end; first, the built-in options.

Ways to sort data in Excel

I know and use three ways to sort regular tables on a sheet (not pivot tables): the Filter, the context menu and the Sort dialog on the Data tab. Personally, I like the Filter best.

Sorting with the Filter

To sort with the filter, turn it on for the header row (Data → Filter or Ctrl+Shift+L) and use the drop-down menu of the column you need. It covers most of the basic options:

• ascending and descending;
• by fill color and font color;
• by cell icon.

The context menu

Right-click a selected range → Sort to sort it right in place, without any headers.

An interesting detail: the sort depends on which cell of the selection is active, that is, the side from which you started selecting. Excel sorts by the column of the active cell. Knowing this, you can quickly sort a multi-column range by its rightmost or leftmost column.

The Sort dialog on the Data tab

This is the most universal and flexible sorting tool in Excel. It lets you set exact parameters, use several levels and control the direction. Select the data range or any cell inside the table and go to Data → Sort. In the dialog:

• Column sets the field to sort by;
• Sort On sets the type of value: cell values, cell color, font color or conditional formatting icon;
• Order sets the direction: A to Z or Z to A for text, smallest to largest or largest to smallest for numbers, oldest to newest or newest to oldest for dates.

One of its most useful features is multi-level sorting, by several columns at once. For example, first by region, then within each region by manager, and within each manager by sales from largest to smallest. Add the second and third levels with Add Level: Excel applies them from top to bottom.

You can also sort by color and set the order of colors: first the rows with red fill (critical values), then yellow (warnings), then everything else. The same works with conditional formatting icons, like status indicators or arrows.

A setting many users don’t know about is the direction. By default, Excel sorts rows top to bottom, but in Options you can choose Sort left to right and reorder columns instead. That’s handy for time series, when you need to reorder months or quarters.

Important: if the table has a header row, make sure My data has headers is checked. Otherwise Excel may sort the headers together with the data.

Sorting with formulas in Excel 365

Excel 365 has the SORT and SORTBY functions, which return a sorted copy of the data and update it automatically when the source changes. Sort a table by the second column in descending order:

=SORT(A2:C100,2,-1)

SORTBY can sort by a calculated value that isn’t in the table at all. For example, by the number of characters in the cell, shortest first:

=SORTBY(A2:A100,LEN(A2:A100),1)

In older versions, the same requires a helper column with =LEN(A2), sorting by it and then deleting it.

Sorting with !SEMTools

In my practice I often needed to sort data in the ways below, so I added procedures for them to make life easier. A couple of clicks is always more convenient than creating helper columns and writing formulas:

• sort a range by word endings, that is, by the last letters in alphabetical order (useful for grouping words with the same suffix);
• sort by the number of characters;
• sort by the number of words.

The add-in also has simple sorting of a selected range: A to Z for text, smallest to largest for numbers and dates, and in reverse.

Summary

I hope you found what you were looking for on this page. The Filter is the fastest for everyday sorting, the Sort dialog for multi-level and color sorting, SORT and SORTBY in Excel 365 for a live sorted copy, and !SEMTools for sorting by length, word count or endings without helper columns.