Advanced Sorting in Excel & Google Sheets

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

 

 

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.

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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.

 

 

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

 

 

Custom Sort List

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

 

 

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

 

 

3. The custom list will then be shown in the Order drop down box.
   Click OK to 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.

 

 

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.

 

 

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

 

 

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

 

 

5. Click OK to sort.

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

 

 

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.

 

 

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

 

 

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

 

 

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.

 

 

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

 

 

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

 

 

4. Click Sort.