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

Hiding the Reviewing Toolbar

Does the Reviewing toolbar keep popping up whenever you create a document? Here's a possible reason, along with what you can ...

Discover More

Changing Caption Numbering Styles

Using Word's captioning feature can be a great boon for automatically numbering certain elements in your documents. It might ...

Discover More

Working With Multiple Printers

If you have multiple printers accessible to your computer, you may need a way to quickly print your worksheet on a specific ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

SUMIF Doesn't Recalc Automatically

What are you to do if you suspect that some of your worksheet functions aren't recalculating automatically? Here's some ideas ...

Discover More

Automatically Loading Add-ins

Want to load a particular add-in for use with a specific worksheet? Here's a quick way to do it using macros.

Discover More

Setting the AutoRecover Directory

Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ...

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 seven minus 2?

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.