How to Delete a Named Range in Excel? 3 Easy Methods

(Note: This guide on how to delete a named range in Excel is suitable for all Excel versions including Office 365)

Named ranges are helpful in decluttering your spreadsheet, especially if you have numerous formulas and an abundance of data in your spreadsheet. 

If you are a regular Excel user, you would have come across named ranges at least once in your work. You may use them to name and refer to a cell or a range of cells, instead of using their cell references. 

For example, after creating a named range called “SAMPLE” referring to A2:A10, you can use the named range inside formulas SUM(SAMPLE), instead of using the range directly i.e SUM(A2:A10). 

Named ranges simplify work and cut the clutter to make your files easy to understand for anyone. 

However,  you may develop a habit of using them and inadvertently overuse them in your spreadsheets. The only problem is that named ranges, especially dynamic ranges, consume a considerable amount of working memory. Overusing them may slow down your Excel spreadsheet. 

In this guide, I’ll show you how to delete named ranges in Excel by walking you through some important aspects like deleting all named ranges, filtering for specific named ranges and using VBA codes to simplify the process.

You’ll learn:

• How to Delete a Named Range in Excel Using Name Manager?
• How to Filter Specific Named Ranges to Delete?
• Advanced Tip – Excel How to Delete a Named Range Using VBA Code?

Related:

How to Remove Hyperlinks in Excel? 3 Easy Methods

How to Indent in Excel? 3 Easy Methods

How to Add Subscript in Excel? (6 Best Methods)

How to Delete a Named Range in Excel Using Name Manager?

Excel’s Name Manager is a built-in tool that allows you to create and manage all named ranges inside a spreadsheet. 

You can use it to delete all or some of the named ranges in a spreadsheet by following the steps below:

1. Click on the Name Manager option in the Defined Names section of the Formula tab. 

2. In the Name Manager window, select all the named ranges you want to delete. 

3. Hit the Delete button. 

Please note that you can select multiple named ranges at once by holding down the Ctrl key while clicking on them individually. 

How to Apply the Accounting Number Format in Excel? (3 Best methods)

How to Enable Excel Dark Mode? 3 Simple Steps

The FORMULATEXT Excel Function – 2 Best Examples

How to Filter Specific Named Ranges to Delete?

It is possible to rely too much named ranges, and before you realise it, your spreadsheet may be flooded with too many of them to select individually. 

That’s where the filter option in the Name Manager will come in handy to filter for specific types of named ranges. 

For example, if you want to delete only named ranges of tables, then you may do this by following these steps: 

1. Open the Name Manager from the Defined Names section of the Formula tab. 

2. In the Name Manager window, click on the Filter option and choose your desired Named Range type from the drop-down list. In this example, we use Table Names. 

3. Select the Name ranges you want to delete from the filtered list and hit the Delete button. 

Advanced Tip – Excel How to Delete a Named Range Using VBA code?

In some rare cases, even using the Name Manager filter to delete named ranges may become tedious. 

You can tremendously simplify the process by using VBA codes. Let’s see how to do that. 

1. Hit Alt+F11 to open the VBA editor. 

2. In the VBA editor menu, go to Insert > Module.

3. Enter either one of the following VBA code snippets inside the module window. 

Code 1: To delete only certain named ranges containing ‘yyy’ inside them.

Sub DeleteFilteredNamedRanges()
Dim FName As Name
For Each FName In Application.ActiveWorkbook.Names
If InStr(1, FName.Name, "yyy", vbTextCompare) > 0 Then FName.Delete
Next
End Sub

Replace ‘yyy’ with your desired named range, that you are looking for. 

Code 2: To delete all Named Ranges

Sub DeleteAllNames()
Dim NamedRange As Name
For Each NamedRange In Application.ActiveWorkbook.Names
NamedRange.Delete
Next
End Sub

4. Hit the Run button or simply press F5. 

Congratulations, you successfully deleted a specific or all named ranges from your spreadsheet at the press of a button. 

You may get creative and replace ‘ActiveWorkbook’ with ‘ActiveSheet’ to delete the named ranges only from the active sheet. 

Suggested Reads:

How to Autofit Excel Cells? 3 Best Methods

How to Extract an Excel Substring? – 6 Best Methods

How to Shade Every Other Row in Excel? (5 Best Methods)

Closing Thoughts

In this guide, I showed you two smart ways to delete unwanted named ranges from your spreadsheet. Do try these out in the practice worksheet and let us know what you think. If you have any questions about this or any other Excel feature, let us know in the comments section. We are always happy to help.

Resources

Visit our free Excel Resources Centre for more high-quality Excel guides. 

Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. 

You can train your entire team in Excel and other business software, for a single low monthly fee by clicking here.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!