Dynamic Array Functions FILTER and UNIQUE not working in Monthly Enterprise Channel

Question

Hello

I have an office 365 subscription, Monthly Enterprise Channel and I am trying to do some dynamic filtering and, according to Copilot my Filter and Unique functions are not actually present even though they do show up as formula options. I am up to date.

Thoughts?

Thank you

Ian

---

Moved from: Microsoft 365 and Office | Excel | For home | Windows

Answer 1

Hello @Ian Smith,

Thank you for reaching out to the Microsoft Q&A forum.

Since you’re on Microsoft 365 Monthly Enterprise Channel, FILTER and UNIQUE should definitely be available. 

To help narrow this down, could you please confirm a few things:

• Are you working in a .xlsx file (not .xls compatibility mode)?
• What is the exact Version and Build number shown under File > Account > About Excel?
• If you manually type =FILTER(A1:A10, A1:A10>0) and press Enter, what error message do you get (if any)?
• Do you see Excel automatically inserting an @ symbol in front of the formula?
• Also, are you using Excel desktop or Excel for the web?

Once we have this information, we can better determine whether this is a channel/version issue or something related to the workbook itself. 

Looking forward to your update.

Answer 2

FILTER and UNIQUE are dynamic array functions. In Excel versions that support dynamic arrays, these functions:

• Return an array of results that automatically spill into neighboring cells.
• Are available in Microsoft 365 subscriptions, including Office 2021, when dynamic arrays are enabled.

Key points relevant to the scenario:

1. Function availability vs. behavior
   • In dynamic array–aware Excel, FILTER and UNIQUE are fully supported and will spill results automatically from a single formula cell.
   • In older, non–dynamic-aware Excel, dynamic array formulas are not supported; formulas that could return multiple values are treated as legacy array formulas and require Ctrl+Shift+Enter. However, dynamic array functions like FILTER and UNIQUE themselves are only available in dynamic array–enabled builds.
2. Spill behavior and errors
   • FILTER and UNIQUE both “spill” their results. Excel automatically creates the required output range when Enter is pressed, as long as the spill range is clear.
   • If the spill range is blocked (nonblank cells, merged cells, etc.), Excel returns a #SPILL! error. For general spill issues, see Dynamic arrays and spilled array behavior and how to correct a spill error.
3. Correct syntax reminders
   • FILTER: =FILTER(array, include, [if_empty])
     • array: range to filter.
     • include: Boolean array (True/False) with same height/width as array.
     • [if_empty]: optional value if no records match; otherwise a #CALC! error occurs because Excel does not support empty arrays.
   • UNIQUE: =UNIQUE(array, [by_col], [exactly_once])
     • Returns distinct rows/columns from the specified range.
4. Cross‑workbook limitations
   • Dynamic arrays (including FILTER and UNIQUE) have limited support between workbooks. Linked dynamic array formulas only work correctly when both workbooks are open. If the source workbook is closed, formulas can return #REF! errors.

If FILTER and UNIQUE appear in the function list but do not behave as dynamic arrays (no spill, unexpected errors), the most common causes are:

• The formula’s spill range is blocked, causing #SPILL! errors.
• The include argument in FILTER is invalid (not Boolean or contains errors), causing FILTER to return an error.
• The dataset returns no rows and [if_empty] is omitted, causing a #CALC! error.

Verifying that formulas are entered with the documented syntax and that the spill range is clear should confirm whether dynamic arrays are working correctly in the Monthly Enterprise Channel build.

---

References:

• FILTER function
• UNIQUE function
• Dynamic array formulas in non-dynamic aware Excel
• Excel functions that return ranges or arrays
• What's new in Office 2021
• #SPILL! error - Unrecognized/Fallback
• SPILL errors are returned when a formula returns multiple results or a PivotTable range is not blank, and Excel cannot return the results to the grid. For more details on these error types, see the following help topics: