How to Sort by Value in Google Sheets (With Examples)

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.

dataset to sort by value in google sheets

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.

navigating to sort range 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:

sort by numerical value in google sheets using sort range

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.

text values in the second column

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.

selecting the whole table

Now, like before, navigate to Sort range from the Data tab, but this time, select the Advanced range sorting options option.

navigating to advanced range sorting options from the data tab

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.

right-clicking to navigate to sort range

This should open the Advanced Sort Range Window:

the advanced sort range window with options

The options available:

  1. Data has header row: Check this option if you have the column headers included in your selection (recommended)
  2. Sort by: This should include all the column headers included in the selection, otherwise, it will have the column numbers to select from.
  3. Sort order: A to Z is ascending and Z to A is descending.

Our result:

sort by text value in google sheets using advanced sort range

As you may have noticed, there is a button called Add another sort column in the advanced options window.

add another sort column option

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.

text values in a number column

Now, if we sort this column by just the SORT function, we get this result:

text values are clustered after using the sort function

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 by different types of values in google sheets using sort and value functions

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:

the value function give error to different types of text

Similar Reading 


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:


using a multiplier to typecast and sort by value in google sheets

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:


using sort and text functions to sort by value in google sheets

Points to note:

  1. 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
  2. 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.
  3. 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.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply