Safely Delete an Excel Named Range with a #REF! Error
Question details
Determine whether an Excel defined name with a #REF! value can be safely deleted.
- Product
- Microsoft Excel defined names (named ranges)
- Device & OS
- not provided
- Scenario
- A workbook contains a defined name whose reference has become invalid, usually because its source cells, sheet, or range were deleted.
- Observed behavior
- The named range displays #REF!, but this error alone does not confirm that the name is unused elsewhere in the workbook.
Identify the exact invalid name in the Name Manager and note whether its scope is Workbook or a specific worksheet. A #REF! value means its target is invalid, so check formulas, data validation, and conditional formatting for dependencies before removal.
Check workbook dependencies before deleting the invalid named range
Use this method first when you need to confirm that formulas, rules, or other workbook features do not still use the name.
A defined name that returns #REF! is broken, but it can still be referenced by formulas, charts, data validation lists, conditional formatting rules, macros, or other defined names. Deleting it without checking may replace dependent formulas with additional errors.
In Excel, open the Formulas tab and select Name Manager. Locate the defined name whose Refers to or Value field shows #REF!.
Press Ctrl+F, enter the exact defined name, select Options, set Look in to Formulas, and choose Find All. Review every formula result that uses the name.
Select relevant worksheets, then open Data > Data Validation and Home > Conditional Formatting > Manage Rules. Check rule formulas and source fields for the invalid name.
Replace the invalid name in dependent formulas or rules with a valid cell range, another defined name, or an appropriate formula. Remove references that are no longer needed.
Delete the named range after confirming it is no longer needed
Use this method after reviewing dependencies or when the invalid name is clearly obsolete.
Manage Broken Excel Named Ranges in WPS Spreadsheet
WPS Spreadsheet lets you review, edit, and remove defined names while checking formulas that depend on them. Open the workbook in WPS Office, inspect the invalid name in Name Manager, update references where necessary, and delete the obsolete entry after validation.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Open, and select the Excel .xlsx or .xls workbook containing the invalid defined name.
- 2. Open the name management tools: Select the Formulas tab and open Name Manager to find the defined name with a #REF! reference.
- 3. Find dependent formulas: Press Ctrl+F, enter the name exactly as shown in Name Manager, and search formulas across the workbook for references.
- 4. Update or remove the invalid name: Correct dependent formulas or rules first, then select the broken name in Name Manager and choose Delete if it is no longer required.
Frequently Asked Questions
Does a #REF! value mean an Excel named range is unused?
No. #REF! means the name points to an invalid reference, often a deleted range or worksheet. Formulas, data validation, conditional formatting, charts, or macros may still reference that name.
How do I find a broken named range in Excel?
Go to Formulas > Name Manager. Look for names whose Refers to field contains #REF! or whose value evaluates to an invalid reference.
What happens if I delete a named range that is still used in a formula?
Formulas that use the deleted name can return a #NAME? error. Update the formulas with a valid range or replacement name before deleting the definition.
Can data validation use a named range that shows #REF!?
Yes. A data validation list or custom validation formula can reference a defined name. Check Data > Data Validation and inspect the Source or formula field before deleting the name.
