Function Problems

How to Use Excel FILTER Formula to Exclude Existing Values

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to create a dynamic spilled array from one column while strictly excluding any values that already exist in a secondary control column.

Product: Excel
Device & OS: not provided
Scenario: Filtering a dataset to extract remaining items by comparing two separate arrays and excluding matching records.
Observed behavior: Generating a dynamic array output that successfully omits the specified existing values automatically without manual deletion.

Before you start

Ensure you are using a modern spreadsheet application that supports Dynamic Arrays, such as Microsoft 365, Excel 2021, or the latest version of WPS Spreadsheet.

Solution 1Recommended

Use FILTER and XMATCH Formulas

This is the most direct method to extract items from a source column while omitting items found in a control column.

By combining the FILTER function with ISERROR and XMATCH, the spreadsheet checks every item in your source list. If an item is not found in the exclusion list, XMATCH returns an error, which ISERROR turns into TRUE, signaling the FILTER function to keep that value.

Select target cell

Click on the empty cell where you want the new filtered list to begin spilling.

Enter the exclusion formula

Type the formula =FILTER(B1:B7,ISERROR(XMATCH(B1:B7,D1:D2))) into the formula bar. Replace B1:B7 with your main data range and D1:D2 with your exclusion list.

Generate the array

Press Enter. The formula will automatically spill down the column, displaying only the values from Column B that do not appear in Column D.

Dynamic Spilling: The generated results will automatically update and spill into adjacent cells if you add or modify data in your source ranges.

Solution 2

Combine with UNIQUE to Remove Duplicates

If your source column contains duplicate entries and you want a clean list of unique remaining values, wrap the formula in a UNIQUE function.

Solution 3

Use LET and BYROW for Full Column References

When referencing entire columns (such as B:B or D:D), this advanced approach prevents thousands of blank cells from cluttering your calculation results.

Advanced Formulas in WPS Spreadsheet

Exclude Existing Values Easily with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions, allowing you to use FILTER, XMATCH, and UNIQUE formulas seamlessly. You can efficiently manage complex datasets, filter data, and perform dynamic comparisons without encountering compatibility issues.

1. Open file in WPS: Launch WPS Office and open your spreadsheet file.
2. Select starting cell: Click on the cell where the filtered array should begin.
3. Apply formula: Enter the =FILTER(B1:B7,ISERROR(XMATCH(B1:B7,D1:D2))) formula and press Enter to fetch your results instantly.

Fully compatible with Microsoft Excel dynamic array formulasProcess complex filtering dynamically without VBA or macrosFree, lightweight, and fast to loadCross-platform support for Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why does the FILTER formula show a #CALC! error?

The #CALC! error happens when the FILTER function evaluates to an empty array (meaning all items from the source were found in the exclusion list). You can handle this gracefully by adding an optional third argument for empty results: =FILTER(B1:B7,ISERROR(XMATCH(B1:B7,D1:D2)), "No records").

What is the difference between XMATCH and MATCH in this formula?

XMATCH is a newer, more robust function that defaults to an exact match comparison. The traditional MATCH function requires you to manually define a match-type argument (0 or FALSE) for exact matching. XMATCH keeps the formula cleaner and is highly optimized for dynamic arrays.

Can I use multiple columns as the exclusion criteria?

Yes, but you must first combine the multi-column exclusion range into a single list. You can wrap your exclusion range in the TOCOL function (e.g., XMATCH(B1:B7, TOCOL(D1:E10))) before applying the ISERROR and FILTER functions.

Will these dynamic array formulas work in older spreadsheet software?

No, dynamic array functions like FILTER, UNIQUE, and XMATCH are only available in Microsoft 365, Excel 2021, and newer software such as the latest WPS Office. Older versions would require complex legacy array formulas using INDEX and AGGREGATE to achieve similar outcomes.

Excel & Data Problems