6 Ways to Sort Data in Excel

01
of 05

Excel Quick Sort Options

Excel quick sort
Quick Sort on One Column in Excel. © Ted French

Excel Sorting Options

This series of tips covers different methods of sorting data in Excel. Specific information can be found on the following pages:

  1. Quick Sort on a Single Column using Sort & Filter or Hot Keys
  2. Sort on 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 long as when it was entered,

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

Excel will even determine, fairly accurately, if the data area has field names and exclude this 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

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

1. 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 if necessary
  3. Click on the Sort & Filter button to open the drop down menu of sort options
  4. Click on one of the two options to sort in either in ascending or descending order
  5. Check to ensure that the data was sorted correctly

2. Sort Data Using Ribbon Hot Keys

There is no keyboard shortcut key combination for sorting data in Excel.

What is available are hot keys, which allow you to use keystrokes rather than the mouse pointer to select the same options listed above on the Home tab of the ribbon.

  • Using the keyboard to access ribbon options always starts with the Alt key.

To Sort in Ascending Order Using Hot Keys

  1. Click on a cell in the sort key column
  2. Press the following keys on the keyboard:
  3. Alt  H  S  S 
  4. The table of data should be sorted A to Z / smallest to largest by the selected column

The hot keys translate into:
"Alt" key > “Home” tab > “Editing” group > “Sort & Filter” menu >  “Sort Smallest to Largest” option.

To Sort in Descending Order Using Hot Keys

The steps to sort in descending order using hot keys are the same as those listed for an ascending sort except the hot key combination is:

Alt H  S  O

The hot keys translate into:
"Alt" key > “Home” tab > “Editing” group > “Sort & Filter” menu >  “Sort Largest to Smallest” option.

    02
    of 05

    Sort on Multiple Columns of Data in Excel

    excel-2013-sort-multiple-columns.jpg
    Sorting Data on Multiple Columns. © Ted French

    3. Sort on Multiple Columns Overview

    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 identify 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 A. Wilson aged 21 being before the record for the second A. 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 criteria 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. It is also separated from the rows below by a thick border.

    Excel uses such 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 it makes a mistake, the Sort dialog box contains a check box - My data has headers - that can be used to override this automatic selection.

    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.

    03
    of 05

    Sort Data by Date or Time in Excel

    excel-2013-sort-dates.jpg
    Sorting by Date in Excel. © Ted French

    4. Sort by Date or Time Overview

    In addition to sorting text data alphabetically or numbers from largest to smallest, Excel's sort options include sorting date values.

    The sort orders available for dates are:

    • 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 the example in the image above - the quick sort method can be used successful.

    For sorts involving multiple columns of dates or times, the Sort dialog box needs to be used - just as when sorting on 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 the Sort Oldest to Newest option in the list to sort the data in ascending order
    5. The records should be sorted with the oldest dates in the Borrowed column at the top of the table

    Dates and Times Stored as Text

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

    In the image above, the record for A. Peterson ended up at the bottom of the list, when, based on the borrowing date - November 5, 2014 - , the record should have been placed above the record for A. Wilson, which also has a borrowing date of November 5.

    The reason for the unexpected results is that the borrowing date for A. Peterson has been stored as text, rather than as a number

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

    Sort Warnings - Sort Dialog Box

    As shown in the image above, if the Sort dialog box is used, even for sorts on one column, Excel displays 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.

    04
    of 05

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

    Sort by Custom Lists in Excel
    Sort by Custom Lists in Excel. © Ted French

    5. Sort by Days or Months Overview

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

    In the example above, the data has been sorted by the month that students started their on-line program of studies.

    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

    Note: 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

    05
    of 05

    Sort by Rows to Reorder Columns in Excel

    Sort by Rows to Reorder Columns
    Sort by Rows to Reorder Columns. © Ted French

    6. Sort by Rows Overview

    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 straight forward 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.

    These options, as outlined at the bottom of this page, are still rather labor intensive and not that easy to use.

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

    Once the sort is done, the added row of numbers can easily be deleted.

    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 top 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 reorder columns in a worksheet.

    Options for creating a custom sort order available in the Sort dialog box are to sort the data by:

    And, 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