Find Named Range References and Invalid Names in Excel
Question details
Find where a named range is used in an Excel workbook and check whether a name with an invalid reference can be deleted safely.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to audit a named range before deleting it, especially when the Name Manager shows an invalid reference.
- Observed behavior
- A named range displays #REF! in its Refers To field, which commonly occurs after its referenced worksheet or cell range was deleted.
Open the workbook in the Excel desktop app and note the exact name shown in Formulas > Name Manager. If the name contains #REF!, review its dependencies before removing it because formulas or workbook rules may still use the name.
Search workbook formulas for the named range in Excel
Use this method to find direct formula references to a named range throughout the current workbook before deleting or correcting it.
Excel Find can locate formulas that contain the named range. Searching with Look in set to Formulas helps avoid matches that appear only as displayed values.
Go to Formulas > Name Manager, then select the named range you want to inspect.
Copy the name from the Name column to locate formulas that use the name. You can also copy the text in Refers To to investigate the underlying cell or sheet reference.
Close Name Manager, press Ctrl+F, and paste the named range name or copied reference into the Find what box.
Select Options, set Within to Workbook, and set Look in to Formulas. Select Find All to list matching formula cells.
Open each result and determine whether the named range is still needed. If an invalid name is not referenced anywhere required, return to Formulas > Name Manager and select Delete.
Check Excel objects that can store named range references
Use this deeper review when formula searching does not find a name but the workbook may use it in rules, controls, or reporting objects.
Manage and audit named ranges in WPS Spreadsheets
WPS Spreadsheets lets you create, review, edit, and remove defined names while working with workbook formulas. Use its formula tools and workbook search to inspect named-range usage, then correct broken references or delete names that are no longer needed.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook, and select the worksheet that contains related formulas.
- 2. Review defined names: Go to the Formulas tab and open the name management or defined-name command to inspect the name and its reference.
- 3. Search formulas across the workbook: Press Ctrl+F, enter the named range, choose the workbook search scope, and search formula content for direct references.
- 4. Correct or remove the name: Update the broken reference if it is still required, or delete the defined name after confirming that no formulas or workbook rules need it.
Frequently Asked Questions
How do I find every formula that uses a named range in Excel?
Press Ctrl+F, enter the exact named range, select Options, set Within to Workbook and Look in to Formulas, and choose Find All. Review the listed cells for direct formula references.
What does #REF! in the Refers To field mean?
It means the defined name points to an invalid reference, often because the referenced cells, range, or worksheet were deleted. Check whether the name is still used before deleting it.
Why does Excel Find not show a named range that I know is used?
The name may be used outside normal cell formulas, such as in Data Validation, Conditional Formatting, chart series, or another workbook object. Review those settings or use a name-management add-in for a broader audit.
Can I delete an invalid named range from Name Manager?
Yes. Go to Formulas > Name Manager, select the invalid name, and select Delete. First search workbook formulas and inspect relevant workbook rules to confirm that nothing still depends on it.
Should I search for the name or the Refers To value?
Search for the exact name to find formulas that call the named range. Search the Refers To value as an additional check when you need to investigate references to the underlying sheet or cell range.
