Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Defined Names > Conclusion
Deze pagina in het Nederlands

Range Names In Excel


This article is focused on giving insight in the power of range names in Excel. Starting off with simply naming a range of cells, continued with the powerful option to put a formula in a name and topped off with showing how to put XLM macro functions to use in defined names, enabling you to do things you can normally only achieve using VBA.

By making proper use of defined names, you can build powerful and easy to maintain Excel solutions.

Name Manager

Creating and editing Range Names in Excel is much easier if you start using the Name Manager!


There are various sites that have extensive information about Excel's capability to define a name that refers to a range of cells or a formula. An instructive one is:

Chip Pearson (English)



Showing last 8 comments of 27 in total (Show All Comments):


Comment by: Jan Karel Pieterse (27-10-2011 04:13:32)

Hi sirplus,

This is a good example of the bug I described indeed.
Strange enough, I get the refersto of the *Global* version of the name, whichever worksheet I select.


Comment by: sirplus (27-10-2011 06:33:53)

very strange
I get the local version no matter what I do.
So by MS definition its not repeatable and therfore not a bug???
Hang on
I can repeat my version & you yours
Lets both submit and see whose gets fixed first


Comment by: sirplus (27-10-2011 06:48:12)

Whichever appears first in the name manager seems to be the one Excel returns..


Comment by: Jan Karel Pieterse (27-10-2011 08:23:50)

Hi sirplus,

You're right!

In both Excel 2003 and 2010, what this line returns:


depends on the position of the worksheets. If the sheet with the locally defined name is the first sheet, then you get the local refersto. If the "local sheet" is NOT the first sheet, you always get the global one.

EXCEPT if you have activated a worksheet which comes before either of the two, which is what our NAme Manager does.


Comment by: Pedro (6-6-2012 05:49:53)

I need a macro to call the Define Name dialog box and wait until de name is input.
The macros allready recorded do not keep the box opened.
Is it possible to send urgent help regarding the subject
Thank you


Comment by: Jan Karel Pieterse (6-6-2012 07:20:06)

Hi pedro,

This line of code opens the dialog:



Comment by: Howard (3-11-2012 01:17:31)

The articles comment that range names may obscure references to cells in other workbooks is a serious drawback to using range names.

Example: WorkbookA has two sheets, Sheet1 and Sheet2. A range name "Stuff" is created to refer to cell A1 of Sheet1. A formula in cell B2 on Sheet2 then references that range name "=Stuff+1". Sheet2 is then moved to another workbook, WorkbookB and WorkbookA is then closed. Cell B2 in WorkbookB still includes the formula "=Stuff+1" instead of ='WorkbookA'!Stuff+1. It is now much more difficult to track down references to other workbooks.

Is there anyway to force excel to fully qualify the references as ='WorkbookA'!Stuff+1?


Comment by: Jan Karel PIeterse (3-11-2012 20:09:47)

Hi Howard,

No, only by typing the reference yourself.

Copying worksheet tabs is not something Excel handles very well, you drag all sorts of rubbish with it from workbook A to Workbook B, like range names, but also styles.

I recommend not to copy worksheet tabs. Instead, create a new tab on the other workbook and copy the cells (formulas). After copying, ensure there are no unwanted external references to sheets of the original workbook in your target workbook.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.