How to Sort by Multiple Columns in Google Sheets

Let’s say you work in a large company’s human resources department, planning a series of training sessions across multiple cities.

And you to manage this, you have the details of employees, such as date of birth, name, height, and city.

You need to organize this training session based on the age and location of employees to ensure that the sessions are according to age groups and easily accessible based on location.

Here you need to sort this data using two different columns, and that’s why you need to learn to sort by multiple columns in Google Sheets.

Sort By Multiple Columns in Google Sheets

• Select the Data to Sort – Select the data you want to sort, including the headers. So here, select the range A1:D31, and make sure that each column has the same type of data (e.g., all dates, numbers, or text) to avoid sorting errors.

• Go to Sort Option – Go to the Data menu in the toolbar. From the menu, choose Sort range and then click on the “Advanced range sorting option”.

• Select Header – As your selection includes your headers, check the box that says Data has a header row. This ensures that Google Sheets recognizes the first row as the column header.

• Set First Columns for Sorting – Now, start by adding the first sort criterion. To organize training sessions, you would first sort them by City. Select City from the dropdown list in the sort dialog and choose either ascending or descending order based on your preference.

• Select Second Column for Sorting – Now click on the “Add another sort columns” and add a second column to sort in the dialog box. Here, choose the Date of Birth. This secondary sorting helps further refine the grouping within each city, making it easier to create the sessions for specific age groups.

Now once you click OK to apply this multi-column sort, Google Sheets will sort the data using City, and then Date of Birth Columns.

By following these steps, you will have effectively organized your data in a way that facilitates the planning and execution of regionally and demographically targeted training sessions.

Now if you want to change the sorting columns which you have defined in the dialog box, you need to follow all the steps again and specify two new columns to sort and then, click OK to sort.

Reverting to the Original Data After Sorting

The best way to get back to the original data which you have just sorted is to use the Control + Z keyboard shortcut to undo that action.

Sort by Multiple Columns with SORT function

With the SORT function, you can sort by multiple columns, that is without making any changes to the original data. Well, with the SORT, you can specify two columns to sort, and it will get the same data for sorted using those columns.

Click on the cell where you want the sorted data to start and enter the SORT function. For sorting by two columns, city and then by date of birth, you need to use a formula like the one below:

=SORT(A2:D31; 4; TRUE; 1; FALSE)

In this formula, A2:D31 is the range where your data is. And “4; TRUE” means sort by the fourth column (City) in ascending order (A to Z). In the end, “1, FALSE” means then sort by the first column (Date of Birth) in descending order (newest to oldest).

After entering the formula, press Enter. The data will be sorted in the new range based on your criteria.

Create a Google App Script to Sort by Multiple Columns with a Single Click

Firs, you need, to go to the Extensions in the menu, and choose Apps Script.

Copy and paste the following script:

function sortData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A2:D31"); // Adjust the range according to your data

  // Sorts by City (Column D, index 4) in ascending order,
  // then by Date of Birth (Column A, index 1) in descending order.
  range.sort([
    {column: 4, ascending: true},  // City in ascending order
    {column: 1, ascending: false}  // Date of Birth in descending order
  ]);
}

Now click on the floppy disk icon or press Ctrl + S to save. And then, click on the run button in the script editor to run the code.

Or you can use the below code, that can create a custom menu with the name “Multiple Columns Sort” in the menu. All, you need to do is to go to the App Script window and paste bellow code there, save it, and the refresh your Google Sheets workbook.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Sorting')
    .addItem('Sort Data', 'sortData')
    .addToUi();
}

And you can also create button so that we can sort our data based on those two columns anytime you need to just by clicking it.

Click on Insert menu, then select Drawing > + New. Use the shapes tool to create a button in the drawing dialog box. You can add text to the button like “Sort Multiple Columns”.

Now, click Save and Close to add the Drawing to your sheet.

Enter the name of the function you want to run when you click the button. Your function in the Apps Script editor is named “sortData”, so enter it and click OK. Now, click the button you created to run the script.