How to Fix the #NAME? Error Caused by the ANCHORARRAY Function in Excel
Question details
The user needs to resolve a #NAME? error in their spreadsheet caused by the unsupported ANCHORARRAY function.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening or editing a workbook originally created in a different spreadsheet application (such as SPREAD.net) that uses application-specific array functions.
- Observed behavior
- The targeted cell returns a #NAME? error because standard Excel and Google Sheets do not recognize the ANCHORARRAY function.
Identify the original spreadsheet application used to create the file and confirm that your current software supports dynamic array formulas.
Replace ANCHORARRAY with Native Spill-Reference Syntax
Convert the unrecognized ANCHORARRAY function to Excel's native dynamic-array spill syntax using the hashtag (#) operator.
The ANCHORARRAY function is specific to third-party spreadsheet tools like SPREAD.net and is not a native Excel function. To achieve the same result and clear the error in modern Excel, you must switch to the spill-reference operator (#).
Click on the cell displaying the #NAME? error in your worksheet.
Click into the Formula Bar at the top of the screen. You should see a formula similar to =ANCHORARRAY(B2).
Highlight and delete the 'ANCHORARRAY' text along with the surrounding parentheses, leaving only the cell reference (e.g., B2).
Type a hashtag (#) immediately after the cell reference. Your formula should now look like =B2#.
Press Enter. Ensure the referenced cell (in this case, B2) is actually the anchor cell of a spilled dynamic-array formula.
Manage Dynamic Arrays Seamlessly in WPS Office
WPS Spreadsheet provides comprehensive support for modern array formulas and dynamic spill references, making it fully compatible with complex files exported from other spreadsheet applications.
- 1. Open the workbook: Launch WPS Spreadsheet and open the file containing the unsupported ANCHORARRAY formula.
- 2. Select the error cell: Click on the specific cell displaying the #NAME? error to make it active.
- 3. Update to spill syntax: In the formula bar, rewrite the formula to use standard syntax by typing the anchor cell followed by the hashtag (e.g., =B2#).
- 4. Calculate the array: Press Enter to instantly calculate and spill the array across the required cells.
Frequently Asked Questions
What does the #NAME? error mean in my spreadsheet?
The #NAME? error occurs when the spreadsheet application cannot recognize text inside a formula. This usually happens if a function name is misspelled, text is missing quotation marks, or an application-specific function like ANCHORARRAY is used but not supported.
Why did my file open with ANCHORARRAY formulas?
Your file was likely created or modified in a different spreadsheet program, such as SPREAD.net, which uses ANCHORARRAY to handle dynamic arrays. When opened in standard Excel or Google Sheets, the function goes unrecognized.
What is a spill-reference or the '#' operator?
The '#' operator is used in modern spreadsheet applications to refer to the entire range of a spilled dynamic array. Adding it after a cell reference (like =B2#) tells the software to include all values generated by the array formula located in that anchor cell.
Will replacing ANCHORARRAY with the '#' operator affect my data?
No, changing the formula to the native spill-reference syntax simply updates how the spreadsheet reads and references the data. As long as the target anchor cell contains a valid dynamic array, your data will display correctly.
