Excel UNIQUE and FILTER #CALC! Error: Causes and Fixes

Errors & fixes · Updated November 20, 2026

A #CALC! error in an Excel formula that combines UNIQUE and FILTER most often means FILTER found no rows to return. With values in A2:A100 and regions in B2:B100, use =UNIQUE(FILTER(A2:A100,B2:B100="East","No matching rows")) to show distinct values for East and return a clear message if there are no matches.

What the error means

UNIQUE and FILTER are dynamic array functions: instead of returning one value, they can return a result that spills into neighboring cells. FILTER selects rows or values that meet a condition, and UNIQUE removes repeated values from the filtered result. When FILTER finds nothing and its optional third argument is missing, Excel returns #CALC! because it has no result to provide.

That is the first cause to check, but not the only possibility. A calculation error could come from another function or reference inside the formula, or from an unusual array operation. The exact formula and the source data matter. A visible #CALC! does not by itself prove that UNIQUE is faulty; in a nested formula, an error from FILTER can simply pass through UNIQUE.

These functions are available in Microsoft 365 and Excel 2021 or later, including the relevant current versions of Excel for Mac and Excel for the web. If a workbook is opened in an older Excel version without dynamic array support, it may show a different compatibility problem rather than calculate the formula as intended.

How to find which function is failing

Temporarily test the inner FILTER on its own. With values in A2:A100 and regions in B2:B100, enter =FILTER(A2:A100,B2:B100="East"). If it returns #CALC!, the East condition probably has no matches and the missing fallback is the issue. If it returns the expected values, inspect how UNIQUE is being used and whether the source ranges contain other errors.

You can also step through the calculation in Excel for Microsoft 365 on Windows:

  1. Select the cell containing the formula.
  2. Go to Formulas > Evaluate Formula.
  3. Select Evaluate repeatedly and watch what each part returns.

When checking an array formula, compare the ranges carefully: the values array and the include array must have compatible dimensions. For instance, A2:A100 and B2:B100 each contain 99 rows. If one range ends at row 100 and the other at row 99, the formula can fail because the include test cannot be aligned with the values. Also look for errors already present in the source cells; a bad source value can affect a larger dynamic-array result.

Fix the no-match case with FILTER’s third argument

FILTER has this syntax: FILTER(array,include,[if_empty]). The third argument is optional, but it is useful when a legitimate search can return zero records. If omitted, no matches produce #CALC!; if supplied, FILTER returns the value you specify instead.

With names in A2:A100 and departments in B2:B100, this formula returns unique names from the Sales department, or a message if no row qualifies: =UNIQUE(FILTER(A2:A100,B2:B100="Sales","No matching rows")). If Sales appears for Alex, Bea, and Alex again, the result is Alex and Bea, each once. If Sales does not appear, the result is the single text value “No matching rows.”

Choose a fallback that makes sense for the report. A message is easy to understand, while an empty string can make a dashboard look cleaner. With names in A2:A100 and departments in B2:B100, use =UNIQUE(FILTER(A2:A100,B2:B100="Sales","")) if you prefer a visually blank result when there are no matches. Keep in mind that a blank-looking result is not the same as an error, and it can be less obvious to another person why no names appear.

Check blanks, criteria, and duplicate values

A formula can return a result without an error and still produce a list that looks wrong. For example, FILTER may include blank cells if the condition allows their rows through, and UNIQUE can treat a blank as one of the distinct values. If the output starts with an apparently empty item, determine whether the source range contains blanks before assuming the function is broken.

With names in A2:A100 and departments in B2:B100, this formula keeps nonblank names from Sales before removing duplicates: =UNIQUE(FILTER(A2:A100,(B2:B100="Sales")*(A2:A100<>""),"No matching names")). The two tests produce TRUE/FALSE arrays; multiplying them requires both conditions to be TRUE. The ranges must cover the same rows. If the data contains spaces that look blank, a cell is not necessarily empty, so clean or normalize the source data if those entries should be excluded.

Check the criterion itself, too. “East” will not match a cell that contains “East ” with an extra trailing space, and a criterion that points to the wrong cell can validly find nothing. Text comparisons in ordinary Excel criteria are generally not case-sensitive, so changing only capitalization usually does not explain an empty result. Inspect the actual cell contents and the criterion reference rather than rewriting the whole formula first.

Distinguish #CALC! from other Excel errors

Different errors point to different problems. #CALC! in this setting commonly indicates an empty result from FILTER, while #SPILL! means Excel cannot place a dynamic-array result into the required cells. A spill range may be blocked by existing content, merged cells, or the edge of the worksheet. Clear the obstructing cells or move the formula; adding an if_empty argument does not fix a blocked spill.

#VALUE! or another error may signal an incompatible argument, mismatched array dimensions, or an error in the data used by the formula. A formula that begins with #NAME? may mean a function name is not recognized, for example because the workbook is being opened in an Excel version that does not support UNIQUE or FILTER. These errors should not be treated as interchangeable: first identify the actual error displayed in the cell.

Also distinguish “no rows matched” from “the matching rows contain blank values.” In the first case, FILTER has no qualifying records. In the second, records qualify, but their returned values may look empty. Testing FILTER without UNIQUE, and then checking a few source rows, helps tell these cases apart.

Use IFERROR carefully

Wrapping the whole expression in IFERROR can hide the visible error, but it may also hide a genuine problem. With values in A2:A100 and departments in B2:B100, =IFERROR(UNIQUE(FILTER(A2:A100,B2:B100="Sales")),"No matching rows") returns the message for any error in the combined calculation, not only the expected no-match case. That could make a broken reference or a source-data error look like an ordinary empty search.

For this specific situation, FILTER’s third argument is usually clearer: it handles the known no-match outcome while allowing unrelated errors to remain visible for investigation. Use IFERROR when you deliberately want to handle a broader class of errors and have considered what they might conceal. Avoid using it just to make every formula cell look tidy.

Dynamic arrays, output space, and workbook setup

UNIQUE and FILTER return arrays that spill automatically in current Excel. Enter the formula in one cell and leave the expected output area available; do not copy the formula down as if it were a conventional one-result lookup. The top-left cell contains the formula, and the remaining results appear in adjacent cells. You can refer to a spilled range with the spill reference operator, such as a cell reference followed by #, if a later formula needs the whole output.

When a formula works on one computer but not another, check the Excel version and whether the workbook is being opened in a compatible application. Excel for Microsoft 365 on Windows is the assumed setup here. The functions are also supported in current Microsoft 365 editions for Mac and Excel for the web; the ribbon layout can differ, but the formulas and the no-match behavior are the same. Older perpetual versions may not include these functions.

Formula argument separators can depend on regional settings. The examples use commas, as in the standard English-language Excel syntax requested here. If Excel rejects an otherwise sound formula in a locale configured to use semicolons, follow the separator convention used by that installation. This is a syntax-setting issue, not a UNIQUE-specific correction to the logic.

Frequently Asked Questions

Why does UNIQUE(FILTER()) return #CALC!?
Most often, FILTER found no rows and its optional if_empty argument was omitted. Add a third argument to FILTER, such as a clear message, and verify that the condition actually matches the data.

Does UNIQUE cause the #CALC! error?
It can be part of a formula that displays the error, but in a common UNIQUE(FILTER()) formula the empty result originates in FILTER. Test FILTER by itself to locate the source before changing UNIQUE.

How do I return a blank instead of #CALC!?
Supply an empty string as FILTER’s third argument. The result will look blank when no rows match, although a message can be easier for a report reader to interpret.

Why do I see #SPILL! instead?
Excel has a result to return but cannot place it in the cells the dynamic array needs. Clear cells in the spill area, unmerge interfering cells, or move the formula to an open area.

Can I use UNIQUE and FILTER in older Excel versions?
These functions require Excel 2021 or Microsoft 365. In an older version, use a compatible alternative such as a PivotTable or an Advanced Filter workflow, or open the workbook in a supported Excel edition.

Practical troubleshooting checklist

When the error persists, work through the likely causes in order rather than replacing the formula at random:

  • Confirm the displayed error is #CALC!, not #SPILL! or another error.
  • Test FILTER by itself and confirm whether the condition returns any rows.
  • Add an appropriate if_empty value if zero matches is a normal possibility.
  • Check that the include and return ranges cover the same rows and columns.
  • Inspect criteria, blank cells, stray spaces, and errors in the source data.
  • If the formula is valid but the function is unrecognized, check Excel version support.

The key distinction is whether the search legitimately has no matching records or whether the formula is malformed or the data is inconsistent. FILTER’s fallback resolves the first case; range checks, criteria checks, and version checks address the others. Once that distinction is clear, a UNIQUE/FILTER error is usually much easier to diagnose.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.