The combination of SORT and FILTER functions in Google Sheets helps users filter and sort data quickly and easily, even in large datasets. This function is a combination of both SORT and FILTER functions and is widely used in data management. Explore this combination with FPT Shop through this article.
How to use the SORT function in Google Sheets
Before we get into the instructions on how to use the combination of SORT and FILTER functions in Excel software, let’s explore the function and usage of the SORT function separately.
What does the SORT function do?
The SORT function is used to sort the rows of a given range of cells by a value that can be from one or more columns. The SORT function helps users sort data in ascending or descending order.
Understand the general usage of the SORT function
The formula of the SORT function is:
=SORT(range; sort_column; ascending; [sort_column_2; …]; [ascending_2; …])
Explanation of the SORT function formula:
- The range is the area containing the values that the user wants to sort.
- The sort_column is the column of data or calculation column outside the range of cells containing the values to be sorted.
- Ascending, with the symbol TRUE: Sort the column to be sorted in ascending order. And vice versa, FALSE is sorting the calculation column in descending order.
- [sort_column; …]; [ascending_2; …]: Indicates the same for other sort_column and ascending arguments and also in ascending order.
For example, let’s illustrate the SORT function:
With the given data table that includes information such as Quantity, Product, and Supplier. Requirements: Apply the SORT function to sort the data table in alphabetical order with the Supplier column.
Syntax: =SORT(A2:C15;2;TRUE)
Explanation of the above syntax:
- A2:C15: The data range (data table) containing the information to be sorted.
- 2: The data column with the value that the user wants to sort, which is the Supplier column.
- TRUE: The symbol represents sorting the characters in ascending order.
How to use the FILTER function in Google Sheets
Learn about and understand the function and usage of the SORT function separately. To get to the instructions on how to use the combination of SORT and FILTER functions in Excel software, in the next section.
What does the FILTER function do?
The FILTER function helps users filter data that meets the required conditions. When filtering data, the function does not affect other data, but only considers and selects data according to the user’s preferences.
Understand the general usage of the FILTER function
The formula of the FILTER function in Google Sheets is:
= FILTER(range; condition 1; [condition 2; condition 3; …])
Explanation of the FILTER function formula:
- Range is the data range containing the values you want to filter.
- Condition 1; condition 2; condition 3; … are the required conditions for the value to be filtered.
For example, let’s take an example of how to use the FILTER function:
Still using the data table from the SORT function section. The requirement is to apply the FILTER function to filter products for each supplier.
The formula is: =FILTER(A2:A15;B2:B15=F1).
Explanation of the above formula:
- A2:A15 is the range that contains the Product column values that need to be filtered.
- B2:B15=F1 represents the condition of the problem to filter the products for each supplier.
Combining SORT and FILTER functions in Google Sheets
The formula for the combination of SORT and FILTER functions is as follows:
=SORT(FILTER(range; condition 1; [condition 2; …]); sort_column; ascending; [sort_column; …]; [ascending 2; …])
The components are:
- FILTER(range; condition 1; [condition 2; …]): The filtered data range through the FILTER function to perform sorting.
- sort_column: Indicates the data column located in or out of the range of cells containing the values that the user wants to sort.
- ascending: With the symbol TRUE, it represents sorting the column in ascending order. And with the symbol FALSE, it represents sorting the column in descending order.
- [sort_column; …]; [ascending 2; …]: Indicates the same meaning as the other sort_column and ascending arguments mentioned above.
Consider the following example:
Continue using the data table provided in the SORT function section. Apply the combination of SORT and FILTER functions to filter products for each supplier and sort them in descending order.
=SORT(FILTER(A2:A15;C2:C15=F1);1;FALSE).
Explanation of the SORT and FILTER functions:
- FILTER(A2:A15;C2:C15=F1): The FILTER function filters the product values for each supplier.
- 1: Represents the column of data with the value that needs to be sorted, which is the Product column.
- FALSE: The symbol represents sorting the values in descending order.