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 November 16, 2015)

3

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

Creating and Using Standardized Tables

If you have a common table layout that you want to use again and again, you'd benefit by having an easy way to save that ...

Discover More

Normal Words Flagged by Spell Check

What do you do when Word's spelling check marks some common, everyday words as wrong? Here are some ideas of places you can ...

Discover More

Changing Body Text to a Heading

When working on a document in Outline view, you may need to change regular body text to a heading in the outline. It's easy ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Changing Ribbon Tool Defaults

The tools available on the Ribbon allow you to easily format information in a worksheet. If you'd like those tools to default ...

Discover More

Turning Off Insert Options

When you insert rows, columns, or cells in a worksheet, does the resulting Insert Options icon bother you? Here's how to get ...

Discover More

Understanding Manual Calculation

When you make changes in a worksheet, Excel automatically recalculates everything that may be affected by that change. If ...

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 8Mpixels. 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 6 - 4?

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.