Google Sheets SORT Function – Usage and Examples

0
123
Google Sheets SORT Function

You must know the use of the SORT function in Google Sheets as there are trendy functions like LOOKUP that works only with sorted data in Google Doc Spreadsheets. I have included some additional tips with this Google Sheets SORT function tutorial. Think out of the way to make the most out of this function.

Purpose of SORT Function:

The one and only purpose of the SORT function is to sort the rows of a given range by the values in one or more columns. SORT is not the only function in Google Sheets with this sorting capability. Query is another function which you can use to SORT an Array or Range similar to SORT. But Query is not limited to sorting.

Before going to our example section, let me tell you one more thing. As I’ve mentioned in the above para, we can use Google Sheets SORT function to sort Rows. Then what about Columns? I mean horizontal dataset?. I’ve included that tips also in this tutorial. Here we go.

How to Use Google Sheets SORT Function to Sort Data – Vertical and Horizontal Data

Syntax:

SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, …])

Example:

Google Sheets Sort In Vertical Range

Formula 1 (in D2)

=sort(A2:B7,1,TRUE)

In this formula the range is A2:B7 and sort_column is column 1. The Boolean TRUE indicates the sorting order which is ascending. This formula sorted the array based on first column in ascending order.

Formula 2 (in G2)

=SORT(A2:B7,2,TRUE)

This is same as above but with one difference. In this Google Sheets SORT formula, I’ve sorted column 2. That means the formula would sort the array A2:B7 based on column 2. Here also, I’ve sorted the range in ascending order. You may just replace TRUE with FALSE to sort in descending order. I think it goes without saying. So I’m not detailing that part again with example.

In the above two examples, using Google Sheets SORT formula, I’ve sorted the data range based on one column. Then how to do multiple column sorting in Google Sheets? See that below.

multiple column sorting in Google Sheets

Here as per the order in the formula arguments; A2:B5 is the range, 1 indicates sort_column 1, TRUE is to sort column 1 in ascending order, 2 indicates sort_column 2 and again the TRUE is to sort column 2 in ascending order.

This is an example to multi column sort in Google sheets. Here the formula first sort column 1 in ascending order then column 2.

Additional Sorting Tips

Below you can see different use of Google Sheets SORT function.

1. How to use SORT in a horizontal range in Google Sheets?

Normally it’s not possible. But we can use Google Sheets TRANSPOSE function together with SORT. Yup! Think out of the way.

Example:

sort horizontal table in google sheets using transpose

For beginners, I’m explaining here how this formula works. We can’t use the SORT function directly in horizontal table. So we should transpose it to vertical table then apply the SORT. With another Transpose we can again change the table back to horizontal array.

2. Point of Interest

I forgot to say one thing. You can use SORT function simply as below in a range.

=SORT(A2:B5)

No need to specify the sort_column or sorting order. This formula would automatically sort all the columns in ascending order but by based on column 1, then 2 and so on.

3. How to Remove Blank Rows in SORT in Google Sheets

If there is blank row or rows in your selected range, it would be moved to last. Then how to remove blank rows in a sorted array in Google Sheets?

how to remove blank rows while sorting in google sheets

In E2, I have the following SORT formula which returns “#REF!” error. Why?

=sort(A2:A8,1,FALSE)

Actually the formula is correct. It successfully returned the sorted values in the range E2:E8 where cell E7 and E8 were blank. I entered a value in the blank cell E7, that caused the formula error. But with the following formula in C2, I have removed such blank cells in SORT.

=filter(sort(A2:A8,1,FALSE),sort(A2:A8,1,FALSE)>0)

Here I filtered out the blank rows successfully. So there is no blank rows at the end of the sorted array and there you can enter any values. I’ve used FILTER function here to eliminate the blank rows. Let me compare the above formula with Filter Syntax. So you can easily understand how this works.

FILTER(range, condition1)

Take a note of the colouring pattern. Hope you understand.

This way you can use SORT function in Google Sheets. That’s all. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here