Editor choice

How to Delete Defined Names in Excel (3 Ways)

Defined Names assigned to a single cell, range of cells, formulas, tables, and so on; are used in order to increase convenience when those elements need to be assigned as data in the same or different worksheets in Excel. In this article, we’ll discuss Formulas Tab, Keyboard Shortcuts, and VBA macro code method to delete specific Defined Names.

Let’s say, we have a dataset with some range of cells Defined as their top columns names & the whole table as Salary_Data.

dataset

Dataset for Download

3 Easy Methods to Delete Defined Names In Excel

Method 1: Using Formula Tab

Step 1:Open the workbook, you want to delete the Defined Names.

Step 2:Go to the Formulas Ribbon >> Click Name Manager (in the Defined Names section).

formula tab method

Step 3: On the Name Manager window, Select the Defined Names you want to delete. Here, we want to delete Allowance, Names & Salary_Data (press CTRL & then click on the multiple Defined Names you want to delete).

formula tab method

Step 4:Click on Delete. 

formula tab method

Step 5: A warning window shows up. Click OK.

formula tab method

The results will be similar to the image below

result formula tab method

The deleted names are no longer available.

Method 2: Keyboard Shortcut

You can simply use pressing CTRL + F3 altogether to bring up the Name Manager window in Excel. In the beginning, the dataset has Defined Names such as

Keyboard shortcut method

Step 1: Press CTRL +F3 altogether, and the Name Manager window will pop up.

Step 2:Select single or multiple Defined Names.

Keyboard shortcut

Step 3: Click on Delete.

Keyboard shortcut

Step 4: A warning dialog box pops up. Click OK on the warning window.

Keyboard shortcut

The outcomes depict results similar to the image below

result of Keyboard shortcut method


Similar Readings


Method 3: Using VBA Macro Code (Delete all Name Range)

At first, we have all the Defined Name in the dataset

VBA method

We want to delete all the Defined Names existing in the dataset. For this purpose, we use VBA macro Code.

Step 1: On the Excel sheet, Press ALT+F11 altogether. Microsoft Visual Basic window will open up.

Step 2:Go to the Menu Bar & Select Insert>>Module.

VBA method

 

 

Step 3: In the Module, Paste the following Code.

Sub DeleteNames()
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
xName.Delete
Next
End Sub
 VBA code

Step 4: Press F5 to run the code.

Step 5: Go to the Excel Worksheet, Check the Defined Name to the Left of the Formula Box. You’ll see all the Defined Name gets Deleted.

result VBA method

Read More: How to Remove Specific Text from a Column in Excel (8 Ways)

Conclusion

For ease of execution we use Defined Name in Excel, in some cases we need to Delete Defined Names. Though there are not many methods to get the work done, Excel offers Formulas tab, Keyboard Shortcut, and VBA methods to workaround. In this article, we describe these methods as simple as possible. Hope you find these methods super easy and steps easy to follow.


Further Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo