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.

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.

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

2. 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. 

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

3. Click OK on the warning message box that appears.

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

4. 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.

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.

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:

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. 

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. 

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.

2. Click Module on the Insert Menu to create a new module in the Visual Basic Editor.

3. 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

4. Save the sub-procedure and save the workbook as a Macro-Enabled Workbook.
5. Click inside the sub-procedure and press F5 to execute the code.
6. 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.
7. Open the Name Box and see that all the defined names that contain the word Student have been deleted.

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.

We use  the steps below:

4. Click the Developer tab and click Visual Basic in the Code group. The Visual Basic Editor is opened.

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

5. Click Module on the Insert Menu to create a new module in the Visual Basic Editor.

6. 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

7. Save the sub-procedure and save the workbook as a Macro-Enabled Workbook. 
8. Click inside the procedure and press F5 to run the code.
9. Click the View Microsoft Excel button on the toolbar to switch to the active worksheet containing the dataset. 

10.  Open the Name Box and see that all the defined names have been 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:

• How to Rename a Table in Excel?
• Excel Table vs. Excel Range – What’s the Difference?
• How to Remove Table Formatting in Excel?