How to delete Excel named ranges

Before we jump into how to delete an excel named range we will first look at what they are and why you might use them. Named Ranges can be extremely useful when you’re working with formulas. They allow you to quickly name a cell, a range of cells, a table or even a function – once named, these can then replace the cell references making the formulae understanding and maintenance much easier and less prone to errors. 

In spreadsheets, used appropriately and sparingly, range names can provide great value because they can make formulas easier to read. 

However, opinion is divided between those who like the use of names in Excel formulas and those who don’t.  

Arguments for named ranges 

• Names make a formula clearer and easier to read. The intent of =Revenue-Costs is clearer than =P999-P807 
• This is particularly so when the formula refer to cells on other workbooks. The name formula would still be =Revenue-Costs; the coordinate formula would be =’Profit and loss’!P999-‘Profit and loss’!P807. In longer formula, the clutter of worksheet names is particularly impenetrable. 
• Relying on Excel to match rows and columns relieves the analyst of the need to do the same and eliminates a common cause of error, referring to a cell that is in a different column from the one intended. It also forces a model developer to pursue consistent conventions about what columns are for in different parts, or on different worksheets, of a model. 

Arguments against using names 

• Names are not widely understood by ordinary spreadsheet users. Using them makes a spreadsheet less accessible, and less safely modifiable, by such users. 
• Determining which cells the formula =Revenue-Costs refers to needs some work, firstly, to check that those names are associated with the intended cells, and secondly to fathom out the effect of any row or column matching that may apply. 
• What happens when a formula expressed using names is cut or copied to a new location is not the same as what happens when a formula expressed in coordinate notation is so moved.  

How to delete range names in Excel 

Range names are stored in what is known as the Name Manager in Excel. You can delete or change unwanted named ranges using the Excel Name Manager. 

1. Select the Fomulas ribbon and, under the ‘Defined Names’ group, click on ‘Name Manager’

 

2. In the Name Manager select the name (or names) to be deleted and press the ‘Delete’ button: 

3. Excel will ask for confirmation and pressing ‘Yes’ will permanently delete the selected named ranges. 

Issues with using Excel’s Names Manager to delete name ranges 

Named ranges can be created automatically by Excel when using Excel’s Solver scenarios, where Excel generates names for calculation parameters and other sensitive data. These names are not visible on Excel’s Name Manager and therefore not possible to delete using the procedures above – hence another reason to use OAK’s Delete Names tool.  

Another problem is related to the number of existing named ranges in a specific workbook. When working on a spreadsheet that has thousands, if not hundreds of thousands, of names within it, the Name Manager will require a very substantial amount of memory to perform the deletion, and very often Excel will throw an ‘Out of memory’ message 

However, by using our Excel-Add in OAK you can negate this problem. We will start by generating a list of ALL named ranges (including any hidden names) across the workbook: 

1. With the workbook open, choose the ‘Build database’ option under the ‘Names’ button of the OAK ribbon: 

2. Using the option ‘Output in the same workbook’ followed by the ‘Go’ button, OAK will create two new sheets on the current workbook ‘Names database’ and ‘Overlapping names’.

The ‘Names database’ sheet contains all named ranges (again including the hidden ones):

The ‘Name database’ sheet: 

3. Select all the names from A9 to A<lastrow>

4. Choose OAK / Names / Delete – this will find those names on the workbook and delete them all.

• Read more on Excel: How to compare two Excel worksheets for differences