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

Get FREE Advanced Excel Exercises with Solutions!

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.

What Is Named Range in Excel?

A Named range in Excel 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

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

1. Using Name Manager to Delete All Named Ranges in Excel

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 named ranges.

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

Delete All Named Ranges Using Name Manager in Excel


  • 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 range 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.

Remove All Named Ranges Utilizing Name Manager in Excel

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

Read More: How to Delete Named Range in Excel

2. Applying VBA Macro to Delete All Named Ranges

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


  • 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

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

Remove All Named Ranges With VBA Code

  • 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: Excel VBA to Delete Named Range

Similar Readings

Download Practice Workbook

Download this practice workbook.


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.

Related Articles

Durjoy Paul
Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF