Back to Blog

How to Sort Data in Google Sheets | Advanced Tutorial

Here is a spreadsheet with the most popular fruits in the U.S. listed by popularity: 

What we want to do is sort this data in alphabetical order. Since it’s a simple use case, a couple of mouse clicks will do the job. However, what if you have a larger data set and more advanced sorting needs? Read this blog post to learn everything about sorting data in Google Sheets.

How to sort all data in Google Sheets by alphabet

Google Sheets provides two built-in options to sort data alphabetically: 

  • Sort sheet – sorts all data on the sheet by a specific column.
  • Sort range – sorts the data in a selected range by a specific column.

You can find both options in the Data menu. 

Let’s use them to handle our task – sorting the list of fruits from A to Z.

Sort sheet by a column in Google Sheets

To sort the entire sheet, take the following steps:

  • Select the column to sort by. To do this, select any cell of the required column.
  • Go to the Data menu and select the alphabetical order for sorting: 
    • Sort sheet by {selected-column}, A to Z 
    • Sort sheet by {selected-column}, Z to A 

Let’s sort our list of fruits from A to Z – here is what it looks like:

How to sort data in Google Sheets with a header row

If your data set has a header row, freeze it before sorting. To do this, click and drag the grey horizontal line that you can find in the top-left corner of the spreadsheet.

Note: If you skip this, the Sort sheet function will include the header row in the sorting. 

Now you can apply the Sort sheet option as it was described above.

How to sort data in Google Sheets by range

The second built-in option, Sort range, also lets you sort data alphabetically. However, it sorts the data range you select rather than the entire sheet:

  • Select the range you want to sort. For example, let’s sort the range A2:B6.
  • Go to the Data menu and select the alphabetical order for sorting the selected range: 
    • Sort range by {selected-column}, A to Z 
    • Sort range by {selected-column}, Z to A 

Alternatively, you can click Sort range and configure the sorting there:

Take a look at what the data range looks like if sorted in reverse alphabetical order, Z to A: 

How to sort data by numerical value in Google Sheets 

You can apply the mentioned sorting options to easily sort numerical values as well. Here is how it works.

How to sort data from lowest to highest in Google Sheets

  • Select the column that contains numerical values to sort by. In our case, this is column B.
  • Go to the Data menu and select the alphabetical order for sorting: 
    • Sort sheet by {selected-column}, A to Z

The data will be sorted by column B in ascending numeric order.

How to sort data from highest to lowest in Google Sheets

To change the sorting order to descending, use 

  • Sort sheet by {selected-column}, Z to A.

There you go:


Now, let’s explore the more advanced sorting cases. For this, we imported a list of products from a Shopify store into Google Sheets using Coupler.io. Coupler.io is an integration solution for importing data into Google Sheets from third-party sources such as Shopify, Airtable, Xero, CSV, and many more. We completed three steps:

  • Installed Coupler.io from the Google Workspace Marketplace
  • Obtained Shopify API credentials: API key and API password
  • Configured the Shopify importer:
    • Entered the API credentials
    • Selected the data entity to import from Shopify – Products
    • Specified the fields to import 

Here is what the Shopify importer looks like:

Read “How to Export Shopify to Google Sheets Without Coding” for details.

  • Ran the Shopify importer by Coupler.io and got the list of products in a spreadsheet.

Read more about Coupler.io and available Google Sheets integrations.

Now we have this data set to explore different sorting options in Google Sheets.


How to sort data in Google Sheets by two different columns

Now, our data set allows us to show you how you can sort data by multiple columns. 

  • Select the range and go to Data => Sort range
  • Select the primary column to sort by, then click the button to Add another sort column 

Click Sort once you’re ready to sort the data.

Note: You can sort data by multiple columns using the SORT function as well. We’ll talk about that a bit later.

How to sort data by date in Google Sheets

In a similar way to using either Sort sheet or Sort range features, you can sort your data by date. In our example, we need to:

  • Freeze the header row
  • Select any cell in the D column (the column with date values)
  • Go to Data => Sort sheet by column D, A – Z (for ascending order) or Sort sheet by column D, Z – A (for descending order)

How to sort data in Google Sheets without messing up formulas

We don’t know which formulas you have for the data set to sort, but here are the most common use cases of calculation errors when sorting.

ARRAYFORMULA calculations messed up after sorting 

Quite often we apply array formulas not in the heading section. For example, here is the array formula in the H2 cell that shows the stock status:

=arrayformula(if(len(F2:F)=0,,
     ifs(
         F2:F<=0,"out of stock",
         F2:F<=20,"enough",
         F2:F>20,"in plenty"
     )
))

If we sort the data set, let’s say alphabetically by column B, the array formula will change its location and mess up.

To avoid this, make sure to locate your array formulas in the header section, so that they won’t be ruined after sorting. Here is what it may look like in H1 cell:

={
   "Stock"; 
   arrayformula(if(len(F2:F)=0,,
      ifs(
         F2:F<=0,"out of stock",
         F2:F<=20,"enough",F2:F>20,
         "in plenty"
      )
   ))
}

Cell reference broken after sorting and messed up calculations 

Let’s say on another sheet, you have a formula with the reference to your data set. In our example, we have a simple SUM formula that totals the number of sandwiches (products which names contain “sandwich“) in stock.

=SUM(
      'Shopify Importer'!F3,
      'Shopify Importer'!F5,
      'Shopify Importer'!F7,
      'Shopify Importer'!F8,
      'Shopify Importer'!F9,
      'Shopify Importer'!F10
)

After sorting, as you understand, the calculation will mess up since the reference values will change. To avoid this, make sure to use certain criteria to reference the cells in the data set to be sorted. For this, you may benefit from such Google Sheets functions as QUERY, VLOOKUP, FILTER, and so on. 

In our case, we can go with SUMIF. Here is the formula that filters values whose names contain “sandwich” and totals their amounts. 

=sumif('Shopify Importer'!B2:B, "*sandwich*", 'Shopify Importer'!F2:F)

Regardless of how the data set is sorted, the sum remains stable compared to the use of the regular SUM formula.

The idea here is to use more advanced formulas that will be independent of sorting.

How to sort data but keep blank rows in Google Sheets

Another interesting case is with blank rows in a data set like this:

When you sort this data in Google Sheets, either in ascending or descending order, the blank rows always sink to the bottom. However, there is a way to make the blanks appear at the top of the data set when it is sorted. The QUERY Google Sheets function can do this. Here is what the formula should look like:

=query('Shopify Importer'!A1:H19, "select * order by B")
  • Shopify Importer'!A1:H19 – the data range to sort
  • order by B – the column to sort the data by (you can choose any column) 

There you go!

Since we touched upon using Google Sheets functions for sorting, let’s delve deeper into this.

Automatically sort data in Google Sheets using the SORT function

The options described above are good for sorting static data – that is, every time you add new entries to your data set, you need to get it sorted manually. If you want to sort data dynamically, you should go with the SORT function. It is the Google Sheets function to sort data by the values in one or multiple columns. SORT doesn’t affect your current data set, but instead creates a new sorted data set. Here is what the SORT syntax looks like:

=SORT(data-range, sort-column, ascending, sort-column2, ascending2, ...)
  • data-range – the data range to sort.
  • sort-column – the column to sort by (column index or a column range). sort-column can be either within or outside the data-range, but it must have the same number of rows as the data-range.
  • ascending – apply TRUE to sort in ascending order, or FALSE to sort in descending order.

Example of how to sort data dynamically in Google Sheets

Let’s sort our Shopify products by price in descending order. To do this, we created a separate sheet and applied the following formula:

={
"id", "title", "description", "date", "price", "amount";
  	sort('Shopify Importer'!A2:F, 'Shopify Importer'!E2:E, false)
 }

Now, every new row added to your data set will be automatically sorted.

How to sort data in Google Sheets into different sheets

Using the SORT function, you can sort data into different sheets. To do this, you only need to specify the data range you want to place and sort to a specific sheet, and repeat this for other sheets. In our example, we may want to split the data set into two chunks and place them into two different sheets. So, we’ll need two SORT formulas for each sheet:

Sheet No. 1:

={
   "id", "title", "description", "date", "price", "amount";
   sort('Shopify Importer'!A2:F6, 5, false)
 }
  • 'Shopify Importer'!A2:F6 – first chunk of the data set
  • 5 – the column index to sort by

Sheet No. 2:

={
   "id", "title", "description", "date", "price", "amount";
   sort('Shopify Importer'!A7:F11, 2, true)
 }
  • 'Shopify Importer'!A7:F11 – second chunk of the data set
  • 2 – the column index to sort by

In this example, we split the data set into two chunks and sorted them differently in each sheet.

Reverse sort data in Google Sheets

It’s not a big deal to reverse the rows of your sorted data set. All you need to do is to change the sorting order from ascending to descending, or vice-versa. However, what if your data set is not sorted, and you do not want it sorted, just reversed?

In this case, you can reverse it using SORT quite easily. Here is the formula syntax to use:

=SORT(data-range,ROW(data-range),FALSE)

In our example, the formula will look like this:

=SORT(A2:F11, ROW(A2:F11), FALSE)

or like this if you want to attach headers:

={
   "id", "title", "description", "date", "price", "amount";
   SORT(A2:F11, ROW(A2:F11), FALSE)
}

As an alternative, you may consider the use of the INDEX function nested with ROWS. The formula for our example will look as follows: 

=INDEX($A$2:$F$11, ROWS(A2:$F$11))

This formula will return the last row of the data range. To get the rest ones, you’ll have to drag the formula down manually. 

How to sort a data set by color in Google Sheets

Since 2020, Google Sheets users have a built-in functionality to sort data by color – both fill and text color. Let’s check out how it works in the example of the following data set.

To enable sorting by color, select any cell within the data range and click the filter button.

Now, click the filter icon next to the header for the column to sort by => Sort by color => Fill color => and select the color.

We selected yellow for our data range, and the sorting functionality now shows yellow colored rows first:

To group rows by color, you’ll need to repeat sorting for the rest of colors.

The same algorithm works when you need to sort data by text color.

To wrap up: How to sort rows in Google Sheets without mixing data

Previously, we introduced multiple options for you to sort your data without any mistakes. We do recommend that you use the SORT function in most cases, since it does not affect your existing data set when sorting. This will let you keep the raw data and the sorted data in separate sheets. 

At the same time, the built-in sorting functionality in Google Sheets will do the job easily when you need to sort data right away. You probably know other ways to sort data without messing it up. Share your use cases with us and we’ll be happy to add them to this tutorial. Good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free