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.
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.
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.
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.
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:
or as shown below:
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.
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.
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!
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: