Function Problems

How to Use Excel FILTER to Replace Google Sheets QUERY Formula

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to filter form-response data in Excel based on a specific match in a column, replicating the functionality of a Google Sheets QUERY formula.

Product: Excel
Device & OS: not provided
Scenario: Extracting and filtering form responses into a separate view based on a specific respondent's name.
Observed behavior: The user wants to achieve a dynamically filtered data view in Excel equivalent to what they would get using the Google Sheets QUERY function.

Before you start

Ensure you are using a version of Excel that supports dynamic array formulas, such as Excel for Microsoft 365 or Excel for the Web, as the FILTER function is not available in older standalone versions.

Solution 1Recommended

Use the FILTER Function in Excel for the Web

The FILTER function is the best direct equivalent to the Google Sheets QUERY formula for extracting rows that meet specific criteria.

In Excel, the FILTER function allows you to extract records based on one or more conditions. It behaves similarly to the QUERY function in Google Sheets by dynamically spilling the results into adjacent cells.

Select the target cell

Click on the empty cell in Excel where you want the top-left corner of your filtered data to appear. Make sure there is enough empty space below and to the right.

Input the FILTER formula

Type `=FILTER(Responses!A:O, Responses!I:I="Nicole Williams")` into the formula bar. This searches for "Nicole Williams" in column I and returns data from columns A through O.

Use a cell reference for dynamic filtering

To make the criteria easily adjustable, replace the name with a cell reference. For example, use `=FILTER(Responses!A:O, Responses!I:I=Z1)` where Z1 contains the name you want to find.

Execute the formula

Press the Enter key. The filtered results will automatically populate the necessary rows and columns.

Dynamic Array Spilling: Because FILTER is a dynamic array formula, you only need to enter it into one cell. Excel will automatically 'spill' the results into the adjacent cells, saving you the trouble of dragging the formula down.

Powerful Excel Alternative

Easily Filter Data using WPS Spreadsheet

WPS Office Spreadsheet provides robust support for dynamic array formulas like FILTER, allowing you to easily process form responses and query data just like you would in Excel or Google Sheets.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the form responses.
2. Locate an output area: Navigate to a blank worksheet or a clear area in your current sheet where the filtered data can be displayed.
3. Enter the FILTER function: Click the target cell and input `=FILTER(Responses!A:O, Responses!I:I="Nicole Williams")` into the formula bar.
4. Apply and review results: Press Enter to instantly extract and display all rows matching your specified criteria.

Free and lightweight Office suite for all your daily tasksFully compatible with Microsoft Excel (.xlsx) formatsNative support for advanced array functions including FILTERAvailable across Windows, Mac, Linux, iOS, and Android platforms

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #CALC! error when using the FILTER function?

The #CALC! error usually appears when the FILTER function finds no results matching your criteria. You can fix this by adding a third argument to the formula to handle empty results, such as `=FILTER(A:O, I:I="Name", "No results found")`.

Can I filter by multiple criteria in Excel like I do in Google Sheets QUERY?

Yes, you can use multiple criteria with the FILTER function by multiplying conditions together. For example, the formula `=FILTER(A:C, (A:A="Criteria1") * (B:B="Criteria2"))` will return only the rows that meet both conditions simultaneously.

Is the FILTER function available in all versions of Excel?

No, the FILTER function is only available in Microsoft 365, Excel 2021, and Excel for the Web. For older standalone versions like Excel 2016 or 2019, you must use alternative methods such as Advanced Filter or an INDEX/MATCH combination.

Why am I seeing a #SPILL! error after typing the FILTER formula?

A #SPILL! error occurs when there is not enough empty space for the dynamic array formula to output its results. Check the adjacent cells below and to the right of your formula and clear any existing data that might be blocking the spill range.

Excel & Data Problems