The Excel empty array error is usually #CALC! with the message “Empty Array.” It means a dynamic array formula—most often FILTER—found no results to return; add an if_empty result, or adjust the criteria so at least one item matches.
What the Empty Array Error Means
In current Microsoft Excel for Microsoft 365, formulas such as FILTER can return arrays that spill into nearby cells. A spill can contain multiple rows or columns, but Excel does not support a result with zero rows. When a formula asks for an array and the result contains no items, Excel may display #CALC! and identify the problem as an empty array.
This is different from a formula returning a blank-looking value. A cell containing an empty string, often displayed as blank, still contains a formula result. It is also different from #SPILL!, which usually means Excel cannot place an otherwise valid array in the intended cells—for example, because cells in the spill range are occupied. The exact error matters: “Empty Array” points to no results, while #SPILL! points to an obstruction or another spill-range issue.
The issue is generally a result of the data or criteria, not workbook corruption. A formula can work on one selection and show the error on another simply because the second selection has no matching records.
Why FILTER Produces It
FILTER returns the rows or columns that satisfy an include condition. Its syntax is FILTER(array, include, [if_empty]). The third argument is optional. If it is omitted and no values meet the condition, Excel has no rows to return and reports #CALC!.
For example, assume A2:C6 contains five records, with names in column A, amounts in column B, and a category in column C. F1 contains the category to look up. If there are no records in C2:C6 equal to F1, this formula can return the empty-array error:
With records in A2:C6, categories in C2:C6, and the requested category in F1, use:
=FILTER(A2:C6,C2:C6=F1)
If F1 is “Hardware” but none of the five rows has that category, the result is #CALC!. If one or more rows match, the matching rows spill into the worksheet instead. The formula itself is valid in both cases; the difference is whether the data contains a match.
This pattern can also appear in a report, lookup area, or dashboard whose criteria are changed by a user. It may not show up until a dropdown selection, date range, or status filter produces no matches.
Fix It with FILTER’s if_empty Argument
The most direct fix is to provide a result for the no-match case. In the same example, the third argument can display a clear message instead of an error:
With records in A2:C6, categories in C2:C6, and the requested category in F1, use:
=FILTER(A2:C6,C2:C6=F1,"No matching records")
If no rows match, Excel returns the text “No matching records” in the result area. If rows do match, it returns the matching rows as usual. Pick a message that makes sense to the person using the sheet; alternatives include “No results” or “Check the selected category.”
The fallback is one value, not a table of placeholder rows. When no matches exist, the message occupies the result’s starting cell. This is often appropriate for a report, but may not suit a later formula that expects every result to be a row of data. In that case, decide how downstream calculations should handle the no-match situation rather than assuming the fallback is a genuine empty table.
Show a Blank Instead of an Error
You can use an empty string as the fallback when a visible message would clutter a report:
With records in A2:C6, categories in C2:C6, and the requested category in F1, use:
=FILTER(A2:C6,C2:C6=F1,"")
When nothing matches, the formula returns an empty string, which looks blank in the cell. That can be useful in presentation-oriented sheets, but it is not the same as returning no cell at all. The formula remains in the starting cell, and functions that inspect the result may treat the empty string differently from a truly unused cell. For example, a blank-looking result is not a reliable way to create a zero-row array for another formula.
Use an explanatory message when users need to know why a result area is empty. Use a blank only when the surrounding labels or instructions make that behavior clear. If users might interpret the blank as a broken formula or missing data, a short message is safer.
Check the Criteria Before Changing the Formula
If you expected matches, inspect the source values and the criterion before settling for a fallback. Common causes include a misspelled selection, trailing spaces, inconsistent labels, and dates or numbers stored in a different form than expected. A cell that looks like “North” may contain an extra space, while a date displayed in a familiar format may contain a time value that affects an exact comparison.
Check a few relevant cells directly, and confirm that the range in the formula covers the rows containing the data. Also check whether the criterion cell is blank. A blank criterion can produce unexpected matches or no matches depending on the source values and formula condition. For a dropdown-driven report, select a category that is visibly present in the source table and see whether the results appear.
For a quick count of matching categories, enter a separate diagnostic formula. With categories in C2:C6 and the criterion in F1, use:
=COUNTIF(C2:C6,F1)
This returns the number of cells Excel considers a match. If the result is 0, the issue is consistent with the empty-array message: the current criterion has no match under that comparison. If the count is positive but FILTER still returns no rows, check that the ranges in the two formulas refer to the same data and that the include condition has not been altered.
Understand What a Blank or Error Is Hiding
A no-match result can be meaningful information. Replacing every error with a blank may make a dashboard look tidy, but it can also hide that a filter is too restrictive or that new data was entered incorrectly. Consider whether the user should see a message, a numeric zero, or an intentionally blank display; those are different business meanings.
Do not automatically wrap the formula in IFERROR just to remove #CALC!. IFERROR can also conceal other problems, such as an invalid reference or an error in the source data. When the expected issue is specifically “no matching rows,” the if_empty argument of FILTER states that intention more clearly and leaves unrelated errors visible.
If the result is used by calculations, decide what those calculations should do when there are no records. For instance, a total of matching sales might reasonably be zero, while a list of matching customers might be better represented by a message. A display choice should not accidentally change the meaning or type of data a later formula receives.
How It Differs from Other Excel Errors
Excel’s error types describe different failure conditions, so replacing one with another is not a universal fix. #CALC! with “Empty Array” indicates that a calculation produced an unsupported empty result. #SPILL! indicates that a dynamic array cannot spill into its intended cells; clearing the obstructing cells may resolve that problem, but it will not create matches for a filter with none.
#VALUE! can indicate an inappropriate value or argument in a calculation, while #REF! indicates an invalid reference. Those errors require their own diagnosis. If Excel displays #CALC! with a different explanatory message, read that message rather than assuming every #CALC! is caused by FILTER finding no rows.
Dynamic array formulas are available in Microsoft 365 and supported newer Excel releases. If a workbook is opened in an older Excel version that does not support a particular dynamic array function, the behavior can differ; the empty-array diagnosis is most relevant when the formula is running in a version that supports the function being used.
Practical Troubleshooting Steps
- Select the cell showing the error and inspect its formula. Identify whether it uses
FILTERor another formula that can produce an array. - Check the condition and the cells it compares. Verify the requested value is present and that the formula includes the intended source rows.
- For
FILTER, add a meaningful third argument if a no-match result is expected and acceptable. - If you expected results, test the criterion against the source data and check for spaces, inconsistent formats, or an incorrect range.
- Keep unrelated errors visible during diagnosis instead of masking everything with
IFERROR.
These checks help separate a normal no-match outcome from a formula or data-entry problem. Once the intended behavior is clear, choose a fallback that communicates that behavior rather than merely suppressing the warning.
Frequently Asked Questions
What does #CALC! Empty Array mean in Excel?
It means a formula attempted to return an array with no items. A common cause is FILTER finding no rows that meet its condition while its optional if_empty argument is omitted.
How do I stop FILTER from showing #CALC!?
Add a third argument to FILTER, such as a short no-results message or an empty string. This tells Excel what to return when the include condition finds no matching values.
Is a blank from FILTER a truly empty cell?
No. If the formula uses an empty string as its fallback, the starting cell contains a formula whose result looks blank. It is not the same as an unused cell or a zero-row array.
Why does my FILTER formula work for some choices but not others?
Some criteria may match source rows while others do not. Check the selected value, the source range, and possible differences such as extra spaces or inconsistent data formats.
Is Empty Array the same as #SPILL!?
No. Empty Array refers to a result with no items, which Excel cannot return as a zero-row array. #SPILL! generally means Excel cannot place the array result in the worksheet, often because its spill area is blocked.
Should I use IFERROR to hide the empty-array error?
Usually, the FILTER if_empty argument is clearer because it handles the expected no-match case specifically. IFERROR may also hide unrelated errors that deserve attention.
Related
- Excel Data Validation List Without a Cell Reference?
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel #VALUE! Error: Causes and How to Avoid ItTo avoid Excel’s #VALUE! error, make sure each formula receives the kind of input it expects: numbers for…
- VLOOKUP Not Working? Common Excel Causes and FixesVLOOKUP usually fails because the lookup value and the first column of the search range do not match…
- Flash Fill in Excel: How to Use It and Fix ProblemsTo use Flash Fill in Excel for Microsoft 365 on Windows, type the result you want beside your…
- Why Is Find Not Working in Excel? FixesFind in Excel usually appears not to work because the search is limited to the wrong sheet or…
- Excel Formula Fill Down Not Working? How to Fix ItIf an Excel formula will not fill down, first check whether Excel is treating it as a formula,…
- Excel Page Break Not Going Away? Fix Persistent LinesIf an Excel page break will not go away, first switch to View > Normal; if the line…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.