If you work in marketing and manage campaign data in Google Sheets, you've likely needed to filter specific rows based on custom criteria, such as channel, budget, or campaign status. The FILTER function in Google Sheets lets you extract only the rows you need, without complex scripts or manual work.
In this complete guide, you'll learn how to use the FILTER function in Google Sheets, with practical marketing-focused examples, common mistakes to avoid, comparisons with other similar functions, and a step-by-step section on how to automate filters using Dataslayer, so your reports stay clean and up-to-date automatically.
The FILTER function in Google Sheets allows you to automatically extract rows from a dataset based on one or more conditions you define. For example, you can use it to show only the rows where the campaign generated more than 10 conversions, or where the CPC is below a certain amount. Once set, the function continuously updates if the source data changes.
Another way to filter data in Google Sheets is by using the Filter View tool. This is a manual and visual way to temporarily hide rows that don’t meet specific criteria. It's accessible through the Google Sheets menu (Data > Filter views), and lets you create saved views of your data without changing the structure of the sheet for other users. However, Filter Views do not update dynamically based on changes in formula conditions and are not ideal for building dashboards or reports.
=FILTER(range, condition1, [condition2], ...)
To use the FILTER function in Google Sheets, start by selecting the range of cells you want to filter, for example, a table of marketing data from A2 to E100. This range should include all the rows and columns you want to evaluate. After that, you add one or more conditions, separated by commas. Each condition corresponds to a column in the range and specifies what values you're looking for.
For instance, you might write a condition like B2:B100 > 100 to show only rows where the number in column B is greater than 100. The FILTER function in Google Sheets will then return only the rows from your selected range that meet all the conditions you've written.
Marketing data is often noisy and vast. Using the FILTER function in Google Sheets helps you:
Imagine you have a report of all your Facebook Ads performance, and you want to focus only on ads that drove results. If column E contains the number of conversions, this formula helps you filter only those that achieved more than 5 conversions, so you can analyze what made them successful.
=FILTER(A2:E100, E2:E100 > 5)
Suppose you're running multiple Google Ads and want to focus on ads that are both cheap and effective. Column B holds the CPC (cost per click), and column C has the number of clicks. This formula filters only the ads with a CPC lower than 1.2 and more than 100 clicks, ideal for budget analysis and ROI tracking.
=FILTER(A2:D100, B2:B100 < 1.2, C2:C100 > 100)
If you're managing international campaigns and want to analyze only the results from Spain, this formula does exactly that. Assuming column D contains the country, you'll get a filtered list that includes only campaigns targeted to Spain.
=FILTER(A2:D100, D2:D100 = "Spain")
Let's say you're preparing a report that should only include ongoing or upcoming campaigns. If column C contains the campaign start date, this formula will show only those that began after January 1st, 2024. It's helpful for reviewing the current strategy without cluttering the view with past efforts.
=FILTER(A2:E100, C2:C100 > DATE(2024,1,1))
Before we compare these tools, let's clarify what the QUERY function is.
The QUERY function in Google Sheets allows you to retrieve specific data from a dataset using a language similar to SQL. It's very powerful for users who are comfortable writing queries and want to group data, perform calculations, or sort it with precision. Unlike the FILTER function, QUERY lets you select specific columns, apply multiple conditions, sort, and even aggregate (like SUM or COUNT) in one step. For example, you can write a query like:
=QUERY(A2:E100, "SELECT A, C WHERE D = 'Spain' AND E > 5 ORDER BY C DESC", 1)
This will return just columns A and C where the country is Spain and the number of conversions is above 5, sorted by column C in descending order.
So, when should you use each option?
FeatureFILTER FunctionQUERY FunctionFilter View ToolDynamic✅ Yes✅ Yes❌ NoSQL-like❌ No✅ Yes❌ NoEasy to use✅ Very⚠️ Medium (requires syntax)✅ VeryBest forDashboards, quick logicAdvanced logic, groupingsManual filtering
When working with the FILTER function in Google Sheets, especially with live marketing data, it's common to run into errors. These errors usually point to mistakes in how the formula is written, inconsistencies in the data, or logic problems in your conditions. Let's look at the most frequent errors, why they happen, and how to fix them with practical examples.
#N/AIf you're filtering conversions > 5 but your dataset doesn't have any such values, the result will show #N/A. You can avoid confusion by wrapping your formula with IFERROR():
=IFERROR(FILTER(A2:E100, E2:E100 > 5), "No results found")
#REF!If your output range is smaller than the number of rows returned, or your ranges are mismatched in size (e.g. filtering A2:A100 with B2:B50), you'll get a #REF! error. Make sure all ranges passed to FILTER are of equal length.
If a column used as a condition contains blank cells, this can lead to unexpected or missing results. You can filter out blanks explicitly using ISBLANK() or filter only cells that contain data:
=FILTER(A2:E100, NOT(ISBLANK(B2:B100)))
ErrorCauseSolution#N/ANo results match your criteriaUse IFERROR(..., "No results")#REF!Incorrect range or data out of boundsCheck your rangeBlank rowsCondition references empty cellsUse ISBLANK() or fill in values
It's useful to know that the FILTER function in Google Sheets becomes even more powerful when paired with other formulas. This can help you build smarter, more insightful reports with fewer manual steps.
FILTER with UNIQUEThis combination is great when you want to extract unique values that also meet a specific condition. For example, if column A contains campaign names and column B shows the channel, this formula returns only the distinct campaign names from Facebook:
=UNIQUE(FILTER(A2:A100, B2:B100 = "Facebook"))
This is useful to generate lists of unique assets or categories based on filtered results.
FILTER + SORT to show top-performing adsThis combo helps you not only filter data but also sort it by performance. For instance, if column B contains the number of conversions, this formula returns all rows where conversions are over 50 and then sorts them by the second column (B) in descending order:
=SORT(FILTER(A2:C100, B2:B100 > 50), 2, FALSE)
It's perfect for building ranked reports like top-performing campaigns.
FILTER + IMPORTRANGE (cross-sheet filtering)Use this when your data lives in another spreadsheet, but you still want to apply filters. For example, if you're pulling campaign data from another sheet and only want rows where the campaign type is Email:
=FILTER(IMPORTRANGE("sheet_url", "Data!A2:E100"), C2:C100 = "Email")
Make sure to allow access the first time IMPORTRANGE is used. This setup is ideal for centralizing reporting across multiple sheets.
FILTER + ISNUMBER + SEARCH for partial text matchingThis method helps when you need to filter based on keywords that might appear anywhere in a text cell. For example, to return all campaigns that include the word "retargeting" in the name (column A):
=FILTER(A2:C100, ISNUMBER(SEARCH("retargeting", A2:A100)))
Useful when naming conventions vary slightly or when you want to group related campaigns.
FILTER + TODAY() for date-based reportsFilter only rows with a date column (e.g., column C) that's greater than or equal to today:
=FILTER(A2:C100, C2:C100 >= TODAY())
Ideal for dashboards showing live or upcoming campaign data without manual updates.
FILTER + ARRAYFORMULA to apply logic across columnsFor more advanced logic, combine ARRAYFORMULA with FILTER. For example, filter rows where the sum of impressions (column B) and clicks (column C) is greater than 10,000:
=FILTER(A2:D100, ARRAYFORMULA(B2:B100 + C2:C100 > 10000))
Powerful when you're working with calculated thresholds across fields.
While the FILTER function in Google Sheets is extremely useful, there are situations where it can become time-consuming, especially if you regularly import large volumes of marketing data from various platforms. For example, having to write or adjust formulas every time your data updates can slow down your workflow.
Dataslayer is a Google Sheets add-on for marketers that lets you import ad, analytics, and campaign data from platforms like Google Ads, Facebook Ads, and GA4. With Dataslayer, you can skip manual filtering altogether. Just set your source, destination, and filters once, and your data will update automatically across sheets.
https://youtu.be/1CnwhauHckE?feature=shared
Country = Spain (only import data from Spanish campaigns)Conversions > 5 (exclude low-performing results)CPC < 2 (focus on cost-effective ads)This is ideal for teams or agencies managing complex, multi-account reports. Want to skip formulas and automate all your reports directly?
Try Dataslayer for Google Sheets.
To get the most out of the FILTER function in Google Sheets, it's important to follow a few best practices that will make your work more efficient and your spreadsheets easier to manage:
FILTER with dropdowns (Data Validation) for interactive dashboardsIf you're diving deeper into spreadsheet automation and marketing reporting, check out these additional guides from our blog:
The FILTER function in Google Sheets is one of the most versatile tools for marketers who want to streamline analysis, clean data quickly, and build smart dashboards.
Whether you're filtering Facebook campaigns by region or narrowing down high-performing keywords from your Dataslayer import, mastering this function will save you hours every week.
And if you want to go even faster, use Dataslayer to pre-filter your data before it even reaches your spreadsheet.
It extracts rows that match given criteria, like campaigns with low CPC or high conversions.
The FILTER function is simpler and easier to use for quick row-level filtering based on one or more conditions. QUERY, on the other hand, uses SQL-like syntax and offers more flexibility, such as grouping, pivoting, or selecting specific columns.Use FILTER when you need speed and simplicity. Use QUERY when you need advanced operations or custom output formatting.
Yes! You can use FILTER to pull data from another tab in the same spreadsheet. For example:=FILTER(OtherSheet!A2:C100, OtherSheet!B2:B100 = "Facebook")If the data is in a different file, combine FILTER with IMPORTRANGE to make it work across spreadsheets.
To filter rows dynamically, you can combine FILTER with cell references. For example, use a dropdown in cell F1 and update the formula like this:=FILTER(A2:C100, B2:B100 = F1)This way, the filtered result will change automatically based on the selected value, ideal for interactive dashboards.
Use the ISBLANK function inside your FILTER to skip empty rows. For example:=FILTER(A2:C100, NOT(ISBLANK(B2:B100)))This is especially useful when dealing with live marketing data or automated inputs that may include empty fields.
This usually means no data matches your condition. It’s not an error in the formula itself. To handle it gracefully, use IFERROR:=IFERROR(FILTER(A2:C100, B2:B100 > 50), "No results found")
Yes, use the * symbol for AND logic and + for OR logic. For example, to filter by channel and status:=FILTER(A2:C100, (B2:B100 = "Google Ads") * (C2:C100 = "Active"))
