Advanced Sorting in Excel & Google Sheets

This tutorial demonstrates several advanced sorting features in Excel and Google Sheets.

 

advancedsorting intro

 

Data sorting in Excel is usually straightforward: A–Z or numerical. But Excel also has advanced sorting options available, such as multi-level, color, custom list, left to right, and case sensitive.

Sort on Multiple Levels

Usually, you can perform a quick sort on a list of data by selecting the list header and clicking on the Sort icon in the Ribbon. If, however, you have more than one column to sort by, custom sorting allows you to add multiple levels.

  1. Select the data you wish to sort and then, in the Ribbon, select Home > Editing > Sort > Custom Sort.

 

advancedsorting custom sort

 

  1. If the Sort window shows any levels that you do not wish to sort, click Delete Level button.

 

advancedsorting delete level

 

  1. To add a new level, click the Add Level button.

 

advancedsorting add level

 

  1. If your list has headers, make sure (1) My data has headers is checked.
    Then (2) select the Column you wish to Sort by, (3) what you wish to Sort On, and (4) the Order you wish to sort by.

 

advancedsorting 1level

 

  1. You can add new levels by clicking on Add Level and specifying the details as above.

 

advancedsorting addlevels

 

  1. Click OK to sort the data according to the sort levels you have specified.

 

advancedsorting sorted

 

Sort by Cell Color

  1. To sort cells by color, select Sort On > Cell Color.
  2. In the Order box, select the background color of the cells you wish to sort on and select whether they should be On Top or On Bottom.

 

advancedsorting cell color

 

  1. Click OK to sort.

The range is sorted alphabetically, with the cells that have a background color first, followed by the cells without a background color.

 

advancedsorting cell color sorted

 

Custom Sort List

  1. To sort using a custom list, in the Order drop down box, select Custom List…

 

advancedsorting customlist

 

  1. In the List entries box, type in the custom list. Click Add, then OK.

 

advancedsorting create customlist

 

  1. The custom list will then be shown in the Order drop down box.
    Click OK to sort.

 

advancedsorting customlist sort

 

Note that the data is sorted first by Range then by Year. In the Region column, it’s sorted according to the custom list.

 

advancedsorting customlist sorted

 

Sort Left to Right

If you want to rearrange columns of data in a certain order from left to right, you can do so by sorting from left to right instead of from top to bottom.

  1. Select the data you wish to sort and then, in the Ribbon, select Home > Editing > Sort > Custom Sort.
  2. In the Sort dialog box, select Options.

 

advancedsorting options

 

  1. In the Sort Options dialog box, under Orientation, select Sort left to right.

 

advancedsorting left to right

 

  1. The first option to sort by will change from Column to Row.
    Select the row that contains the headings you wish to sort on.

 

advancedsorting select row

 

  1. Click OK to sort.

Notice that the columns of data are rearranged according to alphabetical order.

 

advancedsorting left to right sorted

 

Case-Sensitive Sort

If you have data that is a combination of upper- and lower-case letters, you can sort with the case-sensitive option switched on.

 

advancedsorting case sensitive unsorted

 

  1. In Sort Options, make sure that Case sensitive is checked.

 

advancedsorting case sensitive options

 

  1. Click OK, then OK again to sort the data.

Note that text starting with a lower-case letter will always be sorted above text starting with an upper-case letter.

 

advancedsorting case sensitive sorted

 

Advanced Sorting in Google Sheets

Google Sheets provides the ability to sort by multiple levels.

  1. Select the entire table of data you wish to sort and then, in the Menu, select Data > Sort range > Advanced range sorting options.

 

advancedsorting gs advanced sort

 

  1. Check Data has header row and then select the column to sort by. Select either A→Z or Z→A to sort by.

 

advancedsorting gs sort options

 

  1. To add another level to the sorting, click Add another sort column and then once again, select the column to sort by and the sort order.

 

advancedsorting gs add option

 

  1. Click Sort.

 

advancedsorting gs final