All resources
Sorting data effectively in Google Sheets can significantly enhance your ability to manage and analyze large datasets. This guide explores the SORT and SORTN functions, providing practical examples to help spreadsheet users, data analysts, and business professionals optimize their data organization workflows.
Whether you need to arrange data alphabetically, rank top-performing metrics, or apply advanced sorting techniques, this article will equip you with the tools and insights needed to streamline your process.
The SORT and SORTN functions in Google Sheets are essential tools for organizing and ranking data dynamically, allowing efficient analysis without altering the original dataset.
These functions are highly valuable for use cases such as managing inventory, analyzing performance metrics, and prioritizing tasks. By leveraging SORT and SORTN, users can streamline data workflows, gain insights, and easily make better decisions.
These tools offer a seamless way to organize, rank, and filter data, enhancing productivity and ensuring accurate data interpretation in Google Sheets.
The SORT and SORTN functions in Google Sheets provide dynamic ways to organize and rank data. While SORT arranges data based on specified columns, SORTN extracts top or bottom rows with advanced ranking options. These functions streamline workflows, making data analysis more efficient and actionable.
To effectively use the SORT and SORTN functions in Google Sheets, it's essential to understand their syntax and how each parameter influences results. Let’s break down their usage through concise examples highlighting their unique capabilities, such as sorting datasets by multiple criteria or extracting ranked rows with precision.
The SORT function allows you to sort based on one or multiple columns, making it ideal for creating logical data arrangements.
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2], ...)
Let’s break down what these parameters represent:
The function allows you to dynamically sort your data without altering the original order, making it an essential tool for organizing and analyzing large datasets in Google Sheets.
Suppose you have an employee dataset and want to organize it by Joining Date in ascending order to identify the earliest joiners.
Syntax:
=SORT(B3:D10, 3, TRUE)
Here:
The formula dynamically sorts the data based on the Joining Date, ensuring the earliest joiners are listed first.
The SORTN function in Google Sheets is used to extract the top or bottom N rows from a dataset. It allows advanced ranking with options for handling ties and sorting by multiple criteria, making it ideal for filtering ranked subsets like top performers or bottom sales items.
=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)
Let’s break down what these parameters represent:
The function is perfect for scenarios requiring ranked subsets, enabling efficient filtering and analysis in large datasets.
Suppose you have an employee dataset and want to find the top 3 employees who joined most recently (based on the Joining Date column).
Syntax:
=SORTN(B3:D10, 3, 0, 3, FALSE)
Here:
This formula dynamically extracts the top 3 rows with the most recent joining dates, making it easy to focus on recent additions to the team.
In this section, we’ll explore practical examples that demonstrate how to efficiently use these functions to sort data and extract top or bottom rows for better analysis.
When sorting data in descending order, the SORT function arranges values from highest to lowest. This is useful for highlighting the largest or most important entries first.
Suppose you have a list of names and want to sort them in reverse alphabetical order.
To achieve this, you can use the following formula:
=SORT(B3:B10, 1, FALSE)
Here:
Using this formula, the names will be arranged in reverse alphabetical order, making it easier to highlight or analyze the data based on descending priorities.
Sorting numerical data by value using the SORT function is straightforward. You can arrange values in ascending order for more accessible analysis, or in descending order to highlight the highest scores first.
Suppose you have a dataset of performance scores and want to sort in ascending or descending order. We will see how to achieve both.
To achieve the ascending order, you can use the following formula:
=SORT(B3:C10, 2, TRUE)
To achieve the descending order, you can use the following formula:
=SORT(B3:C10, 2, FALSE)
Here:
Using these formulas, you can dynamically organize the performance scores to display either the lowest or highest values, depending on your needs. This is particularly helpful for performance reviews or ranking analysis.
Sorting data by a range reference instead of a column number simplifies the sorting process by directly referencing the column to be sorted.
Suppose you have a dataset of performance scores and want to sort it by the "Performance Score" column in ascending order.
To achieve this, you can use the following formula:
=SORT(B3:C, C3:C, TRUE)
Here:
Using this formula, the dataset will be dynamically organized by performance scores in ascending order, making highlighting the lowest to highest scores easy without specifying a fixed column index.
Sorting data by multiple columns allows you to prioritize the organization of your dataset by applying layered sorting criteria.
Suppose you want to first sort by Department alphabetically (ascending) and then sort by Person Name alphabetically (ascending) within each department.
To achieve this, you can use the following formula:
=SORT(B3:C10, 2, TRUE, 1, TRUE)
Here:
Using this formula, the dataset will be organized first by department, and then names are sorted alphabetically within each department, providing a well-structured output.
The SORTN function in Google Sheets allows you to extract the highest value from a dataset, including all tied values. This feature is useful for identifying top performers or results with multiple ties.
Suppose you want to extract the highest revenue target (%) from a dataset while including all tied values.
To achieve this, you can use the following formula:
=SORTN(C3:C10, 2, 1, 1, FALSE)
Here:
This formula extracts all occurrences of the highest revenue target value (115), including ties, ensuring every instance of the top value is displayed in the results.
The SORTN function can extract the top performers from a dataset based on specific criteria.
Suppose you have a dataset of employees and their performance scores and want to identify the top 5 performers for recognition.
To achieve this, you can use the following formula:
=SORTN(B3:C10, 5, 0, 2, FALSE)
Here:
This formula dynamically ranks and returns the top 5 representatives based on their performance scores, making it easy to identify the highest achievers in the team.
The SORTN function can extract the lowest performers from a dataset based on specific criteria. In this case, we’ll find the bottom 5 representatives based on the number of projects completed.
Suppose you have a dataset of employees and their project completion counts, and you want to identify the bottom 5 performers to plan improvements or interventions.
To achieve this, you can use the following formula:
=SORTN(B3:C10, 5, 0, 2, TRUE)
Here:
This formula dynamically ranks and returns the bottom 5 representatives based on their project completion counts, enabling you to identify the team members needing additional support quickly.
The SORT and SORTN functions in Google Sheets can transform your data handling. Advanced techniques allow you to organize datasets dynamically, extract top or bottom performers, or sort using multiple criteria. These functions empower you to analyze complex datasets effectively and make well-informed decisions efficiently.
We can use the SORT function to organize data from a different tab in Google Sheets. This is particularly useful for cross-sheet analysis and reporting.
Suppose you want to sort data from another tab named Dataset based on the third column (Department) in ascending order.
To achieve this, you can use the following formula:
=SORT(Dataset!B3:E, 3, TRUE)
Here is the dataset in the tab which is named as “Dataset”:
The output is in the different sheet:
Here:
This dynamically organizes data from a different tab, which is useful for cross-sheet analysis.
The SORT function in Google Sheets allows you to sort values in one range based on the order of values in a whole separate range — a useful method when you want to organize data without disrupting the original dataset.
Let’s say you have a list of employees and their existing Employee IDs in column C, but you’ve been given a new list of New Employee IDs in column B. Instead of overwriting the existing values, you want to generate a new column in D that shows the Employee IDs sorted based on the names from column B in descending order.
To achieve this, you can use the following formula in cell D3:
=SORT(C3:C10, B3:B10, FALSE)
Here:
As a result, you get the New Employee ID column in D, which shows the original IDs sorted according to the descending order of names in column B.
Important: Since column C (the range to be sorted) and column B (the sort criteria) are separate, it’s crucial not to delete either range — the formula depends on both being present.
This method is ideal when you want to sort a dataset based on an external list — like ranking IDs by new name order — without changing or reordering the rest of your table.
The SORT function in Google Sheets lets you sort data using multiple columns with different conditions, allowing for complex data organization.
Suppose you want to sort a dataset by Projects Completed (Column 3) in ascending order and then by Person Name (Column 1) in descending order.
To achieve this, you can use the following formula:
=SORT(B3:D10, 3, TRUE, 1, FALSE)
Here:
This formula organizes the dataset first by the number of projects completed, and for rows with the same number of projects, it sorts by the person’s name in descending order.
The SORTN function in Google Sheets allows you to extract the top rows based on specific criteria while excluding ties. This is useful when creating unique rankings for detailed analysis and reporting.
Suppose you want to extract the top 5 rows based on Salary (Column 3) in descending order, excluding ties.
To achieve this, you can use the following formula:
=SORTN(B3:D10, 5, 0, 3, FALSE)
Here:
This ensures that only the top 5 unique rows based on salary are included in the output.
The SORTN function in Google Sheets allows you to include tied rows when extracting the top results. This ensures that all rows with the same rank are included in the output, even if it exceeds the specified limit.
Suppose you want to extract the top 4 rows based on Salary (Column 3) in descending order and include any tied rows.
To achieve this, you can use the following formula:
=SORTN(B3:D10, 4, 1, 3, FALSE)
Here:
This formula ensures all tied rows for the last rank are included in the output, providing an accurate ranking.
The SORTN function allows you to sort data dynamically while removing duplicate rows. This is especially useful when working with large datasets where you must focus on unique values while sorting by a specific criterion.
Suppose you want to remove duplicate salary values from a dataset and display the top 5 unique salaries sorted in descending order.
To achieve this, use the following formula:
=SORTN(B3:D10, 5, 2, 3, FALSE)
Here:
The top 5 unique salaries are displayed, removing duplicates and sorting in descending order. For example, duplicate salaries of, 50000 (Alice Johnson and Grace Lee) and, 55000 (Charlie Brown and Henry White) are resolved, keeping only the first occurrence for each.
💡 Learn how to simplify this process by following our step-by-step guide on how to remove duplicates in Google Sheets. This article will walk you through multiple methods to ensure your data remains accurate and organized.
The SORTN function helps sort and select rows based on specified criteria. By setting the third argument to 3, you can include both unique rows and their duplicates in the result. This is particularly useful when you want to analyze a dataset while preserving duplicate values.
Suppose you want to show the top 5 unique rows based on salary and include duplicates for those rows.
To achieve this, use the following formula:
=SORTN(B3:D10, 5, 3, 3, FALSE)
Here:
Setting the third argument of SORTN to 3 retains duplicates for the top 5 unique salaries. For example, 55000 appears twice, showing both Charlie Brown and Henry White.
The SORTN function in Google Sheets helps you extract the top 5 rows based on dates, sorted in descending order. This feature is ideal for identifying the most recent entries in a dataset, such as new hires or recent updates.
Let's extract the top 5 rows sorted by Joining Date, making it easy to focus on the most recent entries in your dataset.
To achieve this, use the following formula:
=SORTN(B3:D10, 5, 0, 2, FALSE)
Here:
The formula extracts the top 5 rows by Joining Date, sorted in descending order, ensuring that the most recent dates are displayed first. This is a simple yet effective way to analyze datasets for chronological insights.
The SORTN function in Google Sheets enables advanced sorting by allowing you to specify multiple columns as criteria. By combining these columns, you can define primary and secondary priorities for your data.
Suppose we want to extract the top 5 rows sorted first by Joining Date in descending order (most recent first) and then by Salary in descending order to break any ties.
To achieve this, use the following formula:
=SORTN(B3:D10, 5, 0, 2, FALSE, 3, FALSE)
Here:
The formula sorts the dataset first by Joining Date to display the most recent entries, and then by Salary to prioritize higher earnings for rows with the same date.
Combining SORT and SORTN with other Google Sheets functions allows you to perform advanced operations like filtering, removing duplicates, and querying data dynamically. Here are examples of how to integrate these functions for enhanced data manipulation.
The SORT and FILTER functions allow you to organize and filter data based on specific conditions dynamically. Combining these functions allows you to extract rows matching multiple criteria and sort them by one or more columns, providing a clean dataset for analysis.
Suppose, we aim to identify employees from the Sales department whose salary exceeds 45000. Using AND logic, we’ll filter rows meeting both conditions and sort the results by Salary in ascending order and by Person Name as a secondary sort criterion.
To achieve this, use the following formula:
=SORT(FILTER(B3:D10, C3:C10="Sales", D3:D10>45000), 3, TRUE, 1, TRUE)
Here:
By combining SORT and FILTER with AND logic, we successfully extracted and sorted employees in the Sales department earning more than 45000.
In this example, we aim to filter rows where either the department is "Sales" or the salary is greater than 55000. Using OR logic, we will extract all matching rows and sort them by Salary in ascending order, with a secondary sort by Person Name in alphabetical order.
To achieve this, use the following formula:
=SORT(FILTER(B3:D10, (C3:C10="Sales")+(D3:D10>55000)), 3, TRUE, 1, TRUE)
Here:
This formula filters rows that match either condition (Sales department or Salary > 55000) and sorts them by Salary first, then Person Name.
💡 Ever wondered how to extract meaningful data from large datasets effortlessly? The FILTER function in Google Sheets is your ultimate tool for dynamic filtering. Click here to read the full guide and elevate your data analysis skills.
When working with horizontally arranged data, the SORT function alone isn’t sufficient, as it’s designed for vertical data. To sort horizontally, you can use the TRANSPOSE function to temporarily rearrange the data vertically for sorting and then restore it to its original horizontal format.
Suppose the dataset contains employee IDs and their corresponding names arranged horizontally. We aim to sort the Employee IDs in ascending order and adjust the names accordingly while maintaining the horizontal layout.
To achieve this, use the following formula:
=TRANSPOSE(SORT(TRANSPOSE(C3:J4), 1, TRUE))
Here:
Using SORT and TRANSPOSE together allows you to sort horizontally arranged data without disrupting its structure. This method is especially useful for compact datasets or dashboards that require horizontal formatting.
The SORT and UNIQUE functions allow you to clean, organize, and analyze datasets by removing duplicates and arranging data in a logical order. Whether working with a single column or multiple columns, these functions simplify data processing.
Suppose we want to extract unique salary values from the Salary column, remove duplicates, and sort them in ascending order. This helps create a clean and organized list of all distinct salaries.
To achieve this, use the following formula:
=SORT(UNIQUE(D3:D10), 1, TRUE)
Here:
This formula simplifies the dataset by removing duplicate salaries and sorting them. It provides a clear view of unique salary figures for easier analysis.
Now let's identify unique combinations of Department and Salary, remove duplicates, and sort the results by Department alphabetically and then by Salary numerically.
To achieve this, use the following formula:
=SORT(UNIQUE(C3:D10), 1, TRUE, 2, TRUE)
Here:
Using this formula, we generated a dataset that shows unique department-salary combinations in a sorted format. It’s ideal for organizing data for analysis or reporting purposes.
The combination of SORT and COUNTIF functions in Google Sheets allows you to organize data based on how often each value appears. This method is especially useful for analyzing trends, identifying the most common values, or understanding the distribution of your data.
In this example, we aim to sort the dataset based on how often each salary occurs (frequency) and, as a secondary sorting criterion, arrange salaries in ascending order within the same frequency.
We'll first add a frequency column to help readers understand the frequency calculation.
In Column E (Frequency), use the following formula in E3:
=COUNTIF(C$3:C$10, C3)
This calculates the number of occurrences of each salary in the Salary column (C3). Drag the formula down to fill the rest of the column.
Now, we’ll sort the dataset based on the frequency (descending order) and, for rows with the same frequency, by salary (ascending order).
Use the following formula:
=SORT(B3:C10, COUNTIF(C$3:C$10, C3:C10), FALSE, 2, TRUE)
Here:
By combining COUNTIF and SORT, this formula allows you to dynamically organize data based on frequency and salary, ensuring a clear and structured output.
💡 Master data analysis in Google Sheets with the COUNTIF and COUNTIFS functions. This comprehensive guide by OWOX covers everything from filtering data to applying multiple conditions. Simplify your workflows and enhance your spreadsheet skills.
Combining SORT and SORTN with QUERY in Google Sheets provides a powerful way to filter, organize, and analyze data dynamically. These functions allow you to sort datasets, apply conditions, and extract specific rows based on criteria, all within a single formula.
Suppose we aim to sort the dataset by salary in descending order and filter out rows where the salary is less or equal to 55000. The combination of SORT and QUERY functions achieves this dynamically.
Here is how we can achieve that:
=QUERY(SORT(B3:C10, 2, FALSE), "SELECT * WHERE Col2 >= 55000")
Here:
This formula sorts the dataset in descending order by salary and dynamically filters out rows with salaries less than or equal to 55000. It’s an efficient way to combine sorting and filtering in a single formula.
In many situations, you might have employees who record performance scores across multiple months. These scores reflect their contributions over time, and aggregating these scores can help you identify the top performers.
For example, the same employee may have different performance scores for different months in a dataset. To rank employees by their total performance scores and determine the top three performers, we can use a combination of the QUERY and SORTN functions.
Here's how we can analyze the dataset:
=SORTN(QUERY(B2:D12, "SELECT B, SUM(C) WHERE B IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC"), 3, 1)
Here:
This formula dynamically aggregates performance scores and ranks employees to identify the top three performers. It’s a practical method for evaluating contributions across multiple periods and ensuring fair ranking, even in cases of ties.
Errors in SORT and SORTN functions can disrupt data organization and analysis. Understanding these errors and their solutions ensures smooth workflows, accurate sorting, and reliable insights, saving time and preventing frustration during data manipulation in Google Sheets.
⚠️ Error: The #VALUE! error in SORT and SORTN arises when the range includes non-numeric values, blank cells, Boolean values (TRUE/FALSE), or error values. It can also occur if the input range is not continuous, disrupting the sorting process.
✅ Solution: Ensure the range is continuous and contains only numeric or text data. Manually review the range to remove blank, Boolean, or error values. Alternatively, use the IFERROR function to replace errors with valid data, ensuring smooth sorting operations.
⚠️ Error: The #N/A error in SORT occurs when the function cannot locate the value to sort. This may happen if the value is missing from the range or is of a data type that the SORT function cannot process.
✅ Solution: Verify that the value exists in the range; if not, add it. For unsupported data types, convert them to sortable formats. For instance, transform dates into numeric values representing days for proper sorting.
#REF! Error
⚠️ Error: The #REF! error in SORT and SORTN occurs due to invalid range references, often caused by deleting or moving cells included in the function's range. Additionally, it may arise when the array result cannot expand because of data blocking the output cells.
✅ Solution: Update the SORT or SORTN function to reflect the current range of your data. If the error is due to blocked cells, clear or move the blocking data to create space for the array output. This ensures the function works seamlessly.
⚠️ Error: A circular reference error happens when the SORT function refers to the cell it’s in, creating a loop that Google Sheets cannot process. This disrupts the calculation and results in an error.
✅ Solution: Ensure the SORT function does not include its own cell in the referenced range. Carefully review and adjust the range in your formula to eliminate any self-references, resolving the circular dependency.
⚠️ Error: Misinterpreting the sorting criteria in SORTN can lead to unexpected results. This occurs when the sort order or column index is misunderstood, such as using the wrong column for prioritization or incorrectly specifying ascending/descending order.
✅ Solution: Double-check the sort order parameter (TRUE for ascending, FALSE for descending) and verify the column index matches your intended sorting logic. Clearly define your sorting priorities to ensure accurate results.
⚠️ Error: An incorrect range in SORTN occurs when the specified range is incomplete, overlaps invalid data, or doesn’t include all necessary rows and columns for sorting. This can result in missing or inaccurate output.
✅ Solution: Verify that the selected range is complete and free of errors. Ensure it covers all relevant data without including unnecessary or invalid cells. Adjust the range in the formula to match your dataset accurately.
⚠️ Error: Missing parameters in SORT or SORTN, such as the sort_column, can cause unexpected results. Without this detail, Google Sheets cannot determine the sorting criteria, leading to incomplete or incorrect data arrangement.
✅ Solution: Always specify the sort_column parameter in your formula. Double-check the syntax to ensure all required arguments are included, allowing the function to sort data as intended.
Using SORT and SORTN effectively requires an understanding of best practices to streamline data organization and reduce errors. Here are some tips to help you make the most of these functions.
Explore various sort orders with SORT and SORTN to organize data effectively. Sort data in ascending or descending order, and combine criteria to highlight trends or anomalies. Testing different arrangements ensures optimal clarity and insight from your datasets.
Pair SORT and SORTN with functions like FILTER and, QUERY to create advanced workflows. For example, filtering a dataset before sorting allows you to focus on relevant data without manually adjusting the range.
When using SORTN, verify that your data range is accurate and complete. An incorrect range can lead to missing or misleading results. Double-check your selection to include all relevant data for consistent and reliable sorting outcomes.
In SORTN, the limit parameter determines how many unique rows are returned. Double-check this value to ensure it matches your requirements. Setting it incorrectly might exclude essential data or return more results than needed, affecting analysis accuracy.
With SORT, you can include secondary sort columns to refine your data arrangement further. Specify multiple criteria to organize data hierarchically, ensuring clarity when primary columns contain duplicate values. This technique enhances precision and readability in your sorted datasets.
For quick sorting without formulas, use the "Sort Range" option in Google Sheets. It allows you to sort a specific range of data by columns instantly. This is ideal for one-time sorting or quick adjustments during analysis.
Unlock the full capabilities of Google Sheets with essential functions tailored for comprehensive data analysis. These versatile formulas simplify complex tasks, enabling you to work efficiently with large datasets, automate repetitive processes, and easily gain actionable insights.
OWOX: Reports, Charts, and Pivots extension streamlines your data analysis process, offering powerful tools for creating detailed reports, interactive charts, and dynamic pivot tables in Google Sheets. Designed to minimize manual effort, this extension enables users to focus on uncovering valuable insights rather than wrestling with complex data.
By integrating seamlessly with your existing data sources, OWOX ensures that your reports and visualizations are always accurate and up-to-date. The intuitive interface simplifies customizing charts and pivot tables, allowing users of all skill levels to transform raw data into clear, actionable insights.
.png
)
