How to Delete All Named Ranges in Excel (2 Ways)

Suppose you created a huge number of named ranges in your worksheet but now there is no use for these named ranges. To delete all named ranges in Excel you need to utilize different kinds of methods. This article provides a platform how to delete all named ranges in Excel.


Download Practice Workbook

Download this practice workbook.


What Is Named Range in Excel?

In excel, Named range means using a name that refers to cell references, formula results as well as values. It reduces appearing large values in the formula every single time and makes it easier to use. At the same time, It reduces using cell reference (such as cell B4:B15) every time. One can easily use the assigned name and get the desired result.

In this dataset, we describe an item, apple, and its sales in three different months. We like to calculate the total sales for these three months. Previously we have done this by using the Sum function with a given cell reference. But if we give the cell reference name ‘Apples’ then we don’t need to use cell reference anymore.

Named Range


2 Ways to Delete All Named Ranges in Excel

To delete all named ranges in Excel that are assigned previously, we have two essential methods to do it.

1. Delete All Named Ranges Using Name Manager

The first and the easiest method is to use the Name Manager option. When it comes to definite ‘Name Manager’, this is a place where you can create, edit and delete all named ranges.

Here, We have a dataset that defines sales in three different months for several fruits items.

How to delete all named ranges in Excel

Steps

  • At first, you need to find the ‘Formulas’ tab in the ribbon and then select the ‘Name Manager’ to open.

  • After opening the ‘Name Manager’ dialog box, you have all the assigned named ranges in there. Then you need to select the entire named ranges by pressing the ‘Shift’ key and selecting all the named ranges or pressing the ‘Ctrl’ button and selecting one after another. Next, click on the ‘Delete’ option.

  • A prompt box will appear whether you want to delete the selected names or not. Click on ‘OK’.

  • By doing this we have an empty ‘Name Manager’ dialog box that means all the named ranges are deleted.

  • Previously stated that we use named ranges to calculate the total sum of several fruits items. But when we delete all the named ranges function from ‘Name manager’, we will find ‘Error’ in the total sales column that denotes all the assigned ranges are deleted.

How to Delete all Named Ranges in Excel

As you can see, our method successfully worked to delete all named ranges in Excel.

Read More: [Fixed!] Name Manager Delete Option Greyed out in Excel (3 Reasons)


2. Delete All Named Ranges With VBA Code

Another effective way to delete all named ranges in Excel is to use VBA code.

Steps

  • To open VBA, you need to press ‘Alt+F11’ on your keyboard. You can include the Developer tab in the ribbon by customizing the ribbon.
  • Then click on ‘Insert’ in which you’ll get the ‘Module’ option then click on it.

  • Then you’ll find a ‘Module’ window where we can add the following code:
Sub Delete_named_ranges()

Dim named_range As Name
For Each named_range In Application.ActiveWorkbook.Names
  named_range.Delete
Next

End Sub

Delete All named Ranges using VBA

  • After that, you need to minimize the ‘Module’ window and search the ‘View’ tab in the ribbon. You’ll see a ‘Macros’ tab, click on it.

  • A ‘Macro’ prompt box will open. After that, select Delete_named_ranges and then click on ‘Run’.

Delete All named Ranges using VBA

  • When you run the code, all the named ranges will be deleted and you’ll find a similar result to the previous method.

As you can see, our method successfully worked to delete all named ranges in Excel.

Read More: How to Edit Name Box in Excel (Edit, Change Range and Delete)


Conclusion

Both methods are equally effective to delete all named ranges in Excel. You can use one of the two methods and get the desired result. If you have any questions you can comment below. You can visit our Exceldemy for further knowledge about Excel.


Related Articles

Durjoy Paul
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo