06 Easy Ways to Sort Data in Google Sheets, You Must Know

In Google Sheets, there are several functions and methods to sort data dynamically or manually. Sorting can be based on rows, columns, or specific conditions. The most versatile and commonly used function for sorting is the SORT function. Here are the 06 types of SORT technique in Google Sheets:

Types of Sorting, 06 Easy Techniques in Google Sheets:

• SORT Function: Best for dynamically organizes data in ascending or descending order based on specified columns, ideal for automated sorting (commonly used).
• Built-In Sort Features: Perfect for quick, manual sorting directly within the sheet, no formulas needed.
• SORTN Function: Extracts and sorts the top or bottom N rows efficiently, perfect for ranking data dynamically.
• FILTER with SORT: Combines filtering and sorting for conditional data organization.
• QUERY Function: Advanced SQL-like sorting, making it ideal for handling complex datasets efficiently.
• Apps Script: Enables programmatic and automated sorting, offering unparalleled flexibility for managing and organizing complex datasets.

Each techniques offers unique benefits, making it easy to adapt to various data management needs in Google Sheets.

1. SORT Function

The SORT function in Google Sheets is a powerful tool for organizing data dynamically. It creates a new sorted copy of your data based on specified columns and sorting orders, in either ascending or descending. Unlike manual sorting, the function updates automatically whenever the original data changes, making it ideal for handling dynamic datasets.

Key Features of the SORT Function

• Creates a new sorted dataset without altering the original data.
• Automatically updates the sorted results when the source data changes.
• Accepts numbers (1,2,3…), text (A,B,C…), or dates for sorting.
• Supports ascending (small to big) or descending (big to small) order.
• Allows multi-level sorting (e.g., first by name, then by age).
• Outputs are formula-driven and cannot be edited directly.

Syntax:

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

• range: The range of data to sort.
• sort_column: The column index or reference to base the sorting on.
• is_ascending: TRUE or 1 for ascending order, FALSE or 0 for descending order.
• [ sort_column2, is_ascending2, ... ] – Additional columns and sort order flags beyond the first, in order of precedence.

In the image provided below, Red represents the Data Reference, and Green represents the Solution. The formula in the entire Green section is placed in cell H2.

Here formula put in cell H2

Example:

=SORT(A2:F11, 1, TRUE)

→ Sorts the range A2:F11 by column 1 (Rank) in ascending order.

=SORT(A2:F11, 1, TRUE)

→ Sorts the range A2:F11 by column 1 (Rank) in descending order.

=SORT(A2:F11, 2, TRUE)

→ Sorts the range A2:F11 by column 2 (Employee Name) in ascending order.

=SORT(A2:F11, 5, TRUE)

→ Sorts the range A2:F11 by column 5 (age) in ascending order.

=SORT(A2:F11, 5, TRUE)

→ Sorts the range A2:F11 by column E2:E11 [which corresponds to the 5th column (column E), (age) in ascending order.

Here above and below both formulas sort the data in ascending order based on the values in column E (age), so the output will be the same.

Both formulas achieve the same outcome: the first uses the column index number (5), while the second directly references the column range (E2:E11).

=SORT(A2:F11, E2:E11, TRUE)

Why You Can’t Directly Change or Edit SORT Function Results

In the image below, we attempt to modify the data where a SORT formula is already present in cell H2. Here we change in cell I7 as number value “452” shown as below.

After pressing ENTER, we are able to enter the value ‘452,’ but the rest of the data is lost. In cell H2, an error #REF! appears, and a popup message displays on the sheet saying, ‘Array result was not expanded because it would overwrite data in I7’ (shown as below).

The SORT function produces dynamic outputs based on the formula’s input data. Any attempt to edit these outputs directly will break the formula and result in an error. This happens because:

• The sorted results are tied to the logic of the SORT formula, if you try to edit the sorted results, it will break the formula, leading to an error.
• The function recalculates and updates whenever the source data changes, ensuring data consistency.

How to Edit the Sorted Data

💡The SORT function is a dynamic formula, so you can’t directly edit its output. However, converting the results to static values allows for manual changes.

If you want to modify the sorted data manually:

• Copy the results: Select the sorted data, then right-click and choose “Copy” or press Ctrl + C.
• Paste as values: Right-click the destination cell and choose “Paste special” → “Paste values only” or use the shortcut Ctrl + Shift + V.
• The pasted data becomes static, allowing manual changes.

Advantages of the SORT Function

• Automates sorting workflows, saving time.
• Maintains live synchronization with the original dataset.
• Eliminates the need for repetitive manual sorting tasks.

The results generated by the SORT function are dynamic and formula-driven, which means they cannot be edited directly. To make manual modifications, you must first copy the sorted results and paste them as static values. This function is particularly useful for creating live, automated sorting workflows in Google Sheets, saving time and ensuring data consistency.

By leveraging the SORT function effectively, you can streamline your data management, maintain an organized dataset, and eliminate the need for repetitive manual sorting tasks. Its ability to dynamically update based on source data makes it an invaluable tool for efficient and adaptable spreadsheet workflows.

ARRAYFORMULA for Sorting in Google Sheets

= ARRAYFORMULA ( array_formula )

• array_formula: The range, array, or operation to be applied.

=ARRAYFORMULA(SORT(A3:D12, 4, TRUE))

Explanation:

1. SORT(A3:D12, 4, TRUE): Sorts the dataset (Range A3:D12) by column 4 (Age) in ascending order (TRUE).
2. ARRAYFORMULA: Ensures the sorting applies to the entire array and populates all the results dynamically.

The ARRAYFORMULA function in Google Sheets enables dynamic operations over a range of cells, allowing calculations or manipulations on entire arrays instead of individual cells. While ARRAYFORMULA itself doesn’t sort data, it can be combined with other functions like SORT to achieve array-based sorting results dynamically.

When to Use ARRAYFORMULA with Sorting:

• Dynamic Data: To handle datasets that frequently change or expand.
• Auto-Updates: When you want sorting results to adjust automatically with data updates.
• Efficiency: To apply formulas to entire ranges without manual intervention.

YOU MUST KNOW?

• How to use TRUNC, MROUND and INT function in Google Sheets
• How to use ROUND, ROUNDUP and ROUNDDOWN function in Google Sheets

2. Built-In Sort Feature (Data Tab)

Google Sheets provides an easy way to sort data directly from the Data Tab without using formulas. This method allows you to organize your data in-place based on one or more columns.

Steps to Sort Data:

• Select the Data Range: Highlight the range of data you want to sort. Include the header row if applicable.

Go to the Data Tab → Sort range → Advanced range sorting options.
Navigate to the Data menu on the top toolbar. First is Sort sheet, in which you find two option choice, as shown below in first image

Choose a Sorting Option → Choose the column and order (ascending/descending).

• Sort Sheet by Column A (A → Z): Sorts the entire sheet based on the data in Column A in ascending order (A → Z for text or smallest to largest for numbers).
• Sort Sheet by Column A (Z → A): Sorts the entire sheet based on the data in Column A in descending order (Z → A for text or largest to smallest for numbers).

Advanced Range Sorting Options:

Advanced Options for Sorting Range for Specific Columns:

• Select Sort Range under the Data tab.
• Sort range from A1 to F11 (A1:F1 including as header row)
• Click on check box if your data has header row ( if unchecked sort by dropdown list showing list as column or if checked showing the header name)
• Choose to sort by one or more columns, specifying ascending or descending order for each column.

Key Features:

• Header Row Option: If your data has headers, check the box for Data has header row in the sort dialog. This ensures headers are not included in the sorting process.
• Dynamic Sorting: Unlike the SORT function, this method directly modifies the order of rows in your selected range.

Advanced Sorting by Rank and Age:

• Highlight the data range.
• Use Sort Range to sort by the Rank and Age column in (A to Z) order.

Sorting by Rank and Gender:

• Sort by Rank (A → Z) and then click on add another sort column by Gender.

Multi-Level Sorting Make Easy Way:

• By advance range sorting option, Add another sort column to add multiple sort criteria.

Advantages of Using the Built-In Sort Function:

• Quick and easy for static datasets.
• Doesn’t require formulas, making it ideal for one-time sorting.
• Works directly on the dataset, unlike the SORT function, which generates a sorted copy.

Limitations:

• Changes the original data order permanently.
• Not ideal for dynamic data that requires automatic re-sorting when values are updated.

This method is ideal for users who need to organize their data quickly and efficiently without relying on complex formulas.

YOU MUST KNOW?

• How to solve Line Break Errors (Ctrl+Enter) in Google Sheets
• How to use COUNT and LEN function in Google Sheets

3. SORTN Function in Google Sheets

The SORTN function in Google Sheets is a powerful tool that allows you to retrieve a specified number of rows from a dataset after sorting it based on one or more columns. It’s particularly useful for extracting the top or bottom values in a table, handling ties, and dynamically updating results when the data changes.

Purpose: Sorts data and returns the top or bottom N rows.

Syntax:

=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1, ...])

Arguments:

1. range (required): The range of data to sort and filter.
2. n (optional): The number of rows to return (default is 1 if omitted).
3. display_ties_mode(optional): Determines how ties are handled.
   • 0 (default): Exclude rows that tie at the last position.
   • 1: Include all rows that tie at the last position.
   • 2: Display exactly n rows, randomly breaking ties.
4. sort_column (optional): The column to sort by (specified as a number or range).
5. is_ascending (optional): TRUE for ascending order, FALSE for descending.

=SORTN(A3:D12, 5, 0, 4, FALSE)

→ Returns the top 5 rows from the sorted data (Column 4 or D [age in ascending or descending]) below both image.

• Returns the top 5 rows sorted by the 4th column (e.g., Age) in ascending order.
• Retrieves the top 5 rows based on the 4th column (e.g., Age) in descending order.

Multi-Level Sorting:

Sorts first by the 1st column (ascending) and then by the 3rd column (descending) before retrieving the top 3 rows.

• Example:
  =SORTN(A3:D12, 3, 0, 1, TRUE, 3, FALSE)

→ Returns the top 3 rows from the sorted data.

Formula Components:

=SORTN(A3:D12, 3, 0, 1, TRUE, 3, FALSE)

Key Features of SORTN:

1. Top/Bottom Values: Extracts a specified number of rows based on sorting rules (e.g., top 3 ranks, bottom 5 ages).
2. Dynamic Updates: Automatically adjusts results when the source data is modified.
3. Tie Handling: Provides options to include, exclude, or break ties based on the display_ties_mode setting.
4. Multi-Level Sorting: Allows sorting by multiple columns in either ascending or descending order, ensuring precise results.

Practical Use Cases:

• Finding the top N performers in a class or department.
• Identifying the lowest N expenses in a budget.
• Extracting the top N highest-paid employees.

Limitations:

• Cannot edit the results directly (formula outputs).
• Requires understanding of sorting rules and tie-handling modes.
• If the n value exceeds the number of available rows, it returns all rows.

The SORTN function is a versatile tool for creating concise, sorted views of data, perfect for reporting and analysis in Google Sheets.

YOU MUST KNOW?

• QUERY function in Google Sheets | Step-by-Step Guide
• How to Generate NEW YEAR CALENDAR in Google Sheets

4. FILTER Function (with Sorting)

The FILTER function in Google Sheets is used to extract rows or columns from a dataset that meet specific criteria. While the FILTER function alone does not sort the data, it can be combined with the SORT function to filter and organize the results dynamically.

Purpose: Filters and sorts data dynamically based on criteria.

Syntax:

=SORT(FILTER(range, condition1, [condition2, ...]), sort_column, is_ascending)

• range: The dataset to filter.
• condition1, condition2, …: Logical expressions that define the filtering criteria. Rows meeting all conditions are included.

• Example:
  =SORT(FILTER(A3:D12, D3:D12 > 40), 1, TRUE)
=SORT(FILTER(A3:D12, D3:D12 > 40), 1, FALSE)

→ the above first or second formulas, filter rows where column D > 40 and sort the results by column 1 (rank). The first formula sorts in ascending order (TRUE), while the second sorts in descending order (FALSE).

=SORT(FILTER(A3:D12, A3:A12 <= 4), 4, FALSE)

Explanation:

FILTER(A3:D12, A3:A12 <= 4):
Filters rows from A3:D12 where column A (rank) is less than or equal to 4.

SORT(..., 4, FALSE):
Sorts the filtered data by the 4th column (D, which represents age) in descending order (FALSE).

→ the above formula, filters rows where rank ≤ 4 (column A) and sorts the results by age (column 4) in descending order.

• FILTER: Selects rows where age (Column D) is greater than 40.
• SORT: Arranges the filtered rows by Rank (Column A) in ascending order.

Key Features of the FILTER Function:

• Conditional Data Retrieval: Filters data based on one or multiple conditions.
• Dynamic Updates: Automatically adjusts the filtered results when the source data changes.
• Works with Arrays: Can handle entire ranges, extracting only the rows or columns that meet the conditions.

Using FILTER with SORT:

When combined with SORT, the FILTER function can be used to filter and then arrange the results based on specific columns.

The FILTER function is a versatile tool for extracting data based on criteria, and when paired with SORT, it allows users to create dynamic, organized, and conditional datasets. This combination is ideal for generating ranked or prioritized lists from filtered subsets of data.

YOU MUST KNOW?

• How to fill the Serial Numbers automatically in the Google Sheets, Easy Way
• What is the SEQUENCE function and how to use?

5. QUERY Function (for Advanced Sorting)

The QUERY function in Google Sheets is a powerful tool that enables users to filter, sort, and manipulate data using SQL-like syntax. It offers advanced capabilities beyond standard sorting and filtering functions.

Key Features of the QUERY Function:

• SQL-Like Queries: Enables sorting, filtering, grouping, and performing calculations in one formula.
• Dynamic Sorting: Allows sorting by multiple columns and in custom orders (ascending or descending).
• Data Transformation: Can aggregate data, apply conditions, and even combine multiple operations in one step.
• Dynamic Updates: Automatically refreshes results when the source data changes.

Syntax:

QUERY ( data, query, [ headers] )

Explanation:

=QUERY(A1:F11, "SELECT B, C, D ORDER BY D DESC, C ASC", 1)

• "SELECT B, C, D": Selects columns Employee Name, Department, and Age.
• ORDER BY D DESC: Sorts by Age (Column D) in descending order.
• C ASC: If ages are the same, sorts by Department (Column C) in ascending order.
• 1 : Indicates the data includes 1 header row.

The formula organizes (arranges) the rows in the dataset by prioritizing the values in two specific columns, in a specified order. Here’s the breakdown:

• Primary sorting column (D):
  The data is sorted first based on the values in column D (e.g., in descending order, DESC).
• Secondary sorting column (C):
  If there are rows with the same value in column D, these rows are further sorted based on column C (e.g., in ascending order, ASC).

This means the primary sorting is determined by age, and if multiple rows have the same age, the sorting within those rows will then consider the department alphabetically.

Sort by column C and D, in which the function sorting first preferences is column C (department) and than D (age) as image below.

Does indeed sort the data based on two columns:

• Column C (department) in descending order (DESC).
• Column D (age) in ascending order (ASC) when there are ties in Column D.

This means the primary sorting is determined by department, and if multiple rows have the same age, the sorting within those rows will then consider the age alphabetically.

Advantages of QUERY for Sorting:

• Custom Sorting: Sort by multiple columns with specific orders.
• Advanced Filtering: Combine sorting with conditions (e.g., filter by age or department while sorting).
• Dynamic Results: Automatically adjusts when the source data changes.

The QUERY function provides advanced sorting and filtering capabilities with SQL-like flexibility. It’s ideal for managing complex datasets where multi-level sorting, dynamic updates, and custom conditions are required.

YOU MUST KNOW?

• SEARCH DATA by keywords in Google Sheets using the combination of FILTER, ISNUMBER and SEARCH function
• How to Separate Text and Letters in Google Sheets, REGEXREPLACE

6. Apps Script Sorting in Google Sheets: A Brief Overview

Apps Script is a powerful JavaScript-based scripting tool in Google Sheets that allows users to automate tasks and perform advanced operations, including custom sorting. It provides flexibility beyond built-in functions, enabling users to sort data programmatically according to specific requirements.

Key Concepts:

• Automated Sorting:
  • Use Apps Script to create scripts that sort data automatically based on predefined criteria (e.g., columns, order).
• Multi-Level Sorting:
  • Combine multiple sorting rules (e.g., sort first by age, then by name) with precision.
• Custom Logic:
  • Implement advanced sorting logic that is not possible with built-in functions (e.g., conditional or region-specific sorting).
• Dynamic Execution:
  • Trigger sorting scripts manually, on sheet edits, or at regular intervals using triggers.

Basic Syntax for Sorting:

function sortData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Replace with your sheet name
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()); // Adjust for your data range
  range.sort([{column: 3, ascending: true}, {column: 4, ascending: false}]); // Sort by column 3 (asc), then column 4 (desc)
}

Example: Sorting a Dataset:

Script:

function sortEmployeeData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Employees");
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4); // Assumes data starts at row 2
  range.sort([{column: 4, ascending: true}, {column: 2, ascending: true}]); // Sorts by Age, then by Name
}

Results:

Advantages of Apps Script Sorting:

• Customization: Tailor the sorting logic to fit specific needs.
• Automation: Eliminate repetitive manual sorting by setting up triggers.
• Scalability: Handle large datasets or apply sorting to multiple sheets simultaneously.
• Integration: Combine sorting with other tasks like data filtering, formatting, or exporting.

Apps Script provides a flexible and efficient way to implement sorting in Google Sheets. It allows users to automate and customize sorting tasks beyond the capabilities of built-in functions, making it a powerful tool for advanced data management.

Comparison of Methods

Google Sheets offers diverse tools for sorting, catering to varying levels of complexity and dynamic needs. For routine tasks, the built-in features suffice. For advanced automation and dynamic updates, formula-based methods or Apps Script are ideal. Select the approach that aligns best with your dataset requirements and workflow efficiency.

YOU MUST KNOW?

• What is UPS (Unified Pension Scheme), Is its a mixture of NPS and OPS?
• Why Government backtrack after 21 years?

Did you like this article?

If you found this post helpful, feel free to share it with your network using the social media buttons on X or via email located on the left below.

For any questions or assistance regarding the SORT function in Google Sheets, feel free to reach out in the comments. The CountLen team is dedicated to providing prompt and effective solutions, ensuring that all your queries are addressed swiftly. If you come across any inaccuracies or misleading information, don’t hesitate to share your feedback in the comments. We’re here to help you make the most of your data!