Fix SORT, FILTER, and XLOOKUP Formulas in Excel for Mac
Question details
The user needs to fix Excel formulas that contain compatibility prefixes or an unexpected ANCHORARRAY reference.

- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- Opening or editing a workbook that uses SORT, FILTER, XLOOKUP, or dynamic arrays.
- Observed behavior
- Formulas contain prefixes such as _xlfn or _xlws, or include ANCHORARRAY, potentially because of compatibility or formula translation issues.
Open the workbook in Excel for Mac and select an affected cell to inspect its formula in the formula bar. Check whether the workbook was created or edited in another spreadsheet application.
Update Excel and check workbook compatibility
Use this first when a workbook contains newer functions that Excel may not recognize.
In Excel, select Excel > About Microsoft Excel and check that your version supports SORT, FILTER, and XLOOKUP.
Select Help > Check for Updates, then use Microsoft AutoUpdate to install available Office updates.
Close and reopen the workbook in Excel, then select an affected cell and check whether its formula calculates correctly.

Rebuild the formula with standard Excel syntax
Use this when a translated formula still contains ANCHORARRAY or compatibility prefixes after updating Excel.
Work with spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Microsoft Office format support makes it easier to move existing files, although it does not repair Excel-specific formula translation issues.

Frequently Asked Questions
What does _xlfn mean in an Excel formula?
It is a compatibility prefix that can appear when Excel does not recognize a function or when a formula has been translated incorrectly. Check your Excel version under Excel > About Microsoft Excel and install available updates.
Should I type ANCHORARRAY into an Excel formula?
ANCHORARRAY is not normally entered as a worksheet function in current Excel. If it appears in a translated formula, inspect the original formula and rebuild the expression using standard Excel syntax.
Why does my SORT or FILTER formula show #SPILL!?
The results may need more empty cells. Select the formula cell, inspect the intended spill range, and clear any values or other obstructions in that range.
Can another spreadsheet application change my Excel formulas?
Formula translation or support can differ between applications. If a workbook was edited elsewhere, open it in a current version of Excel and compare affected formulas with the original workbook or the intended calculation.




