How to delete Excel named ranges

Before we jump into how to delete an Excel named range, let’s first look at what they are and why you might use them. Named Ranges can be extremely useful when working with formulas. They allow you to quickly name a cell, range of cells, table or even a function. Once named, these can replace the cell references, making the formulas easier to understand and maintain, 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 Excel named ranges

• Names make a formula clearer and easier to read. For example, the intent of =Revenue-Costs is clearer than =P999-P807.
• This is particularly so when the formula refers to cells in other workbooks. The name formula would still be =Revenue-Costs; the coordinate formula would be =’Profit and loss’!P999-‘Profit and loss’!P807.  In the longer formula, the clutter of worksheet names is particularly impenetrable.
• Relying on Excel to match rows and columns relieves analysts of the need to do the same and eliminates a common cause of error, referring to a cell 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 separate worksheets, of a model.

Arguments against Excel named ranges

• Ordinary spreadsheet users don’t widely understand names.  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 remove named ranges 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 Formulas 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 

Excel named ranges can be created automatically 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, can’t be deleted 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 with thousands, if not hundreds of thousands, of names within it, the Name Manager in Excel will require a very substantial amount of memory to perform the deletion. Also, 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 Excel 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