How to Delete Defined Name in Excel? (Name Manager + VBA)

We define and use names in Excel formulas to easily understand, reference, and maintain data.

For example, we can define a name for a table, a cell range, a constant, or a function. 

Too many defined names in your workbook consume a  lot of working memory and may slow down your workbook.

Therefore, you must delete some defined names to improve the workbook’s performance. 

This tutorial shows three techniques for deleting defined names in Excel

Note: If a formula in the workbook uses a defined name you want to delete, ensure that you replace it before deleting it; otherwise, the formula will return an error. 

Method #1: Use the Name Manager Tool

The Name Manager in Excel is a built-in tool that lets us generate, edit, delete and locate all the names used in a workbook. 

We will use the following workbook with three defined names in the Name Box to show how we can use the Name Manager tool to delete a defined name in Excel.

Name box

We want to delete the James_Washington_Math_Score defined name because we no longer need it in the worksheet. 

Below are the steps to delete defined names using Name Manager:

  1. Click the Formulas tab and select the Name Manager option in the Defined Names group. This process opens the Name Manager tool.
Click on Name Manager

Note: You can also open the Name Manager tool using the keyboard shortcut Ctrl + F3.

  1. In the Name Manager window, select James_Washington_Math_Score in the list of defined names and click the Delete button. Please note that if you want to delete multiple names at once, you can select them by holding down the Ctrl key and clicking on them one after the other. 
Click the delete button

A message box appears asking you whether you are sure you want to delete the name James_Washington_Math_Score.

Prompt to confirm deleting the defined name
  1. Click OK on the warning message box that appears.

The name is removed from the list of names in the Name Manager window.

defined name removed
  1. Click the Close button to close the Name Manager. 

If you check the Name box, you see that James_Washington_Math_Scores no longer appears.

Name box doesn't show the name anymore

Filtering for and Deleting Specific Defined Names

Sometimes, your workbook may have too many names to select individually.

That is where the filter option in the Name Manager comes in handy to filter for specific types of names before deleting them. 

For instance, if you want to remove only the defined names scoped to the active worksheet, you do the following:

  • Press Ctrl + F3 to open the Name Manager.
  • Click the Filter button and select Names Scoped to Worksheet.
click the filter button

Only those names scoped to the worksheet will appear in the Name Manager window, making selecting and deleting them easy.

Deleting Names in a Protected Worksheet

Sometimes when you open the Name Manager tool, you may find that the Delete button is greyed out as follows:

delete button grayed out

This happens when the worksheet is protected. You cannot delete names in a protected worksheet. 

You will need to unprotect the worksheet before deleting the names. 

Click the Review tab and select Unprotect Sheet option in the Protect group. You may be required to supply the password used to protect the worksheet. 

click on unprotect sheet

Method #2: Filter For Delete Defined Names Using Excel VBA

As helpful as the Filter option in the Name Manager tool is in filtering and deleting names, sometimes it can become tedious, especially when dealing with too many names. You can simplify the process by using the VBA code.

We use the following workbook with three defined names to show how we can use Excel VBA to filter for and delete specific names. 

three defined names in the workbook

We want to filter for and delete only those defined names that contain the word Student.

We use the following steps:

  1. Click the Developer tab and click Visual Basic in the Code group. This step opens the Visual Basic Editor.
click on visual basic
  1. Click Module on the Insert Menu to create a new module in the Visual Basic Editor.
click on Module
  1. Type the following sub-procedure in the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub FilterDeleteNames()
Dim FilteredName As Name
    For Each FilteredName In Application.ActiveWorkbook.Names
        If InStr(1, FilteredName.Name, "Student", vbTextCompare) > 0 Then FilteredName.Delete
    Next
End Sub
  1. Save the sub-procedure and save the workbook as a Macro-Enabled Workbook.
  2. Click inside the sub-procedure and press F5 to execute the code.
  3. Click the View Microsoft Excel button on the toolbar to switch to the active workbook containing the dataset. You can also use the keyboard shortcut Alt + F11.
  4. Open the Name Box and see that all the defined names that contain the word Student have been deleted.
Name Box and see that all the defined names

Method #3: Use Excel VBA to Delete All Defined Names

We may want to delete all defined names from a workbook at once. That‘s when Excel VBA comes in handy.

We will use the following worksheet with four defined names to show how we can use the Excel VBA to delete all defined names from a workbook.

defined names in the worksheet

We use  the steps below:

  1. Click the Developer tab and click Visual Basic in the Code group. The Visual Basic Editor is opened.
click on visual basic

Note: You can also open the Visual Basic Editor using the keyboard shortcut Alt + F11.

  1. Click Module on the Insert Menu to create a new module in the Visual Basic Editor.
insert a module
  1. Type the following sub-procedure in the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub DeleteDefinedNames()
Dim dName As Name
    For Each dName In Application.ActiveWorkbook.Names
        dName.Delete
    Next
End Sub
  1. Save the sub-procedure and save the workbook as a Macro-Enabled Workbook
  2. Click inside the procedure and press F5 to run the code.
  3. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset. 
Click the View Microsoft Excel button
  1.  Open the Name Box and see that all the defined names have been deleted.
All defined names deleted

This tutorial has looked at three techniques for deleting defined names in Excel.

The methods include the application of the Name Manager tool, the Excel VBA to filter for and delete specific names, and Excel VBA to delete all the defined names in a workbook.

We hope that you found the tutorial helpful. 

Other articles you may also like: