Google Sheets is a powerful cloud-based spreadsheet application that offers several functions for sorting data. In this article, we will explore three key functions – SORT, SORTN, and QUERY – that will help you sort your data effectively. So, let’s dive in!
Sort Orders Ascending and Descending – What’s That?
When sorting data in Google Sheets, you have the option to sort in either ascending (A-Z) or descending (Z-A) order. Ascending order places the lowest values at the top of the column, while descending order places the highest values at the top. You can use any of the mentioned functions (SORT, SORTN, or QUERY) to achieve this.
Is There Any Function That Does Quite the Opposite to Sorting?
While there isn’t a specific function that does the opposite of sorting, you can achieve a similar result by using the RANDBETWEEN function to randomize the selected range. Additionally, Google Sheets provides a menu option to randomize ranges. This can come in handy when you want to add a bit of unpredictability to your data.
Examples to Sort Data in Google Sheets
Let’s explore some examples of how to sort data using different functions in Google Sheets. In the following examples, we’ll focus on sorting by the last name in ascending order.
Formula 1 (SORT):
=SORT(A2:B11,2,1)In this formula, 2 indicates column 2 (column B) and 1 indicates ascending order. To sort in descending order, simply change 1 to 0. If you want to sort by the first name (column 1) in ascending order, change 2 to 1.
Formula 2 (SORTN):
=SORTN(A2:B11,9^9,0,2,1)In the SORTN function, the parameters 9^9 and 0 are constant and indicate the number of rows and the tie mode, respectively. Similar to SORT, you can change the sort column and order by modifying the last two numbers.
Formula 3 (QUERY):
=QUERY(A2:B11,"Select * order by B Asc")This formula uses the QUERY function to sort the data. Simply change “Asc” to “Desc” to sort in descending order. If you want to sort by the first name, change the column identifier from B to A.
Sort Selected Cells in Google Sheets in Asc or Desc Order
Sometimes, you may only want to sort specific cells within a range. To do this, use the following formula:
=SORT({A2:B5;A9:B11},1,1)In this example, the selected cells are sorted by the first name in ascending order. You can exclude certain rows from the sorting process by adjusting the range. For instance, rows 6-8 are excluded in the sorting process, resulting in the remaining rows being sorted in ascending order.
Sort Data Based on a Column Outside the Sort Range
Did you know that you can sort a data range based on a column that is outside the range? Let’s say you want to sort the first name and last name columns (A and B) based on column D, which contains the joining date. Here’s how to achieve that:
=SORT(A2:B11,D2:D11,0)You can use the SORTN function as an alternative to SORT. Additionally, you can use the QUERY function to achieve the same result. The provided formulas will sort the data in descending order based on the specified column.
Custom or Personalized Sort Order in Google Sheets
Now let’s discuss how to create a custom or personalized sort order in Google Sheets. Here’s an example:
=SWITCH(D2:D11,"Complete",1,"In Progress",2,"Pending",3)In this formula, we use the SWITCH function to assign custom values to each status: “Complete” is assigned the value 1, “In Progress” is assigned 2, and “Pending” is assigned 3. You can then sort the range A2:E11 using the SORT or SORTN function.
Sort Formula to Get the Bottom Values on the Top (Flip Data)
To bring the bottom values to the top of your data, you can use the ROW function as the external sort column. Here’s an example:
=SORT(A2:B5,row(A2:A5),0)This formula sorts the range A2:B5 in descending order based on the external ROW formula, which returns the numbers 2 to 5. This results in the values at the bottom of the range appearing at the top.
Now that you’re equipped with these valuable tips and functions, you can easily sort your data in Google Sheets. Enjoy the simplicity and convenience that Google Sheets offers when it comes to organizing and analyzing your data!
For more helpful tips and tricks on using Google Sheets, visit Crawlan.com.
