Oftentimes after gathering and collating data onto a spreadsheet, we want to focus on important figures that are relevant to our analysis. To do that, we have to filter our data. Google Sheets allows us to do this with the built-in FILTER function. This function works when you specify the range of data you want to be filtered and the Google Sheets filter conditions. This post will walk you through some frequently-used filter formulas to get common analyses.
- Using FILTER with a single condition
- Using FILTER with multiple AND conditions
- Using FILTER with multiple OR conditions
- Filtering data with Airboxr
Let's say we want to analyze the traffic coming to our website with this dataset from Google Analytics.
Using FILTER with a single condition
We want to find out which Source/Medium brings in more than 10 users. That is our condition.
First, we will specify the range of the column, Users then the operation:
D2:D16>10
Specify the range of data you want to filter and combine this with the condition formula above:
=filter(A2:E16, D2:D16>10)
And you're done!
Using FILTER with multiple conditions
The FILTER function doesn't just support one condition. It can filter multiple conditions and columns as well.
With multiple conditions, you have two options—do all the conditions have to be fulfilled or at least one? In other words, they are AND or OR conditions respectively.
First, we will explain the scenario with the AND condition.
Bringing back our original dataset, we now want to find out which Medium brought in more than 10 users from the Source, Google. This means the Users must be more than 10 and the Source must be Google. Each condition is separated by a comma:
D2:D16>10, B2:B16="google"
As you can see from above, we are filtering multiple columns. Similar to before, specify the range of data and the final filter formula will be:
=filter(A2:E16, D2:D16>10, B2:B16="google")
Notice how the data is only chosen when all the conditions are met.
Next, we will explain the scenario with the OR condition.
This time, we want to find out which Source/Medium brings in more than 10 users or more than 10 sessions. The formulas are now written differently—they are separated by brackets and the plus sign instead:
(D2:D16>10)+(E2:E16>10)
That will make the complete formula:
=filter(A2:E16, (D2:D16>10)+(E2:E16>10))
As mentioned earlier, the row of data will be selected once either condition is met.
Having just one or two conditions is easy to convert into formulas, but what if you have much more complicated operations?
Choose the easy way with Airboxr.
This time, we want to find out which Medium brought in more than 10 users and 10 sessions from Google. Sounds complicated? Let's see how filtering works with Airboxr.
Launch the add-on and import our data from Google Analytics. Select the columns you want to pull data from. In this case, we want the date, source, medium, users, and sessions.
And now we filter. First, we choose the dates that are relevant to us. From the original dataset, we are only interested in 22/11/2021. Since we are focusing on Google as the source, that is what we are going to type in for the second filter.
The third and fourth filters represent the conditions of more than 10 users and more than 10 sessions respectively. Lastly, just click import.
And there you have it! No more complicated formulas. With Airboxr, you can spend your time analyzing data, not cleaning them. Download the add-on now.