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 in Excel.
Let’s say, we have a dataset with some range of cells Defined as their top column names & the whole table as Salary_Data.
How to Delete Defined Names in Excel: 3 Easy Methods
Method 1: Using Formula Tab to Delete Defined Names in Excel
📌 Steps:
- Open the workbook, you want to delete the Defined Names.
- Go to the Formulas Ribbon >> Name Manager (in the Defined Names section).
- On the Name Manager window, we can see the Defined Names.
- Select the Defined Names you want to delete. Here, we want to delete Allowance, Job_Id & Name (press CTRL & then click on the multiple Defined Names you want to delete).
- Click on the Delete button.
- A warning window shows up. Click the OK button.
- Again, look at the Name Manager window.
We can see the rest of the Defined Names.
- The results will be similar to the image below.
The deleted names are no longer available.
Method 2: Using Keyboard Shortcut to Delete Defined Names
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
📌 Steps:
- Press CTRL +F3 altogether, and the Name Manager window will pop up.
- Select single or multiple Defined Names.
- Click on Delete.
- A warning dialog box pops up. Click OK on the warning window.
- The outcomes depict results similar to the image below.
Method 3: Using VBA Macro to Delete All Defined Names in Range
At first, we have all the Defined Names in the dataset.
We want to delete all the Defined Names existing in the dataset. For this purpose, we use VBA macro Code.
📌 Steps:
- On the Excel sheet, Press ALT+F11 altogether. Microsoft Visual Basic window will open up.
- Go to the Menu Bar & select Insert >> Module.
- In the Module, paste the following Code.
- Press F5 to run the code.
- Go to the Excel Worksheet, and check the Defined Name to the Left of the Formula Box. You’ll see all the Defined Name gets Deleted.
How to Edit Defined Names in Excel?
In this section, we will show how to edit defined names in Excel using Name Manager. If we want to modify the name or the selected range, we have to follow this.
📌 Steps:
- Click on any cell of the dataset. Here, we click on Cell B4.
- Then, go to the Formulas tab.
- Choose the Name Manager option from the Defined Names group.
- The Name Manager window appears.
We can see all the Defined Names listed here.
- Select the desired Name to edit.
- Then, click on the Edit option.
- The Edit Name window appears.
The Name and referred cell references are shown and marked in the image.
- Now, modify the Name and press OK.
- Now, look at the appeared window.
We can see the name of the selected Name has been changed after the editing.
- We can also add Defined Names from this window. For that, click on the New option.
- After that, enter a name and select the cell reference as shown in the Edit Name window.
Dataset for Download
Conclusion
For ease of execution, we use Defined Name in Excel. But in some cases, we need to Delete Defined Names in Excel. Though there are not many methods to get the work done, Excel offers a Formulas tab, Keyboard Shortcut, and VBA methods to work around. In this article, we describe these simple methods. I hope you find these methods super easy and the steps easy to follow.
