Categorized | excel tips

Named Range – A Few Quickies




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Named Ranges are probably one of the most useful features in Excel. Named ranges can add interactivity, make long formulas shorter and and if used properly, generally provide a clean mechanism to share information across the workbook. I remember being mighty impressed with Peter Rakos 3D rotation model last year and spent a good amount of time trying to understand it. The VBA code is only a few lines with the major work being taken up by the named ranges. (Take a look at the modified version of the file here). Considering how useful named ranges are to mankind (especially the ones who work out of cubicles), I am a little surprised that they have gone without getting much focus on this blog. However today changes that.

So here are some tips that will make working with named ranges in excel faster and more productive.

Creating Multiple Named Ranges in One Go using a Shortcut

Normally if one has to create multiple named ranges from a given set of data, the usual course of action is to type in (or select) the address of a named range and then create one after providing it a proper name. However, in many cases where you already have the data headers present in a worksheet, there’s a far simpler option available to create multiple named ranges in one go.

create-named-range-selection

Say for example, we had a set of data points arranged as shown below and we wanted to create four separate named ranges, one for each column. Rather than creating named ranges one by one, you can choose to use the CTRL + SHIFT + F3 shortcut which opens up the ‘Create Named Range From Selection’ option box. The same can be accessed from the under the ‘Formulas’ tab as shown above. You can now create more than one named range – rowwise, columnwise or both.

shortcut-to-create-multiple-named-range

When you click ok, you can go back and see that four separate named ranges have been created. The column headers are used as names for the named range so created. If needed, you can now go back to the named range namager and quickly edit the addresses.

Accessing the Named Range Manager in Excel

While Excel 2003 used ‘Insert’ -> ‘Name’ -> ‘Define’ to access this option, Excel 2007 + had ‘Formulas’ -> ‘Name Manager’. Another way is to use CTRL + F3.

named-range-manager

Using the OFFSET Function

Named ranges would not be half as interesting (and useful) without the OFFSET function. Offset helps positiion and expand a given range. When used as a part of a named range, the result can be potent dynamic range which has the ability to expand, contract or reposition itself.

using-offset-function-with-named-range

Use Absolute Reference When Working with Named Ranges

To be honest, I don’t know if this is a design feature or a bug but if you use relative reference (A1 insted of $A$1) when defining a named range, it does not stay that way you intended it for long. Let me highlight this with an example. Suppose you wanted to create a named range which OFFSETS cell A1 10 rows downwards. The usual thing to do is to write something like:

=OFFSET(Sheet1!A1,10,0)

or as shown below:

named-range-glitch-1

So far so good. If you had to use this named range in a cell, you would probably pick up a cell (say B1) and enter something similar to = my_named_range

where my_named_range is the name that you gave to the named range you create in the previous step.

using-named-range-in-a-cell

Now let’s do some random activities – copy paste cells, draw a chart etc etc. Now go back and revisit the named range (remember to use CTRL + F3). What do you see? The named range has ‘magically’ changed from =OFFSET(Sheet1!A1,10,0) (the formula that you had entered) to =OFFSET(Sheet1!A2,10,0) (or randomly in other cases to something like =OFFSET(Sheet1!A65536,10,0)). The cell where you entered the named range (cell B1 in our case), still continues to show the correct value but may error out at a later stage.

On the other hand, if one had used absolute reference ($A$1 insted of A1), the named range stays put.

named-range-glitch-2

I once overlooked this phenomenon when working with P&L projections and you should have seen the look on people’s faces when they opened the spreadsheet ;-)

Use F2 to Edit a Named Range

Another useful thing to keep in mind to use F2 when editing named ranges. Try using the arrow key to navigate across a named range formula and see what fine mess happens!

arrow-key-in-a-named-range

Pressing F2 before you use the arrow keys prevents this from happening.

Have got any named range tricks of your own? Care to share?

Here’s an example of named range with data validation. You can also download it by clicking on the button below:

data validation download

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Daniel Ferry wrote:

    Named Ranges in Excel behave exactly as their non-named counterparts.

    To understand what I mean by this you must first realize that Named Ranges are in fact Named Formulas. Why Microsoft did not name them this, is beyond me, but I always refer to them as Named Ranges. To support my thesis, consider that EVERY Named Range must begin with an equal sign (=). The following are all valid named ranges: =5, ={1,2,3}, =Now(), =”excelhero.com”, =SQRT(ROW())-1. That’s five named range definitions that have no reference to any RANGE whatsoever. Once we include a range, the result is still a formula.

    All formulas in Excel are subject to the dollar symbol when copied to a new cell. If you intend for your formula to be relative vertically, then you leave the dollar sign off the row portion of the reference.

    The same must hold true in Named Formulas if they are in fact formulas. Here’s a trivial example. Suppose you have cell B1 selected and you go to the Define Name dialog and create a Named Formula called Col_1 with the Refers To field set to =$A1.
    Now in cell B1 enter =Col_1.

    What SHOULD happen if you copy that cell to B2, or C3, or D4, etc? The formula in the new cells SHOULD always point at column A at whatever row the new cell is. So that covers copying, but what SHOULD happen if you just type the Named Formula into some new cell? Logically, since these are really formulas they must be governed by the same dollar signs for relativity, so typing the formula in a new cell is absolutely no different than copying the Named Formula to that new cell.

    This is one of the design features that makes the Named Formula system so powerful. You can define Col_1 in this way and it will always point to column A. Obviously there’s a bazillion other possibilities as well.

    In your post I believe your “random activities” resulted in moving the cursor so that another cell was selected at the time you re-examined your Named Formula. When you discovered that your Named Formula had “randomly” changed to:

    =OFFSET(Sheet1!A65536,10,0))

    you must have been now viewing it from a cell one row above the row where you created it. Since this formula is relative row-wise once the reference goes above Row 1, Excel wraps around to the last row in the worksheet by exactly the number of rows you are above the row in which you created the original Named Formula.

    So depending on your relativity setting within your Named Formulas (the dollar signs), where (as in what cell) you create the Named Formula is crucial. If a user does not understand this than the results will appear haphazard. When the user realizes that Named Ranges are in fact Named Formulas and that they are subject to the same relativity rules as all formulas, this revelation allows the user to tap into one of the most powerful aspects of the Excel calculation engine.

  2. Daniel Ferry wrote:

    Sorry. In paragraph 2, I meant to say that I always refer to them as Named Formulas.

  3. Ajay wrote:

    @Daniel – I don’t agree with the second part. When I want to look at a (relative) named range from some other cell, I am not editing it – I am just “viewing” it. I would expect it to retain the same structure no matter from whichever cell I am viewing it from. For example, if a user ‘A’ defines a (relative) named range and then sends the spreadsheet over to user ‘B’, who in turn has to make changes, that is a recipe for disaster.

    Regards,

  4. Daniel Ferry wrote:

    @Ajay – In that scenario, I would suggest that user A create a Static Named Formula.

    This behavior is built into Excel and we cannot change it. I’ve completely embraced the notion that Named Ranges are in fact Named Formulas and that those formulas follow the same rules as normal formulas, honoring any and all relativity within the references.

    I find it extraordinarily useful.

  5. m-b wrote:

    In Excel 2007 I prefer converting data into a table. That way you can refer to a column within the table by using the table name followed by the column name in square brackets. For example:

    =SUM(tblRevenue[Amount]))

    Those names are dynamic so you don’t have to worry about adding complex formulas, Excel does all the work for you :-)

    They also come in handy within VBA.

  6. m-b wrote:

    That should be:

    =SUM(tblRevenue[Amount])

    One ) too many :)

  7. Ajay wrote:

    @m-b: Awesome … Superb Tip !! I also noticed that Excel did automatically create a named range using the same name as the table.

    Regards,

  8. JonnyC wrote:

    This website is awesome..seriously..thank you.

  9. steve edwards wrote:

    Why do you always say “Named Ranges”? Surely they are “Names”; identifiers used to store range references, constants and expressions. The Named Ranges thing is a bit of 1980′s legacy from Lotus 1-2-3, where you could only store ranges in Names (and they were always relative!)

    Excel Names can store a great deal more than a mere range reference.

  10. z wrote:

    @Daniel Your first reply was at least as helpful as the original post in understanding “Named Ranges.” I agree that “Named Formulas” more accurately conveys the meaning. Not only that, but the usefulness of this feature is exponentially greater when considering the possibilities of “Named Formulas” as opposed to just ranges. Well done, sir.

  1. Table Formula in Excel (Something I didn’t Know Till Yesterday) | Excel & VBA - da Tab Is On wrote:

    [...] m-b commented that he prefers convert a range to a table and then employ table formulas insted of named ranges. That got me curious enough to explore them further and here’s what I [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel