Fix Excel Structured Table References That Change to #NAME? After Reopening
Question details
The user needs to repair an Excel table formula that works during editing but changes to a #NAME? error after the workbook is closed and reopened.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains formulas using structured table references, such as TableName[ColumnName], and must retain those formulas after saving and reopening.
- Observed behavior
- After reopening the workbook, Excel displays #NAME? in formulas that previously used valid structured table references, indicating that a table name, column name, or related defined name was not preserved correctly.
Open the workbook in the desktop version of Excel and check the file extension before making changes. Structured table references require an Excel workbook format such as .xlsx or .xlsm; CSV files cannot retain tables or formulas.
Verify the Excel table and column names used by the structured reference
Use this method when the table may still exist but the formula returns #NAME? because its table name or header text no longer matches the formula.
Structured references depend on the exact table name and column header names. A renamed table, edited header, extra space, or changed special character can cause an otherwise valid formula to return #NAME?.
Click any cell inside the source table so that the Table Design tab appears on the Excel ribbon.
Open Table Design and review the Table Name box in the Properties group. Confirm that it matches the name used in the formula, such as SalesData in =SUM(SalesData[Amount]).
Review the table header row and verify that each referenced column name exactly matches the text inside brackets in the formula, including spaces and punctuation.
Click the error cell, press F2, and retype or select the table and column reference from the worksheet. Press Enter and save the workbook.
Save the workbook in XLSX or XLSM format
Use this method when the workbook was saved as CSV, text, or another format that does not preserve Excel tables, formulas, and defined names.
Repair broken names or recreate the Excel table
Use this method when the table name is missing, Name Manager shows broken entries, or the original table was converted to a normal range.
Work with Excel Table Formulas in WPS Spreadsheets
If Microsoft Excel continues to reopen a workbook with broken structured references, try WPS Office as a free, lightweight Office alternative. WPS Spreadsheets provides a familiar spreadsheet workspace, supports common Excel workbook formats, and helps you move existing files into an easy-to-use editing environment.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the app home screen.
- 2. Open the Excel workbook: Select Open and choose the affected .xlsx or .xlsm workbook from your computer or connected storage.
- 3. Review formulas and table data: Select the affected worksheet cells and verify the table headers, ranges, and formula references in the formula bar.
Frequently Asked Questions
Why does an Excel structured reference show #NAME? after reopening a file?
The table name or column header may have changed, the table may no longer exist, or the file may have been saved in a format such as CSV that does not preserve tables and formulas. Check Table Design > Table Name and the table headers first.
Can a CSV file retain Excel table names and structured references?
No. CSV stores plain text values and does not retain Excel table objects, formulas, defined names, or formatting. Save the working workbook as File > Save As > Excel Workbook (*.xlsx) instead.
Where can I find the table name in Excel?
Click any cell inside the table, then open the Table Design tab. The current name appears in the Table Name box in the Properties group.
How do I fix a broken defined name in Excel?
Go to Formulas > Name Manager, select the name with a #REF! entry in Refers to, and click Edit to point it to the correct range. Delete the name if it is obsolete.
What can I use if structured references keep failing in a workbook?
Recreate the range as a table through Insert > Table, then update the formula with the new table name. Alternatively, replace the structured reference with normal cell ranges or an INDEX/MATCH formula.
