How to Sort Data in Excel

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

Illustration of a person sorting data in a funnel in Excel

Lifewire / Adrian Mangel

When you have large amounts of data, it can be overwhelming if it's not sorted correctly in your workbook. Learn different methods to sort data in Excel to become more productive and to make your spreadsheets easier to manage.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, Excel Online, and Excel for Mac.

Select Data to be Sorted

Before data can be sorted, Excel needs to know the exact range that is to be sorted. Excel will select areas of related data as long as the data meets these conditions:

  • There are no blank rows or columns in an area of related data.
  • Blank rows and columns are between areas of related data.

Excel determines if the data area has field names and excludes the row from the records to be sorted. 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 will be sorted repeatedly, the best approach is to give it a Name.

Sort Key and Sort Order in Excel

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 and is identified by the column heading or field name. In the image below, the possible sort keys are Student ID, Name, Age, Program, and Month Started.

Screenshot of Excel showing Quick Sort option

Sort Data Quickly

To do a quick sort, select a single cell in the column containing the sort key. Then choose how you want the data sorted. Here's how:

  1. Select a cell in the column containing the sort key.
  2. Select Home.
  3. Select Sort & Filter to open the drop-down menu of sort options.
  4. Choose how you want to sort the data. Select either ascending or descending order.

When using Sort & Filter, the sort order options in the drop-down list change depending upon the type of data in the selected range. For text data, the options are Sort A to Z and Sort Z to A. For numeric data, the options are Sort Smallest to Largest and Sort Largest to Smallest.

Sort Multiple Columns of Data in Excel

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 below, the data in the range A2 to E12 is sorted on two columns of data. The data is first sorted by name and then by age.

A screenshot showing how to sort on multiple columns in Excel

To sort multiple columns of data:

  1. Highlight the range of cells to be sorted. In this example, cells A2 to E12 are selected.
  2. Select Home.
  3. Select Sort & Filter to open the drop-down list.
  4. Select Custom Sort to open the Sort dialog box.
  5. Place a check next to My data has headers.
  6. Under the Column heading, select the Sort by down arrow and choose Name from the drop-down list to first sort the data by the Name column.
  7. Under the Sort On heading, leave the setting as Cell Values. The sort is based on the actual data in the table.
  8. Under the Order heading, select the down arrow and choose Z to A to sort the Name data in descending order.
  9. Select Add Level to add a second sort option.
  10. Under the Column heading, select the Then by down arrow and choose Age to sort records with duplicate names by the Age column.
  11. Under the Order heading, choose Largest to Smallest from the drop-down list to sort the Age data in descending order.
  12. Select OK to close the dialog box and sort the data.

As a result of defining a second sort key, shown in the example below, the two records with identical values for the Name field are sorted in descending order using the Age field. This results in the record for the student Wilson J., age 21, being before the record for Wilson P., age 19.

A screenshot showing the result of a multiple column sort in Excel

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. This row contains data that is different from the data in subsequent rows. Excel determined that the first row contains the column headings and adjusted the available options in the Sort dialog box to include them.

Excel uses formatting to determine whether a row contains column headings. In the example above, the column headings are a different font 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 this difference to make a determination on whether the first row is a heading row. If Excel makes a mistake, the Sort dialog box contains a My data has headers checkbox that overrides this automatic selection.

Sort Data by Date or Time in Excel

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.
A screenshot showing data that can be sorted by date in Excel

Quick Sort vs. Sort Dialog Box

Dates and times that are formatted as number data, such as Date Borrowed in the example above, use the quick sort method to sort on a single column. For sorts involving multiple columns of dates or times, use the Sort dialog box in the same way as sorting multiple columns of number or text data.

Sort by Date Example

To perform a quick sort by date in ascending order, from oldest to newest:

  1. Highlight the range of cells to be sorted. To follow the example above, highlight cells G2 to K7.
  2. Select Home.
  3. Select Sort & Filter to open the drop-down list.
  4. Select Custom Sort to open the Sort dialog box.
  5. Under the Column heading, select the Sort by down arrow and choose Borrowed to first sort the data by the borrowed date.
  6. Under the Sort On heading, choose Cell Values. 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.
  8. Select OK in the dialog box to close the dialog box and sort the data.
A screenshot showing the result of data sorted by date

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 attempts to place the text data in the correct location of the sort results. Choose the second option and Excel places the records containing text data at the bottom of the sort results, just as it does with quick sorts.

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

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 lists allow sorting by days or months chronologically rather than in alphabetical order.

A screenshot of Excel showing sorting by month

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

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

  1. Highlight the range of cells to be sorted.
  2. Select Home.
  3. Select Sort & Filter to open the drop-down list.
  4. Select Custom Sort to open the Sort dialog box.
  5. Under the Column heading, choose Month Start from the drop-down list to sort the data by the months of the year.
  6. Under the Sort On heading, select Cell Values. The sort is based on the actual data in the table.
  7. Under the Order heading, select the down arrow next to the default A to Z option to open the drop-down menu.
  8. Choose Custom List to open the Custom Lists dialog box.
  9. In the left-hand window of the dialog box, select January, February, March, April.
  10. Select OK to confirm the selection and return to the Sort the dialog box.
  11. The chosen list (January, February, March, April) displays under the Order heading.
  12.  Select 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. Select 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 sort the data in descending order using the custom list.

Sort by Rows to Reorder Columns in Excel

As shown with the previous sort options, data is normally sorted using column headings or field names. 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.

Customize 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 numbers that indicate the order of columns from left to right. Sorting by rows then becomes a simple matter of sorting the columns smallest to largest by the row containing the numbers.

A screenshot showing how to use sort to rearrange columns in Excel

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

In this instance, as shown in the image above, numbers have been added to the columns to prepare the worksheet to reorder the columns so that the Program column is first on the left followed by Month Start, ​Name, Age, and Student ID.

Here's how to change the column order:

  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 A: 5, 3, 4, 1, 2.
  3. Highlight the range to be sorted. In this example, highlight A2 to E13.
  4. Select Home.
  5. Select Sort & Filter to open the drop-down list.
  6. Select Custom Sort to open the Sort dialog box.
  7. Select Options to open the Sort Options dialog box.
  8. In the Orientation section, select Sort left to right to sort the order of columns left to right in the worksheet.
  9. Select OK to close the Sort Options dialog box.
  10. With the change in Orientation, the Column heading in the Sort dialog box changes to Row.
  11. Select the Sort by down arrow and choose Row 2. This is the row containing the custom numbers.
  12. Under the Sort On heading, choose Cell Values.
  13. Under the Order heading, choose Smallest to Largest from the drop-down list to sort the numbers in row 2 in ascending order.
  14. Select OK to close the dialog box and sort the columns left to right by the numbers in row 2.
  15. The order of columns begins with Program followed by Month Start, Name, Age, and Student ID.
A screenshot showing the result of sorting data by columns in Excel

Use Excel's Custom Sort Options to Reorder Columns

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.

A screenshot showing to use a font color to sort data in Excel

For example, to use font color to reorder the columns:

  1. Select each field name and change the font color for each. For example, change Program to red, Month Start to green, Name to blue, Age to orange, and Student ID to purple.
  2. In the Sort dialog box, select Sort by and choose Row 2.
  3. Under the Sort on heading, choose Font Color.
  4. Under the Order heading, manually set the order of field names colors to match the desired column order.
  5. After sorting, reset the font color for each field name.