'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel FILTER #NAME? and _xlfn._xlws.FILTER Errors
Question details
The user needs to fix formula errors where the FILTER function evaluates to #NAME? and displays an _xlfn._xlws.FILTER prefix in the formula bar.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening an Excel workbook containing the modern dynamic-array FILTER function on a computer with an older or incompatible version of Excel.
- Observed behavior
- Excel fails to recognize the FILTER function, replacing it with the _xlfn._xlws.FILTER prefix and outputting a #NAME? error in the affected cells.
Verify your current Microsoft Office version to confirm if dynamic array functions are supported, and create a backup copy of your workbook before running any software repairs.
Upgrade to Microsoft 365 or Excel 2021
The most definitive way to resolve this error is to open the workbook in a version of Excel that natively supports dynamic array functions.
The FILTER function is a dynamic array feature introduced in newer versions of Excel. Older standalone versions (like Excel 2016 or 2019) do not have the internal code to calculate these formulas, which is why the #NAME? error appears.
Open Excel, click on 'File' in the top-left corner, and select 'Account' at the bottom of the left pane. Review your software version under 'Product Information'.
If you are running Excel 2019 or older, you will need to purchase and install an upgrade to Microsoft 365 or a standalone version of Excel 2021 (or later).
After installing the newer version of Excel, open your file again. The _xlfn._xlws prefix will disappear, and the FILTER formula will evaluate properly.
Update and Repair Microsoft Office Installation
Use this solution if you are already subscribed to Microsoft 365 or own Excel 2021, but are still encountering the missing function error.
Use WPS Office to Seamlessly Handle Dynamic Array Functions
If upgrading a legacy Microsoft Office suite is too expensive or impractical, WPS Office provides a free, robust alternative. It perfectly supports advanced Excel functions like FILTER and modern dynamic arrays without generating #NAME? errors.
- 1. Install WPS Office: Download the free WPS Office installer from the official website and complete the quick installation process.
- 2. Launch WPS Spreadsheet: Open WPS Office and click on 'Spreadsheet' from the main dashboard to access the Excel-compatible editor.
- 3. Open your workbook: Click 'Open', select your .xlsx file containing the FILTER formula, and view your dynamic data without any #NAME? or _xlfn prefix errors.
Frequently Asked Questions
What does the _xlfn prefix mean in an Excel formula?
The _xlfn prefix is automatically added by Excel when a workbook contains a function that is not supported by the version currently running. It acts as a placeholder to preserve the original formula structure, even though it cannot calculate the result.
Can I use the FILTER function in Excel 2019 or 2016?
No. The FILTER function relies on the dynamic array calculation engine introduced in Microsoft 365 and Excel 2021. Older standalone versions like 2019, 2016, or 2013 lack this engine and will always return a #NAME? error.
Is there a workaround to filter data using formulas in older Excel versions?
Yes, you can achieve similar results in older versions using a complex combination of traditional functions like INDEX, SMALL, IF, and ROW entered as an array formula (Ctrl+Shift+Enter). However, this method is significantly slower and harder to maintain than the modern FILTER function.
Why do I see _xlws along with _xlfn in the formula bar?
The _xlws prefix is added internally by Microsoft Excel alongside _xlfn when the unsupported function is specific to modern worksheet calculations, often involving newer dynamic array functionalities or newly added web functions.
