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

Saving All Open Documents

Got a lot of open documents you are working with? You can save them all at one time by adding a handy tool to your Quick ...

Discover More

Undoing Smart Tag Exclusions

Depending on who you ask, Smart Tags can be really cool or really distracting. If you fall on the "cool" side, you may ...

Discover More

Selecting a Text Block

Word has an interesting way of allowing you to select a rectangular block of text, without reference to what may be ...

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)

Enlarging the Formula Bar

The Formula bar is used to display the formula that appears in a cell. You may want to modify how the Formula bar is ...

Discover More

Controlling the Behavior of the Mouse Wheel

The mouse wheel, by default, controls scrolling vertically through your worksheet. If you don't want the wheel to control ...

Discover More

Displaying Excel's Developer Tab

The Developer tab of the ribbon is the gateway to many advanced features in Excel, including those features related to ...

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 five less than 7?

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.