Google Sheets includes three functions that bring advanced level sorting capabilities to this cloud-based Spreadsheet application. To sort data in Google Sheets, you can use the functions SORT, SORTN, or QUERY.

Other than these three, there is the SORT menu option. This post details how to sort data in Google Sheets using functions.

I have included different sort formulas and sort orders in this tutorial.

Sort Orders Ascending and Descending – What’s that?

In Google Spreadsheets, you can sort a table, selected cells, range, columns, or array in ascending (A-Z) or descending (Z-A) order using any of the above functions.

Ascending Order: Lowest values at the top of the column.

Descending Order: Highest values at the top of the column.

Is There Any Function that Does Quite Opposite to the Sorting?

I don’t think there is one exactly. But there is a function that might justify this. Yes! I am talking about randomizing the selected range with RANDBETWEEN.

Similar to SORT, there is a menu option to randomize range in Google Sheets.

Similar: How to Shuffle Rows in Google Sheets Without Plugin.

Here are a few examples of how to sort data in Google Sheets using functions. You can see below several formulas based on different functions. Also, a variety of sort types are included.

Examples to Sort Data in Google Sheets

I am starting this example section with three basic sort formulas.

In my demo data below, the first column contains the first name of few persons and the second column their last name.

I am using the above said three functions to sort their last name in Column B in ascending order.

Formula 1(SORT):

=SORT(A2:B11,2,1)

Here #2 indicates column 2 (column B) and #1 indicates ascending order. To sort descending order change 1 to 0.

To sort the first name (column 2) in ascending order change #2 to #1.

Formula 2 (SORTN):

=SORTN(A2:B11,9^9,0,2,1)

In SORTN, to sort data similar to SORT, you can always keep the 9^9 and the 0 the same. Because the 9^9 indicates n number of rows and 0 is a tie mode. Both these are constant in SORTN when you use it as an alternative to SORT.

Here also #2 indicates the column to sort and #1 the sort order.

You can change these two numbers to change the sort column and sort order.

Formula 3 (QUERY):

This example shows how to use Query to sort data in Google Sheets.

=QUERY(A2:B11,"Select * order by B Asc")

This is simple to read, right? Just change the “Asc” to “Desc” to sort column B in descending order.

The B is the column indicator. If you want to sort the column that contains the first name, change column identifier B to A.

All the above three formulas will sort the data in ascending order (or if you choose sort order 0, then in descending order), and here is the result.

Sorting is pretty simple. Now let me share with you some more tips related to the sorting of data in Google Sheets.

Sort Selected Cells in Google Sheets in Asc or Desc Order

Sometimes you may want to sort the columns differently. For some reason, I want to sort only the selected cells.

This time I am sorting the first name (column A) in ascending order but selected cells only.

See the result. I have excluded rows 6-8 in the sorting. So in the output, other rows are sorted in ascending order.

In the above example, I have used the following SORT formula.

=sort({A2:B5;A9:B11},1,1)

See the SORTN formula that sorts the selected cells.

=sortn({A2:B5;A9:B11},9^9,0,1,1)

Here is the Query alternative to SORT/SORTN.

=Query({A2:B5;A9:B11},"Select * order by Col1 Asc")

This Query formula has one difference from the formula that I have shared earlier. There I have used theorder by B clause to sort column B.

But here instead of usingorder by A, I have usedorder by Col1. This is because our sort range is not a single array.

Query Tip: If you use a table that formed by Curly Brackets or any other formula as the data in Query, you should use numbers as the column identifiers instead of the letter.

Sort Data Based on a Column Outside the Sort Range

Actually, our original topic is about the different functions to sort data. But I am also providing you with different ways to sort data in Google Sheets.

You can Sort a data range based on a column that outside the range.

Here I wish to get the name of persons on the top who are joined recently.

In this example, I want to sort the first name and last name (Column A and Column B) based on column D which is outside the sort range and contains the joining date.

Ultimately, I want the name of the person “Drew Cortez” on the top who has joined recently. Hence, here we should sort the data in columns A and B in descending order based on Column D.

The below formulas sort the data in descending order based on a provided column, i.e. column D.

=SORT(A2:B11,D2:D11,0)

The SORTN follows the same pattern.

=SORTN(A2:B11,9^9,0,D2:D11,0)

In both the formulas, unlike earlier examples, I have used a range (D2:D11) instead of the column number to sort.

Curious to know how the Query works here, right? See that now!

=QUERY({A2:B11,D2:D11},"Select Col1,Col2 order by Col3 Desc")

Compare this Query formula with the SORT and SORTN. This will ultimately help you to learn the use of Query in sorting data.

Custom or Personalised Sort Order in Google Doc Sheets

This is an interesting topic and I have actually detailed the same earlier. But in that, I have only included the SORT formula.

Must Read: Sort by Custom Order in Google Sheets [How to Guide].

I believe this is the time to reinvent the custom or personalized sort order with SORTN and QUERY.

I think a screenshot is a must here. Otherwise, you are not going to reach anywhere!

The sample data in the range A1:D11 shows the task name, project start, and finish dates. Also, there is one column that shows the status of the project.

You may have familiar with this type of data in a timeline or Gantt chart. But that is not relevant here. We are only discussing how to sort this data in personalized sort order.

Here I want to sort the data in the range A2:D11 in a custom order. I think it’s better to call this type of sorting personalized sorting.

I will explain it. In the above data, column D contains the status of each task. I want to segregate the tasks in the order “Complete”, “In Progress” and “Pending”.

There are two different methods.

1. Personalized Sorting in Google Sheets Using Helper Column

Here you need to use an additional column (helper column) to get the personalized sorting. Then apply this formula that uses the SWITCH function in cell E2.

=SWITCH(D2:D11,"Complete",1,"In Progress",2,"Pending",3)

Now you can sort the range A2:E11 as below.

=sort(A2:E11,5,1)

Now see the SORTN version.

=sortn(A2:E11,9^9,0,5,1)

Here is the Query.

=Query(A2:E11,"Select A,B,C,D order by E Asc")

The below Query is equal to the above one. The difference is in the use of column identifiers.

=Query({A2:E11},"Select Col1,Col2,Col3,Col4 order by Col5 Asc")

2. Personalized Sorting in Google Sheets Without Using Helper Column

We are going to include the SWITCH formula within the sort formulas. So that we can skip column E (helper).

It’s very simple. The original data range is A2:D11. We are just sorting this range with an outside column and that outside column is the SWITCH formula.

SORT

=sort(A2:D11,SWITCH(D2:D11,"Complete",1,"In Progress",2,"Pending",3),1)

SORTN

=sortn(A2:D11,9^9,0,SWITCH(D2:D11,"Complete",1,"In Progress",2,"Pending",3),1)

QUERY

=Query({A2:D11,SWITCH(D2:D11,"Complete",1,"In Progress",2,"Pending",3)},"Select Col1,Col2,Col3,Col4 order by Col5 Asc")

Sort Formula to Get the Bottom Values on the Top (Flip Data)

This is simple if you use the formula in a limited array. You can use the ROW function as the external sort column to sort the data.

Here again, I am bringing the first name and last name to sort data in Google Sheets.

=sort(A2:B5,row(A2:A5),0)

Logic: Here the formula sorts the range A2:B5 using the external row formula. The row formula returns the numbers 2 to 5.

A descending sort of this column brings the values in the bottom to the top.

When your data to sort has an unlimited number of rows, you should use the formula as follows.

=sort(A2:B,if(len(A2:A),row(A2:A),),0)

I brought the LEN function additionally to control the row function output up to the last row containing values.

I am not providing the SORTN and Query alternative here. You can find the solution easily if you have followed this tutorial carefully.

That’s all about different functions to dort data in Google Sheets. Enjoy!