In an earlier blog post we talked about how we can sort our data in Excel and Google Sheets by alphabetical or numerical order.  This was assuming you only wanted to sort by 1 column (e.g. sort a table of personnel records by Name in alphabetical order).

But there are times when you will want to sort by 2 or more columns at once.  The technique is largely the same in Excel as it is in Google Sheets, although the menus are in different locations.  In this example we have a table I randomly downloaded from the Office for National Statistics, as we can see there are a number of ways we might want to sort this data:

Inline image 3

So let’s say for example we want this first sorted by “NUTS level” (whatever that means…) and then by Index from low to high.  First we select the table:  in Excel we click the “Sort & Filter” button in the Home tab and choose “Custom Sort”, or in Google Sheets go into the Data menu and select “Sort Range…”

Because our data table has “Headers” (column headings / titles) then we select “My data has headers” or “Data has header row”, otherwise you will need to use the column letters.  After selecting “NUTS Level” we must click “Add level” in Excel, or “Add another sort column” in Google Sheets.

Here is the completed dialogue box in Excel:

Inline image 2

And here is the exact same thing being done in Google Sheets:

Inline image 4

You will notice that for “Index” that Excel gives you the option to sort “smallest to largest” whereas in Google Sheets it is A>Z in both cases.  You will find that Google Sheets is actually still sorting in numerical order (e.g. if we were sorting the numbers 2000 and 300, then 2000 would come first alphabetically whereas 300 would come first numerically).

Here is the sorted table:

Inline image 5

Excel has an advantage over Google Sheets because after you have sorted your data by multiple columns, if you bring up the Custom Sort option again it will remember the choices you made last time, so re-sorting is easy.  But in Google Sheets the options are cleared each time so you will need to re-enter your values.  This can be overcome by using Google Sheets’ SORT() function but that is a big enough topic for another blog post.

Back to top

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *