Office for Mac Issues

Fix SORT, FILTER, and XLOOKUP Formulas in Excel for Mac

Muhammad TalhaMuhammad Talha Nov 18, 2026 870 views

Question details

The user needs to fix Excel formulas that contain compatibility prefixes or an unexpected ANCHORARRAY reference.

Fix SORT, FILTER, and XLOOKUP Formulas in Excel for Mac
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.
Before you start

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.

Solution 1Recommended

Update Excel and check workbook compatibility

Use this first when a workbook contains newer functions that Excel may not recognize.

1
Check your Excel version

In Excel, select Excel > About Microsoft Excel and check that your version supports SORT, FILTER, and XLOOKUP.

2
Install available updates

Select Help > Check for Updates, then use Microsoft AutoUpdate to install available Office updates.

3
Reopen the workbook

Close and reopen the workbook in Excel, then select an affected cell and check whether its formula calculates correctly.

Update Excel and check workbook compatibility
Compatibility prefixes: Prefixes such as _xlfn and _xlws can indicate that a function is unsupported or was not translated correctly. Updating Excel may not repair a formula that another application saved incorrectly.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for everyday workbook tasks.Work with spreadsheet formulas in a lightweight office suite.Move existing Office documents into WPS Office with format support.
microsoft office alternative - wps office

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.