Sorting (Session2) – Multiple column Sorting

June 8, 2019
Sorting

Sort On Multiple Columns Overview

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

Sort On Multiple Columns Example

In the example below, 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. Select the range of cells to be sorted
  2. Click on the Data tab of the ribbon.
  3. Click on the Sort icon on the Sort & Filter group.
  4. Under the Column heading in the dialog box, choose Name from the drop down list to first sort the data by the Name column
  5. The Sort On option is left set to Values – since the sort is based on the actual data in the table
  6. Under the Sort Order heading, choose Z to A from the drop down list to sort the Name data in descending order
  7. At the top of the dialog box, click on the Add Level button to add the second sort option
  8. 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
  9. Under Sort Order heading, choose Largest to Smallest from the drop down list to sort the Age data in descending order
  10. 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 Ganesh aged 25 being before the record for the second Ganesh aged 25.

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.