How to Fix Excel Defined Names Changing After an Office 365 Update
Question details
The user needs to fix an issue where defined names in a workbook point to a range in an XLA add-in file following an Office 365 update, causing #NAME? errors and breaking macros.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Running VBA macros or calculating formulas that rely on defined names after applying a recent Office 365 update.
- Observed behavior
- Defined names automatically and incorrectly change to reference an XLA add-in file. This breaks macro functionality and results in a #NAME? error that persists even if the name is manually repaired.
Before modifying your defined names or VBA code, review the Name Manager to document the original intended cell ranges for your references, and create a backup copy of your workbook.
Replace the Defined Name with the Full Range Address in VBA Macros
This is the most reliable workaround to prevent the Office 365 update bug from continually breaking the defined name reference in your macros.
By hardcoding the absolute cell address directly into your VBA macros, you remove the reliance on Excel's Name Manager. This prevents the application from mistakenly linking your reference to an external XLA add-in.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer on the left, double-click the module containing your macro code and find the lines referencing the broken defined name.
Change the code so it uses the explicit range address instead of the name. For example, replace Range("MyData") with Range("Sheet1!A1:D10").
Return to your Excel workbook, go to the Formulas tab, and click Name Manager. Select the problematic defined name and click Delete to prevent further #NAME? formula errors.
Recreate the Defined Name with Explicit References
If you prefer not to alter your VBA code, you can recreate the defined name by specifying the fully qualified reference to force Excel to look at the correct workbook.
Try WPS Office to Avoid Update-Triggered Macro and Formula Errors
If recent Office 365 updates are causing disruptive #NAME? errors and corrupting your workbook's defined names, consider switching to WPS Office. It provides a highly stable, lightweight environment for managing complex spreadsheets without unexpected reference changes.
- 1. Download WPS Office: Visit the official WPS website to download and install the free, lightweight suite.
- 2. Open Your Workbook: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .xlsm files.
- 3. Manage Names Reliably: Use the reliable Name Manager in WPS Spreadsheet under the Formulas tab to keep your named ranges intact.
Frequently Asked Questions
Why does my defined name show a #NAME? error after an Excel update?
Office updates can sometimes alter how Excel resolves workbook references, especially when XLA add-ins are active. This bug can cause Excel to point a defined name to a missing add-in range, resulting in an unrecognized name (#NAME?) error.
How do I find and remove a damaged defined name?
Go to the Formulas tab in Excel and click on Name Manager. Look for any names that display a #NAME? or #REF! error in the 'Values' or 'Refers to' columns, select them, and click the Delete button.
Can I still run macros if I delete my defined names?
Yes. You will need to update your VBA macro code to reference explicit cell addresses (such as Range("A1:D10")) instead of relying on the defined names. This prevents errors caused by broken name references in the Name Manager.
What is an XLA file in Excel?
An XLA or XLAM file is an Excel Add-in file. It typically contains VBA macros, custom functions, and other tools that run in the background to provide additional features to your standard Excel workbooks.
