Clean up hidden and broken Excel names
Defined names accumulate. Some are hidden, some point at #REF!, some point at another file, and some are leftovers that no formula uses. Name Manager is where you delete them. The named-range auditor is the list that shows which kind each name is, including names the Name Box hides. Reserved names that start with _xlnm are Excel’s own print areas and filters. Leave those alone unless the print area itself is wrong.
See the whole list
The auditor reads definedName elements from workbook.xml. Scope is Workbook or a single sheet. Hidden names are marked. Broken names contain #REF!. External names contain a bracketed workbook. Unused candidates are names that are not reserved, not broken, and whose label does not appear in scanned formulas or in other names. That last test can flag a name that a chart, a data-validation rule, or a conditional format still uses, because those are not cell formulas. The word candidate is deliberate. The list is a review queue, not a delete script.
Hidden names
Excel hides some names so they do not clutter the Name Box. Filters and some internal features do this. A hidden name is not a macro and it is not evidence of abuse. Read the formula. A hidden name that points at a sheet you know, with a normal range, can stay. A hidden name that points at another file, or at #REF!, should be treated like any other broken or external name. Very hidden sheets and hidden names are different mechanisms. A workbook can have either, or both. Check visibility separately if the name’s formula refers to a sheet you cannot see.
Broken names
A name whose formula is #REF! will push #REF! into every cell that uses it. Fixing the cells one by one does nothing if the name is still broken. Open Name Manager, find the name, and either retarget it or delete it. If you delete it, cells that used it become #NAME? until you edit them. If many cells used it, retargeting is kinder than deleting. Then calculate and run the error finder. The #REF! count should fall. If the broken name was external, you have also removed a link, which the link finder should agree with on the next save.
Unused candidates, carefully
Sort the candidates. Ignore _xlnm names. For each remaining name, search the workbook in Excel for that label in formulas. Check data validation lists and conditional formats if the search is empty and you still doubt the flag. Then delete it on a copy. There is no prize for the shortest name list. There is a cost to deleting a name that a chart series uses only in the chart XML. If a chart breaks, restore the name from the copy. Names that duplicate a table’s structured references are common leftovers from a range that was turned into a table. Those are usually safe to remove once no formula uses the old name.
When you want the numbers from your own workbook, audit the name list locally.
Scope surprises
A sheet-local name and a workbook name can share a label and point at different ranges. Formulas on that sheet use the local name. Formulas elsewhere use the global one. If two sheets “should” calculate the same way and do not, compare scopes on the auditor before you rewrite logic. Renaming is cleaner than living with the collision. Name Manager shows scope. The auditor shows it without opening Excel, which matters when you do not yet want macros in an untrusted file to run.
.xlsb and the copy you keep
Binary workbooks store names in workbook.bin, which this version does not decode. Save a copy as .xlsx to audit names here, and keep the binary original. After any cleanup, save, calculate, and skim for #NAME?. That error is the cleanup talking back. It is easier to restore one name than to discover the hole in a meeting.
A review order that avoids #NAME?
Print or copy the auditor’s table and mark four passes. Pass one: broken names. Retarget or delete them, calculate, and confirm #REF! falls. Pass two: external names you do not want to keep. They are links. Handle them with the link guide so you do not leave a value gap. Pass three: hidden names whose formulas you have actually read. Most stay. Pass four: unused candidates, a few at a time, on a copy, with a calculate between batches. #NAME? means you crossed a name that was still in use, including a use the candidate flag could not see. Restore that one name. Do not restore the whole batch if only one name was wrong. Reserved _xlnm names are not in these passes unless a print area is visibly wrong, in which case you change it from Page Layout rather than from a delete key.
Names and sheets with the same purpose
Teams often have a name Sales and a sheet Sales and a table Sales. The auditor is how you see the collision instead of discovering it when a formula on one sheet returns a different total. Pick one of them as the supported interface and point formulas at it. Leave a note in Name Manager’s comment field if you can. The next cleanup will go faster if the comment says “used by the board pack” or “leftover from the 2022 range.” Comments are not part of this site’s unused test. They are part of making the next human slower to delete the wrong thing. A name without a comment and without a formula reference is the one the fourth pass is for. Stop after a handful of deletions and calculate. A single #NAME? is a cheap undo. A sheet full of them means the batch was too large and the candidate flag was doing more work than your search. Shrink the batch, not your patience. When the fourth pass is finished, rerun the auditor and keep the remaining list. A name that survived because a formula still uses it should stay in Name Manager with a comment, so the next cleanup does not treat it as abandoned. Delete only after a calculate on the copy shows no new #NAME? errors anywhere in the workbook, including sheets you do not normally open.
Related reading
The closest repair notes are Fix stored formula errors and A list of volatile functions .
Walk the same checks in order with the Workbook audit checklist.
The terms behind this guide are Defined name and External link .
The paired check for that note is the Find #REF! errors in a workbook.
Questions people ask before trusting the result
Will deleting a print area hurt anything?
It clears the print area. The name _xlnm.Print_Area is reserved in the report so it is not offered as an unused candidate. Change print areas from the Page Layout tab if they are wrong.
Can two names have the same label?
Yes, when their scopes differ. A local name hides the global name on that sheet. The report shows the scope so you can see the pair.
Do table names appear here?
Tables are a different feature. Some workbooks also have a defined name left over from the range that became the table. The leftover can be an unused candidate. The table itself remains.
Should every hidden name be deleted?
No. Read the formula. Hidden is a display flag. Broken and external are the flags that usually need an edit.
Can a name start a macro?
A name does not run code. A macro can read a name. If the file is untrusted, keep macros disabled and still read the name list. The VBA inventory is separate.