What Is the Sort Key Function in a Spreadsheet?

What a Sort Key is and when to use it in Excel

Microsoft Excel logo

Microsoft

The sort key is the data in the column or columns you want to sort by. When sorting data in Excel, the sort key is identified by the column heading or field name. In a quick sort, selecting a single cell in the column containing the sort key is sufficient enough to tell Excel what the sort key is. In multi-column sorts, the sort keys are identified by selecting the column headings in the Sort dialog box.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, Excel Online, and Excel for Mac. It may also apply to Google Sheets and other spreadsheet applications.

Sorting by Rows and Sort Keys

When sorting by rows, which involves reordering the columns of data in a selected range, field names aren't used. Instead, possible sort keys are identified by row numbers, such as Row 1, Row 2, or Row 3.

Excel numbers the rows according to their location in the worksheet, not in the selected data range. For example, Row 7 may be the first row in the range selected for the sort, but it's identified as Row 7 in the Sort dialog box.

Sort Keys and Missing Field Names

Excel uses column headings or field names to identify possible sort keys. If a data range doesn't include field names, Excel uses the column letters for those columns included in the sort range, such as Column A, Column B, or Column C.

How Multiple Sort Keys Work

Excel's custom sort feature permits sorting on multiple columns by defining multiple sort keys. In multi-column sorts, the sort keys are identified by selecting the column headings in the Sort dialog box.

If there are duplicate fields of data in the column containing the first sort key (for example, if two students are named A. Wilson), a second sort key (such as Age) can be defined and the records containing the duplicate fields of data will be sorted on this second sort key.

Only records with duplicate fields for the first sort key are sorted using the second sort key. All other records, including those containing duplicate data fields in non-sort key fields, aren't affected by the second sort key.

If there are duplicate data fields under the second sort key — for example, if two students are the same age — a third sort key can be defined to resolve the situation.

As with a quick sort, the sort keys are defined by identifying the column headings or field names in the table containing the sort key.