How to Sort Data in Excel

Use dates, headings, and more to arrange data in different orders

Business people working with charts and graphs on glass wall
Martin Barraud / Getty Images

This article covers different methods of sorting data within Excel. The information that can be found in the different sections of this article includes instructions on how to:

  1. Sort a Single Column using Sort & Filter or Hot Keys
  2. Sort Multiple Columns
  3. Sort by Dates or Times
  4. Sort by Days of the Week, Months or other Custom Lists
  5. Sort by Rows — Reordering Columns

Selecting Data to be Sorted

Before data can be sorted, Excel needs to know the exact range that is to be sorted, and usually, Excel is pretty good at selecting areas of related data, so as long as when it was entered the following conditions were met:

  1. No blank rows or columns were left within an area of related data.
  2. Blank rows and columns were left between areas of related data.

Excel will determine, fairly accurately, if the data area has field names and exclude the row from the records to be sorted. However, allowing Excel to select the range to be sorted can be risky — especially with large amounts of data that are hard to check.

To ensure that the correct data is selected, highlight the range before starting the sort. If the same range is to be sorted repeatedly, the best approach is to give it a Name.

Sort Key and Sort Order in Excel

Screenshot of Excel showing Quick Sort option

Sorting requires the use of a sort key and a sort order. The sort key is the data in the column or columns you want to sort; it is identified by the column heading or field name. In the image above, the possible sort keys are Student ID, Name, Age, Program, and Month Started In a quick sort, clicking on a single cell in the column containing the sort key is sufficient to tell Excel what the sort key is.

For text or numeric values, the two options for the sort order are ascending and descending.

When using the Sort & Filter button on the Home tab of the ribbon, the sort order options in the drop-down list will change depending upon the type of data in the selected range.

Quick Sort using Sort & Filter

In Excel, a quick sort can be carried out using the Sort & Filter button on the Home tab of the ribbon.

The steps to performing a quick sort are:

  1. Click on a cell in the column containing the sort key.
  2. Click on the Home tab of the ribbon.
  3. Click the Sort & Filter button to open the drop-down menu of sort options.
  4. Click one of the two options to sort in either in ascending or descending order.

Sort Multiple Columns of Data in Excel

Screenshot of Excel showing the sorting of multiple columns

In addition to performing a quick sort based on a single column of data, Excel's custom sort feature allows you to sort 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.

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

Sort on Multiple Columns Example

In the example above, the following steps were followed to sort the data in the range H2 to L12 on two columns of data — first by name, and then by age.

  1. Highlight the range of cells to be sorted.
  2. Click on the Home tab of the ribbon.
  3. Click on the Sort & Filter icon on the ribbon to open the drop-down list.
  4. Click on Custom Sort in the drop-down list to bring up the Sort dialog box.
  5. Under the Column heading in the dialog box, choose Name from the drop-down list to first sort the data by the Name column.
  6. The Sort On option is left set to Values — since the sort is based on the actual data in the table.
  7. Under the Sort Order heading, choose Z to A from the drop-down list to sort the Name data in descending order.
  8. At the top of the dialog box, click on the Add Level button to add the second sort option.
  9. For the second sort key, under Column heading, choose Age from the drop-down list to sort records with duplicate names by the Age column.
  10. Under Sort Order heading, choose Largest to Smallest from the drop-down list to sort the Age data in descending order.
  11. Click OK in the dialog box to close the dialog box and sort the data.

As a result of defining a second sort key, in the example above, the two records with identical values for the Name field were further sorted in descending order using the Age field, resulting in the record for the student Wilson, aged 21, being before the record for the second Wilson, aged 19.

The First Row: Column Headings or Data

The range of data selected for sorting in the example above included the column headings above the first row of data.

Excel detected this row contained data that was different from the data in subsequent rows so it assumed the first row to be column headings and adjusted the available options in the Sort dialog box to include them.

One criterion that Excel uses to determine whether the first row contains column headings is formatting. In the example above, the text in the first row is a different font and it is a different color from the data in the rest of the rows.

If the first row does not contain headings, Excel uses the column letter, such as Column D or Column E, as choices in the Column option of the Sort dialog box.

Excel uses such a difference in making its determination on whether the first row is a heading row, and it is pretty good at getting it right, but it is not infallible. If Excel makes a mistake, the Sort dialog box contains a checkbox — My data has headers — that can be used to override this automatic selection.

Sort Data by Date or Time in Excel

Excel screenshot showing sorting by date.

In addition to sorting text data alphabetically or numbers from largest to smallest, Excel's sort options include sorting date values. Available sort orders available for dates include:

  • Ascending order — oldest to newest;
  • Descending order — newest to oldest.

Quick Sort vs. Sort Dialog Box

Since dates and times are just formatted number data, for sorts on a single column, such as Date Borrowed in our example, the quicksort method can be used successfully. For sorts involving multiple columns of dates or times, the Sort dialog box needs to be used — just as when sorting multiple columns of number or text data.

Sort by Date Example

To perform a quick sort by date in ascending order — oldest to newest — for the example in the image above, the steps would be:

  1. Highlight the range of cells to be sorted.
  2. Click on the Home tab of the ribbon.
  3. Click on the Sort & Filter icon on the ribbon to open the drop-down list.
  4. Click on Custom Sort in the drop-down list to bring up the Sort dialog box.
  5. Under the Column heading in the dialog box, choose Borrowed from the drop-down list to first sort the data by the Name column.
  6. The Sort On option is left set to Values — since the sort is based on the actual data in the table.
  7. Under the Sort Order heading, choose Oldest to Newest from the drop-down list to sort the dates
  8. Click OK in the dialog box to close the dialog box and sort the data.

If the results of sorting by date do not turn out as expected, the data in the column containing the sort key might contain dates or times stored as text data rather than as numbers (dates and times are just formatted number data).

Mixed Data and Quick Sorts

When using the quick sort method, if records containing text and number data are mixed together, Excel sorts the number and text data separately by placing the records with text data at the bottom of the sorted list.

Excel might also include the column headings in the sort results, interpreting them as just another row of text data rather than as the field names for the data table.

Possible Sort Warning

If the Sort dialog box is used, even for sorts on one column, Excel may display a message warning you that it has encountered data stored as text and gives you the choice to:

  • Sort anything that looks like a number as a number.
  • Sort numbers and numbers stored as text separately.

If you choose the first option, Excel will attempt to place the text data in the correct location of the sort results. Choose the second option and Excel will place the records containing text data at the bottom of the sort results — just as it does with quick sorts.

Sorting Data by Days of the Week or by Months in Excel

Screenshot of Excel showing sorting by month

You can also sort data by days of the week or by months of the year using the same built-in custom list that Excel uses to add days or months to a worksheet using the fill handle. These list allow sorting by days or months chronologically rather than in alphabetical order.

As with other sort options, sorting values by a custom list can be displayed in ascending (Sunday to Saturday/January to December) or descending order (Saturday to Sunday/December to January).

In the image above, the following steps were followed to sort the data sample in the range H2 to L12 by months of the year:

  1. Highlight the range of cells to be sorted.
  2. Click on the Home tab of the ribbon.
  3. Click on the Sort & Filter icon on the ribbon to open the drop-down list.
  4. Click on Custom Sort in the drop-down list to bring up the Sort dialog box.
  5. Under the Column heading in the dialog box, choose Month Started from the drop-down list to sort the data by the months of the year.
  6. The Sort On option is left set to Values - since the sort is based on the actual data in the table.
  7. Under the Sort Order heading, click on the down arrow next to the default A to Z option to open the drop-down menu.
  8. In the menu, choose Custom List to open the Custom Lists dialog box.
  9. In the left-hand window of the dialog box, click once on the list: January, February, March, April... to select it.
  10. Click OK to confirm the selection and return to the Sort the dialog box.
  11. The chosen list — January, February, March, April will be displayed under the Order heading.
  12.  Click OK to close the dialog box and sort the data by months of the year.

By default, custom lists are displayed only in ascending order in the Custom Lists dialog box. To sort data in descending order using a custom list after having selected the desired list so that it is displayed under the Order heading in the Sort dialog box:

  1. Click on the down arrow next to the displayed list, such as January, February, March, April..., to open the drop-down menu.
  2. In the menu, select the custom list option that is displayed in descending order, such as December, November, October, September...
  3. Click OK to close the dialog box and sort the data in descending order using the custom list.

Sort by Rows to Reorder Columns in Excel

Screenshot of Excel sorting by rows

As shown with the previous sort options, data is normally sorted using column headings or field names, and the result is the reordering of entire rows or records of data. A less known, and therefore, less used sort option in Excel is to sort by row, which has the effect of rearranging the order of columns left to right in a worksheet

One reason for sorting by row is to match the column order between different tables of data. With the columns in the same left to right order, it is easier to compare records or to copy and move data between the tables.

Customizing the Column Order

Very seldom, however, is getting the columns in the correct order a straightforward task due to the limitations of the ascending and descending sort order options for values. Usually, it is necessary to use a custom sort order, and Excel includes options for sorting by cell or font color or by conditional formatting icons.

The easiest way of telling Excel the order of columns is to add a row above or below the data table containing the numbers 1, 2, 3, 4... that indicate the order of columns left to right. Sorting by rows then becomes a simple matter of sorting the columns smallest to largest by the row containing the numbers.

Sort by Rows Example

In the data sample used for this series on Excel sort options, the Student ID column has always been first on the left, followed by Name and then usually Age.

In this instance, as shown in the image above, the columns have been reordered so that the Program column is first on the left followed by Month Started, ​Name, etc.

The following steps were used to change the column order to that seen in the image above:

  1. Insert a blank row above the row containing the field names.
  2. In this new row, enter the following numbers left to right starting in column H: 5, 3, 4, 1, 2.
  3. Highlight the range of H2 to L13.
  4. Click on the Home tab of the ribbon.
  5. Click on the Sort & Filter icon on the ribbon to open the drop-down list.
  6. Click on Custom Sort in the drop-down list to bring up the Sort dialog box.
  7. At the bottom of the dialog box, click on Options.. to open the Sort Options dialog box.
  8. In the Orientation section of this second dialog box, click on Sort left to right to sort the order of columns left to right in the worksheet.
  9. Click OK to close this dialog box.
  10. With the change in Orientation, the Column heading in the Sort dialog box changes to Row.
  11. Under the Row heading, choose to sort by Row 2 — the row containing the custom numbers.
  12. The Sort On option is left set to Values.
  13. Under the Sort Order heading, choose Smallest to Largest from the drop-down list to sort the numbers in row 2 in ascending order.
  14. Click OK to close the dialog box and sort the columns left to right by the numbers in row 2.
  15. The order of columns should begin with Program followed by Month Started, Name, etc.

Using Excel's Custom Sort Options to Reorder Columns

As mentioned above, while custom sorts are available in the Sort dialog box in Excel, these options are not easy to use when it comes to reordering columns in a worksheet. Options for creating a custom sort order available in the Sort dialog box are to sort the data by cell color, font color, and icon.

Unless each column has already had unique formatting applied, such as different font or cell colors, that formatting needs to be added to individual cells in the same row for each column to be reordered.

For example, to use font color to reorder the columns in the image above:

  1. Click on each field name and change the font color for each, such as to red, green, blue, etc.
  2. In the Sort dialog box, set the Sort on option to Font Color.
  3. Under Order, manually set the order of field names colors to match the desired column order.
  4. After sorting, reset the font color for each field name.