How to Clean Up Defined Names in Excel

Defined names make formulas readable, but they pile up over the life of a workbook. Copied sheets, imported ranges, and deleted data leave behind duplicates, stray scopes, and broken references that slow Excel down and clutter your dropdowns. Here is how to take stock of the names in a workbook and clean them up properly.

See every name with the Name Manager

Before you delete anything, get a complete picture of what is defined in the workbook.

1. Press Ctrl+F3 to open the Name Manager, or go to the Formulas tab and click Name Manager.
2. Review the Name, Value, Refers To, and Scope columns for every entry.
3. Click the Filter button to narrow the list to names with errors, names scoped to the workbook, or names scoped to a specific sheet.
4. Sort by the Refers To column to spot duplicates and ranges that no longer make sense.

Know what is safe to remove

A name is usually safe to delete when its Refers To value shows a #REF! error, when it duplicates another name that points at the same range, or when nothing in the workbook references it anymore. Be careful with names used in charts, data validation lists, conditional formatting, and PivotTables, because those references are easy to miss. When in doubt, note where a name is used before removing it.

Delete and rename in the Name Manager

The Name Manager lets you remove a name by selecting it and clicking Delete, and you can Ctrl-click to select several rows at once before deleting. To rename, select a name, click Edit, and change the Name field. The catch is that editing values and scopes still happens one entry at a time, and the dialog has no search box, so a workbook with hundreds of names becomes tedious to maintain.

Clean up names in bulk with ModelMint

Name Scrubber gives you a searchable, filterable list of every defined name and lets you act on many at once. Filter to the names you care about, then rename, hide, unhide, or delete them in bulk, and sweep out broken #REF! names in a single pass. It turns an afternoon of clicking through the Name Manager into a couple of minutes.