New article: Defined Names In Excel

Hi everyone,

About a year ago I published an article in a Dutch magazine about using defined names in Excel.

I thought it was high time to translate that article into English and publish it on my site.

Here’s the introduction of the article:

Defined Names In Excel

In Excel you can give a range of cells a name. When you do that, you can use this name instead of the address to point to that range. In general it is easier to remember a name than a range address of the start and end of a range of cells.

Using names has a couple of advantages: ranges are easier to find, formulas are simpler to understand and maintenance of the spreadsheet model becomes more reliable.
Furthermore, some thingsin Excel can only be achieved bij using defined names. But much more is possible with defined names than just naming a range of cells. Because you can also put a formula into a name, a whole world of possibilities opens up!

Even more so, since you can also put the old Excel 4 macro functions to use in a defined name. This enables you to do things normally only possible using VBA.

And here’s the TOC:

Introduction
How To Define Range Names
How To Use Range Names
Absolute And Relative Addressing
The Context Of Names
Special Names
A Step Further: A Formula In A Defined Name
Dynamic Names
Another Step Further: XL4 Macro Functions In Names
Passing Arguments To A Defined Name Formula
Bugs in Excel’s Name Object
Conclusion

Go and read it, then tell me what you think of it!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Posted in Uncategorized

21 thoughts on “New article: Defined Names In Excel

  1. Thanks Jan
    This is great explanation of the difined names
    glad you took the time to translate

  2. Thanks Jan for the links and for Name Manager

    I think though that there is a debate as to whether range names aid spreadsheet transparency. I recently reviewed a business valuation that was driven by 5000 range names, scenarios and macros. Its an extreme example but the range names obfusctaed the auditability and layout of the model

    I’ve found that basic Excel users generally prefer to see formula addresses than range names as range names are an additional skill that lie outside their comfort zone. If applications are being developed for a wide audience at our company, our business evaluation group recommends that range names be avoided and that calculations should be broken up into readable rows that can be read in logic chunks without delving into formulas (ie show tax, foreign exchange, pricing, inflation etc line by line) rather than in one cell.

    While accepting that range names offer more than just range addresses, most models won’t have to use Excel 4 macros, range named constants & formulas etc to do their job

    I realise that there will be very strong opinions either way on this. In a recent poll elsewhere of mainly experienced VBA modellers, using range names found favour with 65% of the respondees.

    I’ve found Name Manager invaluable for cleaning up the clutter from erroneous and external range names, thanks for your great work and updates on this

    Cheers

    Dave

  3. Weighing in on range names (and wrt Dave’s obviously well reseached perspective), I wouldn’t code without them. I have used everything in Jan’s article exept XL4 macros and relative range names (which I am planning to try).

    Something I have stumbled on recently is the use of ‘parsed’ names (eg. “HideRng.CTL1?, “HideRng.CTL2?). I find this very useful for organizing the naming conventions and also for supporting iterative code loops which use names.

  4. Dave –

    One main purpose for the names in my projects is dynamic names, which move or resize in response to other parameters. Another is naming cells or ranges that I call in VBA, since it’s easier to move a range definition in Excel than find all the possible places I’ve referenced a range address in my code. (I don’t know what’s wrong with the other 35% of the experienced VBA modelers.)

    Jan Karel’s Name Manager is one of my favorite Excel UI enhancements, perhaps second only to Rob Bovey’s Chart Labeler.

    – Jon

  5. Hi Jon,

    Yep, dynamic ranges are a great weapon in the power users armoury, especially for charts. Using XL4 macros offers some marvellous possibilies to the programmer/power user as well. Etc Etc

    My concern – which I readily acknowledge is in the minority – is that range names are not best pratice when designing models for widespread use. Maybe ok for a locked down customised app, but not a model that many users may need to understand and modify.

    Name Manager, Find Links and Smart Indenter are the three addins I consider indispensable.

    Cheers

    Dave

  6. Unless I missed it …

    … the article omits one very important area: how to define local names from the worksheet.

    1) Highlight the range you want to name
    2) Click on the Name Box
    3) Type in SheetName!LocalName
    – SheetName is the name on the tab of the current worksheet
    – LocalName is the name you want to give the range
    – “!” is just that (bang?)

    I always use local names unless I have a compelling reason not to. It’s curious why even in Excel 2002 the Define Name dialog does not offer the option.

    M

  7. Dave,

    I agree with most of what you have written. For an average user, range names are not very straighforward.

    And when you want a model that a lot of people can understand, splitting the calculations accross multiple columns/cells is a good way to make it transparent.

    But when building models, I do prefer to use them, you know the arguments.

    Regards,

    Jan Karel.

  8. for me, i use a mixture of names and address refs, depending on the nature, and situation. Each have thier pro’s and cons. You can design a model that is “easy” to follow useing both, or you can design one thats hard to follow using both.

  9. My experience with designing models for widespread use is that it’s best to hide lots of things (hidden names, hidden sheets) and lock it down, because the widespread users can really screw things up when they think they understand what’s going on.

    I had one guy call me demanding the password to a workbook. I told him all he had to do was select his department from the dropdown and enter data in the yellow cells. No, he wanted to enter the data directly into the table in the hidden sheet. After a long series of discussions with him, and after a number of broken models, his manager had to get involved. Sheesh!

  10. I get your point Jon. You’re right about hte hiding and locking down in most cases, except in the case where it is the model itself the users need to be able to understand. In that case, you just need to make sure they can follow the methods.

    Whether it is by using range names or by using direct references doesn’t really matter, you just need a way to convey to the user what’s being done and how that is achieved.

  11. I would think with the prevalence of the internet, the ignorance excuse could only be understood as being indifferent (ignorance is curable; indifference is terminal). Anyone working for me and saying “I don’t know” very often, would soon find themselves looking elsewhere for employment.

    While I don’t know everything there is to know, even about named ranges, I know what I need to know for now and if something comes up I don’t know, I will take a few minutes to find out. That is why sites like this one are so invaluable and greatly appreciated.

    The phrase ‘comfort zone’ should be striken from an employees lexicon. Knowledge is just too easy to acquire now for ignorance to be anything but loser’s koolaid.

  12. Helpfiles and other types of documentation can easily support maintance etc of any kind and also communicate models and methods in use.

    In my part of the world it’s called system-documentation ;)

    BTW, well done Jan Karl!

    Kind regards,
    Dennis

  13. Jan-

    Nice article. I have one observation and one question.

    Observation: A dynamic named range created using the OFFSET function will not work with certain other Excel functions. Example the INDIRECT function. In a thread I created on this blog with Dick K. we learned that this was true and how OFFSET works. I also found with further testing that a dynamic named range using the OFFSET function that is being used as link between two files will not work unless both files are open.

    Question: Do you some reference to the GET. functions? I can’t find references on how GET works. It sounds powerfull and I would like to use more of it. Maybe some one has a reference to another web site or thier own site to explain the meaning of the GET. functions.

    Thank you.

    Charlie III

  14. Hi Charlie,

    Could you point me to the discussion you refer to?

    On the Conclusion page I added a link to a downloadpage where you can get the XLM helpfile.

  15. See Dick.s archive of 3/1/05 INDIRECT and named Ranges.

    I saw your link. Thanks. I will give the english version a try.

    Charlie

  16. Just a note of congratulations to Jan Karel Pieterse and Charles Williams on their fine work with the Name Manager. When I work with spreadsheets, I always use Charles Williams’ Fast Excel with the Name Manager built in. I always use range name whenever possible, for it reduces errors and allows to more quickly understand the formulas.

  17. Admittedly, I only skimmed thru it. Appears to be a good writeup on the positive aspects of range names. It’s a bit typical in that it doesn’t point out any of the pitfalls… (but perhaps I missed the warnings due to skimming, apologies if that’s the case)

    Yes, they have their uses. VBA refs are particularly helpful. I typically recommend prequalifying those names with “VBA_”. It’s nice to know at a glance which ones are being used in code.

    However, I see at least 3 warnings that should be issued when recommending named ranges.

    Prolific use of defined range names in formulas “may” (or for accountants, “will likely”) lead to:

    * More complicated auditing
    * Can breed unwanted external links and ghost links
    * Can limit or in some cases even disable formulas when names become too long (as you know, there’s a limit to how long a cell formula can be)

    …other than that, yeah they’re great use em if you like.

    Personally, I don’t find myself using a lot of them. Especially when it comes to straight formula references. I’m not saying I NEVER use them. I might use 5-10 at most for cell formula refs in a model. But to see like 50 or more in a model (for formula reference) would be too much in my opinion. More commonly I’m using them mostly for VBA references.

    There are some auditing benefits to using range refs. Those who follow the convention of using same columns from sheet-to-sheet for same years understand the benefit better than most. Knowing whether the cell is above/below a formula cell can be helpful. For auditing sometimes it’s even helpful to just know the data is reading from a certain sheet. These things are masked by defined names.

    I’ve debated the topic too often to want to post repeatedly on the topic again so this is going to be my only commentary.

    You all can have the last word…

    Regards,
    Aaron

  18. Jan Karel Pieterse,

    You are most welcome. I added a post on my weblog that recommends both your and Charles’ software programs.

    Best regards,
    Kevin


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.