Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Turning Off Names.

Turning Off Names

by Allen Wyatt
(last updated January 4, 2019)

7

Excel includes the capability to convert cell references (such as B2 or C7) to names you have defined within a document; exactly how you do this has been covered in other issues of ExcelTips. There is no inherent command that will convert from named references back to cell references.

Unfortunately, if you try to delete names already defined in a workbook, Excel simply replaces the results of formulas referencing the name with the #NAME? error. The only way to switch back to cell references is to edit each of the formulas that reference a name and replace the reference with a cell reference.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11195) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Turning Off Names.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding Add-Ins

The primary way to extend what Excel can do is through the use of add-ins. This tip explains what they are and the ...

Discover More

Getting Rid of Workbook Links

Excel allows you to easily link information from one workbook to another. If you want to get rid of links that may be ...

Discover More

Placing Numbers Over Other Numbers

Sometimes you need to create text that isn't as "linear" as you might expect. For instance, you may need to put some text ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Turning Off Display of Zeros for All Worksheets

Some people like zero values displayed; others do not. Excel allows you to easily turn the display on or off for a single ...

Discover More

Turning Off Error Checking

A little green triangle in the corner of a cell means that Excel thinks there is an error with the cell contents. If ...

Discover More

Displaying Page Breaks

Page breaks can be added to a worksheet manually or automatically. If you want to see where Excel places page breaks, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight less than 8?

2019-01-04 23:32:17

Col Delane

Neil: An ever-changing range is no reason NOT to use a Defined Name - it just means you need to create a Name that dynamically changes its dimensions to match the underlying data in the target range.

As John suggests, this can be done by converting the data in the matrix to an Excel Table - though I would still create a separate Name that references the target column within the Table and then use that Name in my formulas.

Alternatively, create a Name using functions such as OFFSET and COUNTA in the RefersTo formula (after all, Names are really just named formulas) to dynamically adjust the range it references. Search the web for "Excel dynamic range name" to discover how to achieve this.


2019-01-04 14:49:25

John F. Murray

I've gotten to whenever possible I actually convert my data to a table. This way as the rows in the table increase (or decrease) using the table name+column as reference keeps all things working.


2019-01-04 11:36:55

Neil

One reason to not use named ranges is when you have a continually growing list that is named and the list outgrows the limits of the named range, potentially leading to data being missing from calculations. This condition is more apparent and easier to see when the range is shown right in the formula rather than having to look in the Name Manager. This weakness can be managed by making the range longer, if possible, than you can imagine ever needing.


2019-01-04 06:40:28

Mark

This tip immediately brings up in my mind a bigger question. Why would you ever want to NOT use names? Named Ranges/cells is one of the most underutilized power tools in Excel. I've got one particular workbook that uses Names and all the formulas can easily be read like any standard math equation. With cell references they're just so much gobbledygook.


2017-06-24 10:01:11

Pieter de la Court

There is a solution for this using VBA. The trick is to replace the range names in the formulas by the range addresses before deleting the names. The macro could look something like this:

Sub RemoveNames()

Dim Rng As Variant 'a named range
Dim Nme As String 'the name of the range
Dim Addr As String 'the range address to be used in the formula
Dim Sh As Variant 'a sheet
Dim Sheetnm As String 'the sheet name

For Each Rng In ActiveWorkbook.Names
Nme = Rng.Name
For Each Sh In ActiveWorkbook.Sheets
Sheetnm = Sh.Name
Addr = Trim(Mid(Rng, Len(Sheetnm) + 3, 50))
Sheets(Sheetnm).Cells.Replace What:=Nme, Replacement:=Addr, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next Sh
ActiveWorkbook.Names(Nme).Delete
Next Rng

End Sub


2015-07-01 07:56:36

Simon Graffe

Before deleting the name, look up the address for the defined name in the name manager. Use search and replace to replace the name of the range in the formulas that use it. Then delete the defined name


2014-01-30 05:40:46

Jackiez

Thank you


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.