Function Problems

How to Use Excel FILTER to Skip Blank Cells and Return Matching Names

Kushani Nimanthika Oct 22, 2026 868 views

Question details

The user wants to extract names from a master list based on a specific marker (like 'x') in another column, ensuring that blank cells are skipped and no formula errors occur.

How to Use Excel FILTER to Skip Blank Cells and Return Matching Names

Product: Excel
Device & OS: not provided
Scenario: Extracting a subset of data (such as a guest list for a wedding shower) from a master spreadsheet using specific text criteria while filtering out any blank rows.
Observed behavior: Needs a reliable dynamic formula to filter data accurately without returning blank entries or triggering VALUE and SPILL errors.

Before you start

Ensure your spreadsheet software supports Dynamic Array functions like FILTER (available in Microsoft 365, Excel 2021, and WPS Office). Check that there is enough empty space below the formula cell to prevent #SPILL! errors.

Solution 1Recommended

Use the FILTER Function with Multiple Criteria

Combine conditions using the asterisk (*) operator to filter for a specific text marker while simultaneously excluding blank cells.

The FILTER function dynamically extracts records that meet one or more conditions. By multiplying two conditions together inside the formula, you apply an 'AND' logic: the row must have the target marker AND the name cell must not be blank.

Select the destination cell

Click on the cell where you want the filtered list of names to begin. Ensure the cells below it are completely empty.

Enter the base FILTER formula

Type =FILTER('Master List'!B21:B580, 'Master List'!R21:R580="x", "No matches") to return all names that have an 'x' in column R.

Add the condition to skip blanks

Modify the formula to include a check for non-blank cells: =FILTER('Master List'!B21:B580, ('Master List'!R21:R580="x")*('Master List'!B21:B580<>""), "No matches"). The <>"" syntax ensures empty cells in column B are ignored.

Press Enter to apply

Press Enter. The formula will automatically calculate and spill the matching, non-blank names down the column.

Use the FILTER Function with Multiple Criteria

Preventing SPILL Errors: If you see a #SPILL! error, clear any existing data, formatting, or spaces in the cells directly below your formula to allow the results to populate properly.

Advanced Spreadsheet Data Filtering

Filter Complex Data Easily with WPS Spreadsheet

WPS Office Spreadsheet fully supports dynamic array functions like FILTER, allowing you to extract, analyze, and manage lists such as event registries without complicated workarounds.

1. Open your list in WPS Spreadsheet: Launch WPS Office and open your master guest list or data workbook.
2. Apply the FILTER formula: Select an empty cell and input =FILTER(B21:B580, (R21:R580="x")*(B21:B580<>""), "No matches").
3. Format and manage: Use WPS Spreadsheet's conditional formatting or table tools to seamlessly manage and present your extracted data.

Full compatibility with Microsoft Excel functions, including dynamic arrays like FILTER.Lightweight and runs smoothly even with massive master lists and datasets.Free built-in templates for event planning, guest lists, and project data management.

microsoft office alternative - wps office

Frequently Asked Questions

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

A #SPILL! error occurs when the destination range for the filtered results is blocked by other data, hidden characters, or merged cells. Clear the cells below your formula to fix this issue.

Can I filter based on more than two conditions at the same time?

Yes. You can add more conditions by wrapping each in parentheses and multiplying them together, like (Condition1)*(Condition2)*(Condition3) within the include argument of the FILTER function.

What does the asterisk (*) do in the FILTER formula?

In array formulas, the asterisk (*) acts as an 'AND' operator. It multiplies the TRUE (1) and FALSE (0) results of your conditions. Only rows where all conditions evaluate to TRUE (1 * 1 = 1) will be included in the final filter.

What if no names match my criteria?

The third argument in the FILTER function allows you to specify a fallback value. In the formula =FILTER(A:A, B:B="x", "No matches"), the text 'No matches' will display in the cell if no rows meet your given criteria.

Excel & Data Problems