SORT Function for Multiple Columns in Google Sheets

Today, we will look at how we can use the SORT function to sort for multiple columns in Google Sheets. We will look at different use cases that are common for any regular user of Google Sheets.

Let’s get started.


SORT Function Basics

Let’s start by understanding the fundamentals of the SORT function.

SORT function syntax:

SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])

sort function syntax for multiple columns in google sheets

Function Breakdown:

  • range: The range of data that is to be sorted and extracted.
  • sort_column: The index of the column within our range selection by whose values our data will be sorted. You can also use the range of the column. You must use the range of the column if the sorting data is outside of the selected range.
  • is_ascending: The sorting condition of the data. Takes TRUE/1 for ascending order or FALSE/0 for descending order.
  • [sort_column2, is_ascending2, …]: This is Optional. You can add more column conditions to the sort in order of priority.

Simple Multiple Column Sorting Example With SORT Function

In this article, we will focus solely on how we can sort by multiple columns using the SORT function in Google Sheets. And to that end, we have created the following dataset to represent different types of values that we can use to sort.

dataset for sort function for multiple columns in google sheets

As for our example, let’s say we want to sort our entries first by their Departments and then by each employee’s Starting Dates.

So, our sort conditions will be:

  • Range: B3:E12
  • For Department:
    • sort_column: 3
    • is_ascending: TRUE
  • For Start Date:
    • sort_column: 4
    • is_ascending: TRUE

Our final formula:

=SORT(B3:E12,3,TRUE,4,TRUE)

using sort function for multiple columns in google sheets

That concludes the basic way in which we can use the SORT function to sort multiple columns in Google Sheets. But for more ways to sort by multiple columns, please visit our How to Sort by Multiple Columns in Google Sheets article.

With the fundamentals out of the way, let’s see some more examples of using this function in different scenarios.


More Examples: Using SORT Function for Multiple Columns in Google Sheets

1. Column References

The SORT function allows us to also use the column range instead of the index for the sort_column field of the formula.

Now, modifying the basic SORT formula with column range references we get:

=SORT(B3:E12,D3:D12,TRUE,E3:E12,TRUE)

using column range instead of column index in the sort function

One important thing to be careful about in this format is the range length. If the column range for one column differs from the other, you will get an error.

error because of mismatched column range lengths

So make sure that all the column ranges are of the same length for all sort_column inputs.


Similar Readings


2. From a Different Column

For this example, we have created another worksheet in our workbook called Salary. Aptly named as it contains the salaries of all the employees of the Main worksheet.

the salary worksheet

Our task is to extract all data from the Main worksheet to the Salary worksheet and sort by Department first and then by the descending order of Salary.

Not only will we be bringing in data from another worksheet, but also have sort_column conditions from both Main and Salary worksheets.

Our formula:

=SORT(Main!B3:E12,Main!D3:D12,TRUE,C3:C12,FALSE)

sort function for multiple columns in google sheets from another worksheet and column location

Here, we can see that the data we have extracted, and the first column condition (Department) is referenced from another worksheet. Whereas the second column condition (Salary) comes from the current worksheet, and also it is in descending order (is_ascending is FALSE).

3. Horizontal Sort

We may sometimes have data arranged horizontally instead of having traditional vertical columns.

horizontal arrangement of data

Using the SORT function on this arrangement of data will only end in errors.

With SORT being the traditionalist as it is, we are asked to rearrange the data within the function to make it work. And doing that is simple with the TRANSPOSE function.

We apply TRANSPOSE around the range field of the SORT function. Our formula:

=SORT(TRANSPOSE(C2:G3),1,TRUE)

using transpose function to help sort function work with horizontal tables

However, if you want to keep the horizontal format, you can enclose the entire SORT formula in another TRANSPOSE function:

=TRANSPOSE(SORT(TRANSPOSE(C2:G3),1,TRUE))

keeping the horizontal arrangement even after using the sort function


Final Words

That concludes all the ways we can use the SORT function for multiple columns in Google Sheets. We hope that all the examples we have shown come in handy in your daily spreadsheet tasks.

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 officewheel.com 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

OfficeWheel
Logo