Coupler.io Blog

How to Sort Data in Google Sheets | Advanced Tutorial

  • Zakhar Yung
    • 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.

    Import and sort data in Google Sheets without formulas

    If you’re loading your data from external sources into Google Sheets, you can automate this process and sort data on the go with the help of Coupler.io. 

    Coupler.io is a data automation and analytics platform that allows you to automate data import into Google Sheets (and other destinations) from multiple apps (Shopify, Airtable, Xero, etc.) and databases. Before loading your source data to the target spreadsheet, you can perform versatile transformations including sorting by one or multiple columns. This can be done easily in any of Coupler.io’s Google Sheets integrations without any formulas. Here is what it may look like in the example of Shopify to Google Sheets integration:

    To start using the tool, sign up to Coupler.io or install Coupler.io Google Sheets add-on from the Google Workspace Marketplace. 

    Now let’s see the native options you can consider to sort 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: 

    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:

    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:

    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 by value in Google Sheets

    Sorting means to order data in an increasing or decreasing manner. So, technically, you can’t sort by value. Instead, you can filter data by value. This is where the confusion between sorting and filtering by comes from.
    If you need to filter data by value, click the Create a filter button.

    Then click the hamburger menu on the column you want to filter values by and select Filter by values.

    Specify one or several values to filter by and click Ok. The data set will be filtered accordingly.

    Read our blog post to learn more about filtering in Google Sheets.

    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

    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 

    There you go:

    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. 

    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:

    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")

    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

    What does it mean to auto sort in Google Sheets?

    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 auto sort in Google Sheets, i.e. 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 newly sorted data set. Here is what the SORT syntax looks like:

    =SORT(data-range, sort-column, ascending, sort-column2, ascending2, ...)

    Example of how to sort data dynamically in Google Sheets

    Let’s auto sort our Shopify products in Google Sheets 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 auto sort data into different Google 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)
 }

    Sheet No. 2:

    ={
   "id", "title", "description", "date", "price", "amount";
   sort('Shopify Importer'!A7:F11, 2, true)
 }

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

    How to Link Data Between Multiple Google Sheets

    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 easier 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!

    Exit mobile version