For small business owners and freelancers, Google Sheets is a suitable replacement for Microsoft Excel. It offers most of the features that work pretty similar to the ones in excel so switching to Google Sheets isn't a big deal.
Filter Function is one of the most powerful features Google Sheets has for handling the data. It filters out the data from multiple rows and columns of an array that meet a specified condition. Thus, it's not limited to filtering data from only a single row or column that increases its scope of use.
An Overview of the Syntax of Filter Function
By using this function, you can get multiple results of matches. And since your data is filtered with a function, the results will update automatically. Let's quickly go over the syntax of the Filter Function before moving forward on the formula implementation.
The filter function has three arguments; Range, Condition 1, and [Condition 2 ...]. The first two arguments are required, while the third argument is optional.
Do note that there is no limit to the number of conditions you can specify. So, you can continue to add conditions to the Filter functions for as long as needed. Let's discuss what this range and condition arguments represent:
- Range: The range argument refers to the data you intend to filter from an array. It can contain any number of rows or columns.
- Test condition 1: The condition arguments correspond to a set of true or false values in a column or row, whether they are associated with the first column or row of 'range' or a formula evaluating true or false.
- [Condition 2....]: It refers to additional rows or columns containing the boolean value TRUE or FALSE, which indicates whether the corresponding row or column should pass through the 'FILTER.'
If you use the Filter Function, you must specify filtered data and one condition argument. Creating a dropdown for the test condition will be easier to filter out the desired data by updating the selection.
Note that the filter function spills all the time. So, you won't have to add dollar signs ($), which you usually put in while executing range references. You can add one formula, and it'll return results from the entire array.
Example for Filter Function
Let's take a look at the below data to see the implementation of the Filter Function.
You can find information about the sales and profits for various grocery items across different states in the US.
Let's say you want to filter the grocery items, sales, and profit by state. Here you have multiple matches for each state, and your range is these three columns.
You can use the filter function to filter the desired result. As an array column containing the names of states is your condition 1, let's create a dropdown for it.
Creating a Dropdown for Tested Condition
1. Right-click on a cell to open the dropdown menu.
2. Scroll down and go to the data validation section.
3. In data validation, set criteria to be List from a range.
4. Click on the square boxes in the criteria section to choose a range or formula.
5. In this case, the state column will be the range. You will need to specify the entries from Cells A2 to A13 here.
6. Save the Data Validation settings by clicking Save.
This will create a dropdown menu consisting of unique items from the selected range.
Now that you have the dropdown menu created, let's begin filtering grocery items, sales, and profit for each state using this Filter function.
Related: How to Create a Checklist in Microsoft Excel
Implementing the Filter Function
Range, in this case, is the data in the three columns, Sales, Grocery items, and Profit. Let's choose cells B2 to D13 in the first argument of the Filter function.
Suppose you wish to add more data to already selected columns as a range. Keep the second value in the range open-ended. You can do this by removing the cell number, which in this case is 13.
As soon as you add a new value, the filter function will take these into account without requiring you to filter them again.
The condition argument in this example will be the state. Select the state column by keeping it open-ended and equal it to the cell containing the dropdown menu from where you will assign filters for each region.
To ensure consistency across the two datasets, make sure that both ranges have the same height.
Once you press Enter to execute the formula, it will filter out the data of grocery items, sales, and profits for the state of taxes from the mixed dataset.
Additionally, note that the Filter function only includes valid values. If the tested value is true, the value in the array is considered; otherwise, the function ignores it and skips to the next.
In this example, when you select Utah from the dropdown, you will see the relevant data for that state in the filter area.
Filtering Data by Testing Multiple Conditions
Let's update the function to include a profit greater than 5000 as the second condition. To filter data meeting both conditions, you need to add this new condition to the filter formula.
1. Go to Cell F8, where you added the formula before.
2. Choose the profit column as a range for the second condition, and the new formula looks like this:
=Filter(B2:D,A2:A=G5,D2:D>5000)3. Press Enter to execute the formula.
The profit for flour in Utah was 1240, and the filter function ignored it because it did not meet the second condition. In the same way, you can filter the data by testing any number of conditions.
Related: How to Remove Duplicates in Excel
Have More Control Over Filtering Data in Google Sheets
In Google Sheets, the Filter Function comes in handy when filtering some data after testing a large dataset. It is important to remember that the filter function in Google Sheets differs slightly from Microsoft Excel. So keep its syntax in mind during implementation.
You can further enhance your productivity by integrating Google services like Google Forms with Google Sheets.
