About This Article
This article was created using a generative AI-assisted automation workflow. We reviewed Microsoft's official documentation for the FILTER function and #CALC!, and organized a method to test the difference between 0 and 1 matching results using a mock order table.Verification Status: 📘 Official specifications verified; reproduction on an actual Excel instance has not been performed.
What happens when Excel's FILTER function returns 0 results? Testing the difference between #CALC! and empty strings
Even though you created a list to "display only unprocessed orders" using Excel's FILTER function, it displays#CALC!precisely on days when all tasks are finished. This does not necessarily mean the formula is broken.When there are 0 matching rows and the third argument is omitted, this occurs because FILTER cannot return an empty array. Specifying""as the third argument can make the output appear blank, but it does not mean a table with 0 rows can be created.
While this minor difference may not matter if the results are only meant for human reading, it becomes problematic when counting results with another formula or passing them to another sheet. Here, without using actual customer data, we compare "0 results", "1 result", and "alternative display values" using four mock orders. The display examples in this article are expected results based on official specifications and are not execution results generated by the author on an actual Excel instance.
- Target Environment and Safe Preparation
- First, try without the third argument
- What changes when you add the third argument
- Change only one place and compare 0 items versus 1 item
- Understanding with a Diagram: Branching of FILTER Results
- Delegate Count Judgment to COUNTIF
- How to Differentiate #CALC! and #SPILL!
- Design Decisions for Work Usage
- Summary
- Official documentation
Target Environment and Safe Preparation
| Item | Description |
|---|---|
| Target | Excel for Microsoft 365, Excel 2024, Excel 2021, and other versions supporting FILTER |
| Prerequisites | A new blank worksheet only |
| Experimental Data | Fictional order ID and stationery name |
| Scope of change | Only the cells in this experimental sheet |
| Verification status | Official Microsoft specification verified, not yet executed on physical Excel |
Enter the following table in cells A1 through C5. A1:C1 are headers, and A2:C5 are four orders. There is no need to directly edit existing work files. Since dynamic arrays in Excel spill results into adjacent cells, leave the area around E2 through G10 empty.
| Order ID | Item | Status |
|---|---|---|
| A001 | Notebook | Completed |
| A002 | Pen | Completed |
| A003 | File | Completed |
| A004 | Sticky notes | Completed |
The key point to note here is that all statuses are "Completed" and there are not any "Pending" items. Since a human can verify beforehand that there are zero results, it becomes easier to isolate what a subsequently displayed error means. You can close the verification sheet without saving it, and it will not affect the original operational data.
First, try without the third argument
Enter the following formula in E2. The first argument of FILTER is the range of the table you want to return, and the second argument is the condition to determine which rows to adopt. This is specified to return only rows where column C is "Pending", spanning from column A to column C collectively.
=FILTER(A2:C5,C2:C5="未処理")
The expected display is#CALC!. Because there are no rows matching the condition, not a single row is included in the resulting array. Microsoft states in the official documentation for FILTER that because empty arrays are not supported, omitting the third argument and resulting in an empty output triggers this error.
The important thing here is#CALC!not to jump to conclusions about the cause based solely on the text. This error also has other causes, such as nested arrays or how LAMBDA is used. The reason we can determine that zero items is the cause this time is because we can actually cross-reference the four rows of the input table with the condition "Pending". If you see the same display in an operational file, first check whether any rows matching the condition exist.
What changes when you add the third argument
Replace the formula in E2 with the following. The last argument added,"", is an empty string, meaning a string with a length of zero. The third argument of FILTER specifies the alternative value to return when the extraction result is empty.
=FILTER(A2:C5,C2:C5="未処理","")
The expected result is a display where E2 appears blank. It might seem resolved because the error is no longer visible, butan empty 3-column table has not been generated. An empty string is returned as a substitute when there are no matching rows. Microsoft's official FILTER function examples also demonstrate specifying an empty string in the third argument to return a value when there is no match.
If you overlook this difference, subsequent formulas designed to count "the number of rows in the spilled result" might end up counting a single cell containing the alternative value. Having nothing visible on the screen and having zero rows of data are two different things. There are cases where it is better not to leave user-friendly visuals and correct row count management to the same formula.
Change only one place and compare 0 items versus 1 item
Next, change only C3 from "Completed" to "Pending". Do not change the formula. Now only the row for order A002 satisfies the condition. The expected result is a single row and three columns spanning E2:G2, with "A002", "Pen", and "Pending" lined up horizontally. Instead of the empty string from earlier, the actual row extracted from the source data should be returned.
When you revert C3 to "Completed" after the comparison, there are zero matches again, and the empty string of the third argument is used. What we want to observe here isthat the shape of the return value changes depending on the input conditions even with the same formula. If there is one or more items, table rows are returned; if there are zero items, the specified alternative value is returned. The third argument is not a string that is always appended to the extraction results.
Furthermore, by setting the third argument to"対象なし", you can display an explanatory text in E2 only when there are 0 results. This is convenient for lists that staff check every morning, but if another formula processes the result as an order ID, you must note that the explanatory text will mix into the data. Designate whether to use the location exclusively for display or for programmatic aggregation.
Understanding with a Diagram: Branching of FILTER Results
The following diagram is not an internal Excel implementation, but rather an organization of result branching that can be read from the official specification. It illustrates the flow where conditions are evaluated from left to right, and the return value is determined by the presence or absence of matching rows and the specification of the third argument.
flowchart TD
A["FILTERで各行の条件を判定"] --> B{"一致する行がある?"}
B -->|ある| C["一致した行の配列を返す"]
B -->|ない| D{"第三引数を指定した?"}
D -->|はい| E["指定した代替値を返す"]
D -->|いいえ| F["空配列を返せずCALCエラー"]
Proceeding to the lower side of the diagram is the pitfall this time. The operation of adding a third argument to suppress error displays fundamentally changes the return value when there are no results. Considering how subsequent processing handles that value before deciding on an alternative value purely for visual convenience helps avoid aggregation errors.
Delegate Count Judgment to COUNTIF
Apart from the FILTER for display, enter the following formula in J2. This allows you to directly count how many "Unprocessed" items exist in the status column. In the original table, the expected result is 0, and it becomes 1 when only C3 is changed to "Unprocessed".
=COUNTIF(C2:C5,"未処理")
If J2 is 0, there are 0 unprocessed orders; if 1, there is 1. Have the FILTER in E2 return an empty string or "No target", and use the count in J2 for business decisions. This separation ensures that even if you change the wording shown to users, you do not need to change the formula for count judgment. For example, changing "No target" to "There is no unprocessed work today" does not change the meaning of J2.
However, just because COUNTIF is 0 does not mean the input data is completely correct. If there are extra spaces or different notations in the status column, they may not match the intended conditions. A mechanism that automatically judges 0 counts as "Processing completed" without inspecting input values is dangerous. In critical operations, it is appropriate to set up data validation for statuses and operate by checking exceptional values separately.
How to Differentiate #CALC! and #SPILL!
When FILTER results are not displayed, first check the error name.#CALC!occurs not only with empty arrays like this time, but also due to other calculation issues. On the other hand,#SPILL!indicates a spill range issue, such as another value existing in the range where results are meant to be spilled. If existing data is present to the right of E2 when one or more results are returned, the results may not be placable.
Just because a spill destination is filled in a business sheet does not mean you should unconditionally delete surrounding cells. First, test the same formula in a new empty sheet to determine whether the problem lies in condition evaluation or layout. Also, if the extraction range A2:C5 has 4 rows, but the condition range C2:C4 has 3 rows, the array sizes will not match. Specifying the third argument cannot resolve such structural formula errors.
Another boundary is references to other workbooks. Microsoft provides guidance on limitations regarding inter-workbook connections for dynamic arrays, such as when both workbooks are open. Updating with another workbook closed may result in#REF!, which is a separate issue from the alternative value for 0 results. The example this time is structured to be completed within the same sheet so it can be understood without increasing dependencies on external workbooks.
Design Decisions for Work Usage
For unprocessed lists checked every morning, specifying "No target" in the third argument of FILTER makes it easier for users to understand the situation. On the other hand, when performing email notifications or process branching based on counts, it is safer to judge independently using COUNTIF or similar, and not rely on programmatic inputs for display phrasing. Whether to treat 0 counts as normal or to check them as suspected input omissions must also be determined by business rules.
Rather than combining multiple conditions from the start, it is easier to understand by first checking 0 and 1 results with a single condition as done here, and then adding dates or assignees one condition at a time. If the count drops to 0 immediately after adding a new condition, you can isolate whether the source data is missing or the condition is too strict. Being able to explain the meaning of the results is more important than shortening the formula.
Summary
If the third argument of FILTER is omitted and zero records are extracted, Excel cannot return an empty array and will result in#CALC!. Specifying an empty string can make the display cleaner, but it does not generate a table with zero rows. Verifying the difference through an experiment by changing a single input, and separating the FILTER function used for display from the COUNTIF function used for count management, makes it easier to understand the intent in subsequent aggregations. The Excel display shown here is the expected result, and reproduction on a physical device has not been verified.
