Fix Missing Excel FILTER and UNIQUE Functions in Microsoft 365
Question details
The user is unable to find or use dynamic-array functions like FILTER and UNIQUE in Microsoft 365 Excel.
- Product
- Microsoft Excel (Microsoft 365)
- Device & OS
- Mac
- Scenario
- Attempting to use advanced dynamic array functions for data analysis but finding them unavailable or returning errors.
- Observed behavior
- The FILTER and UNIQUE functions are missing from the function library and fail to calculate, despite using a Microsoft 365 subscription on Mac.
Ensure you have an active Microsoft 365 subscription or Office 2021, as dynamic array functions are not supported in older standalone versions like Office 2016 or 2019.
Clear the Excel Container Data
Resetting the Excel application container can resolve issues where damaged preference files cause functions to disappear.
On Mac, application preferences and cache files are stored in a dedicated container. Removing this container forces Excel to rebuild its default settings, which often restores missing Office 365 features.
Restart your Mac and hold down the Shift key as it boots to enter Safe Mode. This prevents background apps from interfering.
Open Finder, press 'Cmd + Shift + G' on your keyboard, type '~/Library/Containers/' into the prompt, and press Return.
Locate the folder named 'com.microsoft.Excel'. Drag this folder to your Desktop to keep it as a temporary backup.
Reboot your Mac normally, launch Microsoft Excel, and check if the FILTER and UNIQUE functions are now available.
Update Microsoft Excel to the Latest Build
Installing the most recent software updates ensures your Excel version fully supports dynamic arrays.
Test with a New Mac User Account
Creating a new user profile helps determine if the missing functions are caused by corrupted user-specific settings.
Experience Flawless Data Analysis with WPS Office
If troubleshooting Microsoft Excel is slowing down your workflow, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment where advanced functions work seamlessly without the hassle of damaged containers or profile bugs.
- 1. Download and Install: Get the free WPS Office installer for Mac from the official website and run the setup.
- 2. Open Your Spreadsheets: Double-click your existing .xlsx files to open them instantly in WPS Spreadsheet.
- 3. Use Advanced Functions: Start using advanced formulas immediately to filter, sort, and analyze your data seamlessly.
Frequently Asked Questions
Why does Excel say my FILTER function is invalid?
You may be using an older perpetual license of Excel (like Office 2016 or 2019) that does not support dynamic arrays, or your Microsoft 365 application preferences on Mac have become corrupted and need to be reset.
Do I need a Microsoft 365 subscription to use the UNIQUE function?
Yes, dynamic array functions such as UNIQUE, FILTER, and SORT are exclusively available to Microsoft 365 subscribers and users with Office 2021 or newer. They are not available in Office 2019 or older versions.
What should I do if removing the Excel container doesn't work?
If clearing the 'com.microsoft.Excel' container doesn't restore your dynamic array functions, try fully reinstalling the Microsoft Excel application or test it on a new Mac user profile to bypass system-level corruption.
Are dynamic arrays fully supported in Excel for Mac?
Yes, dynamic array functions are fully supported in Microsoft 365 for Mac. If they are missing, the issue is typically related to an outdated app build, subscription activation glitches, or damaged local files.
