Function Problems

Why the Excel FILTER Function Is Missing in Office 2016 & How to Fix It

Phi Hung Vo Oct 10, 2026 869 views

Question details

Users are unable to locate or utilize the dynamic array FILTER function within Microsoft Excel 2016.

Why is the Excel FILTER Function Missing in Office 2016?

Product: Microsoft Excel 2016
Device & OS: not provided
Scenario: Attempting to dynamically filter a range of data using the =FILTER() formula in an older version of Excel.
Observed behavior: The FILTER function is completely unavailable. Entering it manually results in a #NAME? error, even though the FILTERXML function might be visible in the formula list.

Before you start

Verify your exact Microsoft Office version by navigating to File > Account in Excel, as your installed version dictates natively supported formulas.

Solution 1Recommended

Upgrade to a Newer Version of Microsoft Excel

The FILTER function relies on dynamic array architecture, which is only supported in newer versions of Microsoft Office. Upgrading is the only way to get native support.

Dynamic array functions were introduced after Office 2016 and Office 2019. To use the =FILTER formula, you must be operating on Microsoft 365, Excel 2021, or Excel 2019 with specific enterprise updates.

Check update availability

Open Excel, go to 'File', select 'Account', and click on 'Update Options' to ensure your current software is completely up to date.

Upgrade your Office package

If you are on an older standalone version like Office Professional Plus 2016, you will need to purchase a newer standalone version like Office 2021 or subscribe to Microsoft 365.

Upgrade to a Newer Version of Microsoft Excel

Information: Microsoft does not backport dynamic array features to Office 2016 via standard updates. Upgrading the software is strictly required for this formula.

Solution 2

Use the Advanced Filter Feature as an Alternative

If you cannot upgrade from Excel 2016, you can use the built-in Advanced Filter tool to extract specific data without relying on dynamic array formulas.

Free Microsoft Office alternative

Experience Powerful Data Processing with WPS Office

If your current version of Microsoft Office lacks the tools you need and you want to avoid expensive subscriptions, try WPS Office. It is a completely free, highly compatible alternative that seamlessly handles your existing Excel spreadsheets while providing a robust suite of data analysis features.

1. Download the software: Visit the official WPS Office website and download the free installer for your operating system.
2. Install and launch: Run the installer and follow the quick setup wizard. Once finished, launch WPS Spreadsheets.
3. Open existing Excel files: Click 'Open' and select your existing .xlsx files. WPS Office will load them with complete formatting and data integrity.

Completely free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Familiar ribbon interface ensures zero learning curveFast performance with rich formula and data filtering support

microsoft office alternative - wps office

Frequently Asked Questions

Does having the FILTERXML function mean I should have the FILTER function?

No. FILTERXML is a completely different function used to extract specific data from XML content. Its presence in Excel 2016 does not indicate that your version supports the dynamic array FILTER function.

Can I install an add-in to get the FILTER function in Excel 2016?

No, you cannot. The FILTER function requires a fundamental shift in how Excel calculates arrays (dynamic array architecture). This architecture cannot be patched into Excel 2016 via third-party add-ins or standard updates.

What does a #NAME? error mean when I type =FILTER?

When you type =FILTER in an unsupported version like Excel 2016, a #NAME? error appears because the software does not recognize 'FILTER' as a valid built-in formula name.

Excel & Data Problems