Everything You Need to Know About Sorting Data in Google Sheets

Google Sheets are so versatile that managing and sharing data has become easier ever since they came into the market.

Being able to see the data in a proper order is crucial. This is when the sorting comes into the picture.

You should always be able to sort the data in an appropriate way.

Knowing all the sorting techniques you have at your disposal will make life easier.

This guide is going to show you EVERYTHING about sorting your data! By the end, you will have mastered all the sorting techniques inside Google Sheets.

If you’d like to follow along, you can download the example workbook with the above link.

So, let’s get started!

Dataset

We have created a small dataset of movies with five columns and 15 rows (meaning, we have information about 15 films). This dataset is something that is going to be used for learning all the sorting techniques in Google Sheets.

It consists of five columns.

  • Title – The movie title. It consists of text data.
  • Release Date – A column of date data with the release date of each movie.
  • Genre – A column of text data with the movie genre.
  • Country – Country of origin for the film. This is a column of text data.
  • Gross Revenue – A column that shows gross earnings of the movie. This is a column of numeric data.

Now to explore all the different sorting methods inside Google Sheets.

This related post might interest you if you instead want to randomize the sort order of your dataset.

Sort From the Data Tab

You have a built-in sorting menu inside the Data tab from Google Sheet. There are two options to sort if you are doing it through the Data tab.

  1. Sort sheet – Sorts the entire sheet based on a specific column.
  2. Sort range – Sorts a particular range of data based on a particular column.

We will discuss both of these methods one by one.

Sort the Entire Sheet

Before you sort the data by sheet, you should consider freezing the header rows from your data. Otherwise, the system will add those rows inside the sorting range.

To freeze the top row, drag the gray horizontal line below up to the first row. You can find this line at the top-left corner of the sheet.

Freezing the first row will set the header row free from the selection when you apply the Sort sheet method and also keep it viewable at the top as you scroll down your sheet.

Now, to sort the sheet, select the cell A1.

  • Navigate towards the Data tab.
  • Inside the Data tab, click on the option Sort sheet by column A, A ➜ Z.

This action will sort the entire sheet by column A in ascending order (A ➜ Z means ascending). See the output as shown above.

💡 Tip: You can sort the data in descending order by selecting the Sort sheet by column A, Z ➜ A option. Try this one. I am keeping it as an exercise for you.

Advantages of Sort Sheet Method

  • The entire sheet gets sorted using this option. You don’t need to select any range.
  • Easily accessible through the Data tab menu.

Disadvantages of Sort sheet Method

  • It won’t help you sorting data based on multiple columns.
  • Takes headers into account while sorting. You have to freeze the top row to remove headers from sorting.

Sort Only a Range

Another option, as we discussed above, is of using the Sort range option to sort the data. This option requires you to select the cells specifically to sort.

Select the range you want to sort. In this example, it is B2:E16.

  1. Navigate towards the Data tab
  2. Click on the Sort range by column A, Z ➜ A to sort the content in descending order based on column A.

The output should be something like in the screenshot above.

📝 Note: If you select the column headers while using this sorting method, it will also sort them, which is not an ideal case. Therefore, we have the Sort range option inside Google Sheets.

Let us explore the Sort range option. It has various customizations available, and we will be looking at those as well.

Now, select the data range from A1:E16, as shown in the screenshot above.

This time, navigate towards the Data tab and select the Sort range option.

The Sort range customization window pops up, as shown in the screenshot above.

  1. Check the Data has a header row option to restrict the first row from being sorted.
  2. Under the Sort by drop-down, you can choose a column based on which you want to sort.
  3. Two radio buttons specify the sorting order. The A ➜ Z radio button allows you to sort in ascending order. If the Z ➜ A radio button is selected, sorting will happen in descending order.
  4. Click on the Sort button to apply the changes and sort the data.

The verdict based on the selection? We are trying to sort the data based on column A, excluding the header row, in descending order and rightly shown in the screenshot above.

Advantages of the Sort Range Method

  • This method allows you to sort only the selected range. Useful when you have multiple tables inside the sheet and need to sort only a specific range.
  • The customizations are cool, aren’t they? You can remove headers from being sorted, sort data based on any column, and sort based on more than one column.  

Disadvantages of the Sort range method

  • This solution is not dynamic. If values are added or changed, the sorting doesn’t affect those values. You have to sort the data again.

Sort From the Filter Toggles

You can also sort your data based on a specific column through the filter toggle. This option allows you to filter data based on a particular column. Besides, it also allows you to sort based on cell color or text color, which is really cool.

To use the sorting options through the filter toggle, you first apply the filter to your data.

  1. Select the entire data with headers, A1:E16.
  2. Now, navigate towards the Data tab.
  3. Click on Create a filter option to apply a filter on the data selected.

You can see an icon of three horizontal lines on each header row, with each line smaller than the previous. This logo is the filter toggle.

Sort in Ascending or Descending Order

Suppose you want to sort the data based on the Release Date column.

Just navigate towards the column and click on the filter toggle at the header row. A new filter menu will open up with a lot of options, as shown above.

At the top, you will see two options Sort A ➜ Z and Sort Z ➜ A. To sort this data in ascending order based on Release Date, just click on the Sort A ➜ Z option.

The data is now sorted based on the Release Date in ascending order. See the screenshot above.

💡 Tip: Sorting by date is required if you wanted to calculate a running total to date for the revenue.

Sort Based on Cell Fill Color

Suppose your dataset consists of colored cells, as shown in the screenshot above.

You want to sort this data based on the colored cells. This means you want all the yellow-colored cells at the top.

You can use the Sort by color option from the filter menu to get this done.

Go to any column header and click on the filter toggle. The filter menu will pop up.

  1. Navigate to the Sort by color side drill. You will see two options, out of which grayed out is the Text Color.   
  2. Go to the Fill Color side drill, which is associated with the cell colors. You can see two color options there, yellow and white.
  3. Click on the yellow option to see all the yellow cells at the top.

Your data is now sorted in a way that all yellow-colored cells are at the top.

💡 Tip: You can also sort data by color hex codes.

Sort Based on Text Color

What if you want to sort your data based on the text color? You can do that inside Google Sheets as well.

As the screenshot above shows, a few cells from your data have the text colored in red. You want to sort this data such that the cells with red text appear at the top. You can achieve this using sorting by text color.

Click on the filter toggle in any column, and the filter menu will pop up.

  1. You should click on Sort by color side drill. A new side window pops up.
  2. Click on the Text Color side drill as you have the colored text in your data that needs to be sorted based on color.
  3. Finally, you have the option to sort based on text color. You hit on the red as you want cells with red-colored text to appear on top.

Data is sorted based on the text color

The sorted output should look like the one in the screenshot above. These are few ways of sorting data through the filter toggle.

Advantages of the sorting through filter toggle

  • Click-based method. You have all the sorting options available through a click of the mouse
  • You can sort based on cell colors or text colors which makes this way very versatile. You can sort based on multiple colors as well.

Disadvantages of the sorting through filter toggle

  • If you are sorting for color cells or ranges, you need to follow the order. The color you want to appear the first should be sorted last. It makes life a bit tough as you have to decide the sorting order manually.

Using the SORT Function

Up to now, we have been using ways of sorting that are mouse-click-based.

However, there is also a function that can do the job for us.

The SORT function is a unique and versatile way of sorting data.

It can sort the data vertically and horizontally (we need to combine SORT with another function). Apart from this, it also allows us to sort the data from another sheet.

If you ask me, this is way beyond cool!

Syntax for the SORT Function

= SORT ( range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …] )
  • range is the range which you want to sort. A mandatory argument
  • sort_column specifies the column index by which you need to sort the data. The column can be from the data range or outside the data range. This argument is mandatory
  • is_ascending is a logical argument. Again a mandatory argument.
    • TRUE will sort the data in ascending order.
    • FALSE will sort the data in descending order.
  • sort_column2 – optional argument. Specifies the column index apart from the first one by which you should sort the data—needed when you want to sort by more than one column.
  • is_ascending2 – specifies the sorting order for sort_column2.

SORT with a Single Column

Let us sort the data with a single column using the SORT function.

Initiate the SORT function inside cell G2 of the sheet by typing the name itself.

= SORT ( A2:E16, 4, TRUE )

Now, inside the function, set the arguments as below in sequence:

  • Set the range option as A2:E16 as this is the range you wanted to sort.
  • You want to sort the data based on the Country column. The index of this column is 4. Specify that as a second argument.
  • You want to sort the data in ascending order. Thus set the third argument is TRUE.

You can see that through G2:K16, the original data is now sorted based on the Country column in ascending order.

SORT with Multiple Columns

Now, consider a situation where you want to sort the data based on two columns.

Maybe you wanted to check what the top-earning movies in each country are? In such cases, you need to use the SORT function with more than two column indexes as an argument.

= SORT ( A2:E16, 4, TRUE, 5, FALSE )

Initiate the SORT function and set up the arguments as below:

  • Range as A2:E16 as this is the table where original data lies.
  • We first wanted to sort the data by Country column, which is fourth in the tab. Hence set the sort_column argument as 4.
  • Set the third argument as TRUE to sort the data in ascending order.

Now, you want the countrywide highest-earning movies on top.

  • Set the sort_column2 argument as 5 since the Gross Revenue column will be the fifth.
  • To sort by Gross Revenue column in descending order, set is_ascending2 argument as FALSE.

Excellent work! Your data is now sorted in a way that you can see the country-wise highest-earning movies.

You can see the screenshot above and tell that The Lego Movie is a film with the highest revenue in Australia. Or you can convey to them that Iron Man is the highest-earning film from the USA. Well, that’s obvious, isn’t it? 😉

Sorting Horizontally with the SORT Function

Up to now, we have been sorting the data vertically.

What if I tell you that the SORT function can also sort the data horizontally?

You will indeed be surprised, but I am not. If we use SORT in combination with the TRANSPOSE, we can achieve this.

Suppose we have the Release Date horizontally arranged as shown above.

To sort this data vertically, we need to use the SORT function with the TRANSPOSE function.

= SORT ( TRANSPOSE ( N21:AB21 ), 1, TRUE )

Add the above formula.

  • The SORT function works on vertical cells only. Because of this you need to enclose the range you want to sort inside the TRANSPOSE function. It will arrange the range vertically in a single column.
  • Then, set the sort_column argument as 1. There is only 1 column as you just transposed the entire row to a single column.
  • We want to sort the data in ascending order. Thus, set the is_ascending argument to TRUE.

This will result in a single column of values. You will need to use the TRANSPOSE function again to convert the results back into a row or values.

= TRANSPOSE ( SORT ( TRANSPOSE ( N21:AB21 ), 1, TRUE ) )

Enclose the formula from the previous step inside the TRANSPOSE function, as shown above.

If you look now, the formula you wrote just sorted the data in ascending order and horizontally.

This function is so versatile. I mean, just have a look! The data is sorted in horizontal order.

Advantages of the SORT function

  • You can keep the data in its original order as this will produce a copy of the data in a new order.
  • You can sort based on two, three, and more columns and set the order for each column separately. Well, this was also possible with the Sort range option.
  • This function can be used in combination with other functions. This allows for many possibilities.
  • If used in combination with the TRANSPOSE, this function can even sort the data horizontally.
  • It is also a dynamic method, and the sorting will update if you add another row to the data.

Disadvantages of the SORT function

  • You need to write the formula in another cell to get the sorted view of the original data. It means the sorting doesn’t occur in the original data.
  • You have to keep the header row away from the range you provide in this function. If you incorrectly add the header row inside the range, the system will also sort that, which is not correct.

📝 Note: A little exercise for you. Initiate a new sheet, and then try to write the SORT function so that you get a sorted view of the data from another sheet.

Using the SORTN Function

The SORTN function sorts the data based on one or more columns and returns the top N rows based on the sorted output.

Since this is a function, it gets evaluated in another cell and produces a new range with the top number of rows.

This function can help you reduce the number of rows from your data by showing top-ranked rows. Interestingly enough, you can also use this function to remove duplicate entries from your data.

Syntax for the SORTN Function

= SORTN ( range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …] )
  • range specifies the range you want to sort. It is a mandatory argument.
  • n is an optional argument that limits the number of rows to return after the sort happens. By default, one row is produced in the output.
  • display_ties_mode – This is an optional argument that specifies how to handle the ties or duplications. It has a default value of 0.
    • 0 – If 0, then the formula returns the first n rows.
    • 1 – if 1, the output will have the first n rows plus the duplicate values for the nth row.
    • 2 – if 2, there will be first n rows in the output, and the system will remove the duplicates associated with the nth row.
    • 3 – if 2, the formula will return the first n rows and the duplicates associated with each row.
  • sort_column is the column index by which the formula should sort the data. It is an optional argument with default value as the lowest column index.
  • is_ascending – logical argument. By default, the system sorts in ascending order.
    • TRUE will be sorting data in ascending order.
    • FALSE will sort the data descending order.

Suppose you want to sort the data so that the movie with the highest revenue shows first. To do this, you have to sort the data in descending order based on the Gross Revenue column.

= SORTN ( A3:E17, 16, 0, 5, FALSE )

Initiate the SORTN function and start filling the arguments inside it.

  • The first argument will be the range. Put the data range as A3:E17.
  • The second argument will be the number of rows to return in output. Since our dataset has 16 rows excluding headers, put 16 as an argument there.
  • The third argument is for the ties. Since the entire data itself doesn’t have duplicates, you can mention any value of your choice between 0 – 3 for this argument. I have used the default value 0.
  • The following argument is for the sort_column index. Since we want to sort the data based on the Gross Revenue column, we will mention its index here, which is 5.
  • The last argument is for sorting order. Since we want to sort the data in descending order, the parameter for this argument should be FALSE.

📝 Note: If you have duplicates in your data and want to remove them, set the display_ties_mode to 2. Using this option will eliminate the duplicates from the top N view inside the output.

Advantages of the SORTN Function

  • It returns the top N rows without any fuss. Even if the data is in ascending order, it shows top N rows in ascending order.
  • If the display_ties_mode is set to its default value 0, the function works as the SORT function.
  • You can use this function to remove duplicate entries from your data.
  • This function can reduce the data size. By potentially showing only top N rows.

Disadvantages of the SORTN function

  • Every time, you have to mention the row number manually, which is a tedious job.
  • You have to eliminate the header row from the range. It doesn’t differentiate the header row and considers it as a part of data while sorting.

Sort Data Using the QUERY Function

The QUERY function is a very powerful tool in Google Sheets. It will allow you to do all manners of data transformations and summarization with one function.

Check out this complete guide to the QUERY function for full details.

Sorting data is also possible with the QUERY function where you can use the ORDER BY clause for any type of sorting you need.

The default sorting order is ascending, and you don’t need to mention it (if you want to, use the keyword ASC).

If you’re going to sort the data in descending order, you should use the keyword DESC.

Consider a situation where you want to sort the data based on Release Date in descending order.

Syntax for the QUERY Function

= QUERY ( data, query, [headers] )
  • data – is a mandatory argument that specifies the data range to sort.
  • query – is a required argument. Here, you can write the SQL-Like query within the double-quotes.
  • headers – is an optional argument associated with the data headers.
= QUERY ( A2:E17, "SELECT * ORDER BY B DESC" )

Use the above QUERY function inside the cell.

  • The first argument is the data argument that specifies the sorting range. Set it as A2:E17.
  • The ORDER BY clause allows you to sort the data. You have to mention the column alphabet. Since you want to sort the data based on Release Date in column B, mention that after the ORDER BY clause. The DESC clause allows you to sort the data in descending order.
  • Finally, the SELECT * clause gives you all the rows as an output in the view.

The QUERY function is powerful no doubt!

If you want to learn more about it, I have written a complete guide to the QUERY function which will show you everything this amazing function can do.

Advantages of the QUERY Function

  • Sorting data using the QUERY function can restrict the output if you add the LIMIT clause inside it. This method is helpful when you are dealing with a large dataset.
  • You can even sort based on the column that is not part of the final output.

Disadvantages of the QUERY Function

  • Suppose you are working on a large table that is associated with a view from another table. In that case, the sorting operation will always take a lot of time.
  • If the data table is large (the number of rows), the QUERY function will take more time to execute the sorting than standard ways because of the ORDER BY clause.

Sort Data in Pivot Table

Pivot Table is a go-to solution for all the data summarization problems.

In just a few clicks, you can have a decent summary of the large dataset.

Apart from summarizing the data, you can also sort it inside the Pivot Table.

Suppose you have movies data in the pivot table view, as shown in the screenshot above.

Sorting inside the Pivot Table is pretty simple. Click on the column you want to sort and then navigate towards the Pivot table editor. You can see that the data is sorted by default in ascending order.

Follow the steps below to sort the data inside the pivot table based on the Title in descending order.

  1. Navigate towards the Pivot table editor menu to sort the data based on the Title column.
  2. Click on the Order drop-down for the Title column.
  3. Choose the option Descending to sort the data in descending order.

The screenshot above shows the data is sorted in descending order based on the Title column.

To sort the data based on numerical values,

  • Navigate towards the Pivot table editor option and go to the Title column.
  • Inside the Title column properties, this time around, click on the Sort by drop-down.
  • Click on the SUM of Gross Revenue option to sort the data in descending order based on that column.

You can see in the screenshot above that the data is now sorted based on the SUM of Gross Revenue column in descending order.

📝 Note: You can also use the Sort range option to sort the pivot table data by multiple columns and multiple sort orders.

Advantages of Sorting through Pivot Table

  • Easy to use method. You don’t have to go to the various tabs or write a formula to sort the data.
  • You can also use the Sort range option on the pivot table to sort the data based on multiple columns and criteria.

Disadvantages of Sorting through Pivot Table

  • You can only sort the data based on the first column inside the rows. This limits the usage of sort functionality.
  • Suppose you want to sort by another column. For Ex. Genre. Then you need to change the order and take the Genre column in the first place to sort the data based on it.

Sort Data via App Script

What if I tell you that you can automatically sort the data through a script that sorts the data again if you add a new row to the data?

You will be surprised, right?

Google App Scripts allow you to automate just about anything with Google Sheets including the data sorting.

I have stored the films data inside a new tab and named it App Script Data for simplicity. We will use this data for sorting with the App Script.

Inside the Google Sheet, go to the Tools tab and click on the Script editor to open Google App Script.

The Apps Script interface opens up, as shown in the screenshot above.

I am not following the details here about the app’s script in much detail. If you want to know a bit about it, I have explain it in more detail here in this article.

function SortDataAuto(){

  let sht = SpreadsheetApp.getActiveSpreadsheet()
  let wrksht = sht.getSheetByName("App Script Data")
  let srtRng = wrksht.getRange(2, 1, wrl.getLastRow()-1, 5)

  srtRng.sort({column: 5, ascending: false})
}

Inside the App Script, copy and paste the code from above.

Explanation of the Code

  • The first line of the code defines a function named SortDataAuto(). Within the function body, we will write down a code that can sort the data.
  • The second line returns the active spreadsheet using the SpreadsheetApp.getActiveSpreadsheet() method. To store the result of this method, we are assigning it to a variable named sht.
  •  In the third line, we access the spreadsheet App Script Data using the getSheetByName() method. This method is applicable on the variable sht defined previously and is stored in a variable named wrksht.
  • In the following line, we are trying to access the range on which the function is applicable. We are using the getRange() method to get the range from the active worksheet. The parameters of this method are.
    • The first parameter defines the starting row.
    • The second establishes the starting column;
    • The third argument returns the last active row and the column numbers argument. If you try to decode this, it will read as, get the range starting from the second row, the first column, until the last row with the data and five columns. This covers the entire data range we wanted to sort. You store this result inside the shtRng column.
  • Finally, the sort() method is called on the shtRng variable. You want to sort the data based on the Gross Revenue column in descending order. Therefore, the column number is mentioned as 5 and ascending as false.

Click on the Run button from the upper ribbon in Apps Script. It will execute this code on the current Google Sheet.

The execution log above shows that the execution is successful. There is no error in this code at the execution level.

If you navigate back to the Google Sheet, you can see that the data is sorted based on the Gross Revenue column.

To automate this sorting script in Google Sheets, you have to amend the trigger inside the Apps Script.

On the left-hand side menu inside the Apps Script, you can see several options. Click on Triggers out of those to set the trigger for this code to automatically sort the data.

To add a trigger that will sort the data every time the sheet gets edited, you must click on the Add Trigger option.

Setting the trigger up for sheet edit

  1. Once you click on the Add Trigger button, you will see a new Add Trigger for SorDataAuto window popping up.
  2. Navigate towards the Select event type drop-down menu.
  3. Select the On edit option. Setting up this option allows the code to run every time the sheet is updated.
  4. Finally, hit the Save button to save the trigger.

If you add a new row to the sheet, the system will automatically sort the data. This feature is pretty cool.

As the screenshot above shows, the script automatically triggers and sorts the data based on the Gross Revenue column.

The Avengers: Endgame movie goes to the top automatically as it is the highest-earning movie out of the list.

Advantages of the App Script

  • Dynamic in nature and auto sorts the data if the user adds a new row at the end.
  • Allows you to automate the entire sorting procedure and can sort based on multiple columns with a different order for each of them.

Disadvantages of the App Script

  • You have to write the script which is developed in modern JavaScript. It might be an issue for users who are not aware of modern JavaScript, and it’s working.
  • You have to set the triggers automatically to run the script every time there is a change in data. That might be hard to understand for some users.

How to Create a Custom Sort Order

Is it possible for you to sort the data in a custom order?

What if you want Canada at the top, Australia on the second line, South Africa on the third, etc., once you sort the data?

You can absolutely achieve this result.

If you use the SORT in combination with the powerful MATCH function, you can get this done.

Since you wanted to sort the data based on country with a custom list, you should first decide the custom sort order. See a screenshot above. That is our custom sort list and we will expect the function to sort the data in this order.

This means that all movies from Canada should be sitting first in the sorted view, Australia second, etc.

= SORT ( A3:E17, MATCH(D3:D17, AK3:AK9, 0 ), TRUE )

Now, use the above formula to sort the data based on a custom order, as shown in the previous screenshot.

Explanation

  • The first argument is the sorting range. In our example, it is A3:E17.
  • The second argument decides the sort_column. Here, we use the MATCH function. This function will match the values from the original Country column with the newly chosen sorting order from column AK. Once the function finds a match, it will generate the position number. Potentially, it will be a numeric column that gives the positions of countries. Sorting happens based on these numbers.
  • Finally, the sorting order is decided based on the is_ascending argument. If you set it as TRUE (as we have), the sorting will happen in the current order. If you put it as FALSE, the sorting will occur in reverse order to the custom list.

Advantages of using a custom sort order

  • As the name itself suggest, you can sort the data in a custom order. Whatever order you want, you can use it to sort.
  • You can use multiple custom orders to sort the data. This is again cool. Didn’t like the view after the traditional sort? No Problem! Create a one of your own and sort the data based on it.

Disadvantages of Using a Custom Sort Order

  • This is a more complex formula. Some users who are not exposed to Google Sheets can find it challenging to understand.

Conclusions

Sorting is an important task when you are working with the data.

It allows you to get the data either in ascending or descending order based on a column.

You can sort the sheet based on the Sort sheet option, or if you need, you can sort based on multiple columns through the Sort range option.

You also have powerful functions such as SORT, SORTN, and QUERY which can be used in combination with other functions such as TRANSPOSE and MATCH for exciting custom sorting results.

If you need to summarize your data, pivot tables offer a variety of ways to sort the results.

Whatever your needs, there are various ways of sorting the data inside Google Sheets. Each with its own advantages and disadvantages.

Check Out These Essential Google Sheets Tutorials

About the Author

Lalit Salunkhe

Lalit Salunkhe

Lalit is a data analyst with diverse skills and experience in data mining and analysis. He has a M.Sc. in Statistics from one of the top institutes in Maharashtra. Lalit is also a Google Sheets expert and enjoys teaching others how to use Google Sheets to solve their data problems.

Subscribe

Subscribe for awesome Google Sheets videos 😃

Top Posts

Related Articles

Comments

0 Comments

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Find Out More

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!