Mehrab Imtiaz
In this article, we will look at all the ways in which we can sort by value in Google Sheets. Let’s get started.
The 2 Types of Values that can be Sorted in Google Sheets
1. Sort By Numerical Value in Google Sheets
Sorting by numerical values is the most fundamental procedure that you can perform in Google Sheets. To show that, we have created the following dataset.
The simplest way to sort by numerical values is to use the Sort Range option of Google Sheets. Simply select the range of cells that contain the number values, along with any adjacent columns, and navigate to the Sort Range options from the data tab.
We have two options here:
- Sort range by column B (A to Z): This is the ascending order option.
- Sort range by column B (Z to A): This is the descending order option.
We have chosen the ascending order option. Our result:
Remember, that applications like Google Sheets, always perceive values from the left side. This is proven since the default sorting option said “Sort range by column B…” which was the first column in our selection.
So, when using the default sorting option, make sure that your priority column is all located on the leftmost side.
To know more about the many other ways we can sort by numerical values, please visit our How to Sort Numerically in Google Sheets article.
2. Sort By Text Value in Google Sheets
Text values in Google Sheets are sorted in alphabetical order, with A to Z being the ascending order and Z to A being the descending order.
We will use the same worksheet that we have used in the previous section, but since we already know that the default sort option of Google Sheets always starts from the left, we have to take a different approach.
Here, we will be using the advanced Sort Range options of Google Sheets.
Our first difference from the previous method comes from the selection. This time, we will select the whole table, the column headers included.
Now, like before, navigate to Sort range from the Data tab, but this time, select the Advanced range sorting options option.
Another way to open the advanced sort range options is by right-clicking over the selection and finding the option under View more cell actions.
This should open the Advanced Sort Range Window:
The options available:
- Data has header row: Check this option if you have the column headers included in your selection (recommended)
- Sort by: This should include all the column headers included in the selection, otherwise, it will have the column numbers to select from.
- Sort order: A to Z is ascending and Z to A is descending.
Our result:
As you may have noticed, there is a button called Add another sort column in the advanced options window.
Pressing this button will allow us to add another column as a sorting condition in our worksheet. This can be quite useful when you want to sort a large table with different hierarchies of sorting conditions. And for more ways to sort by text, please see our How to Sort Alphabetically in Google Sheets article.
How Do You Sort Mixed Values in Google Sheets?
We are already well aware of how we can use the SORT function in Google Sheets. But did you know that we can use it as a base to sort any types of values together?
Let’s see what happens when we try to sort the following column of different values. We know that in Google Sheets, numbers in a cell are right-aligned and text values are left-aligned by default, and our column contains both types.
Now, if we sort this column by just the SORT function, we get this result:
All the text values are clustered at the bottom.
To overcome this issue, we take the help of the VALUE function. So, our newly modified formula is:
=SORT(VALUE(B3:B12))
The VALUE function replaces the data range field of the SORT function. The rest remains the same so you can modify your SORT function in any way.
A downside on the VALUE function is obviously that it cannot make sense of other forms of text:
Similar Reading
Alternatives
I. Using a Multiplier (*)
Simply put, we can use a multiplier to transform the text values to numbers that will help to sort. The obvious multiplier is 1. Let’s see it in action:
=SORT(B3:B12*1)
This is a direct alternative to using the VALUE function. This is also an example of typecasting.
II. Using the TEXT function
So far, we have seen examples of typecasting to sort by different types of values in Google Sheets, and each time the text values were permanently transformed into numbers.
What if we say we can avoid that and retain the format of the text values even after sorting?
Please welcome the TEXT function.
We will use the TEXT function in tandem with the SORT function. But unlike the time with the VALUE function, we will use the text function around the sort_column field of the SORT function.
Our new formula:
=SORT(B3:B12,TEXT(B3:B12,"0000.00"),1)
Points to note:
- The length of the column range for the data_range field of the SORT function must be equal to the length of the range used for the TEXT function
- This formula only works with numbers and text values inputted as numbers. While any other text value won’t show any errors, as we have seen with VALUE, it is still a good idea to only use it as recommended.
- The TEXT function portion of the formula must only cover one column.
To know more about this formula and its advantages, please visit this link.
Final Words
That concludes all the ways we can sort by value in Google Sheets. We hope that the processes we’ve discussed come in handy in your day-to-day tasks.
Please feel free to leave any queries or advice you might have for us in the comments section below.
