New Range Name features in Excel 2007. By Simon Hurst

What range names are and why they are useful
Excel allows you to apply a name to an individual cell or a group of cells. In fact you can apply names to formulae and constants as well, though this aspect of range names is perhaps less frequently used. Once a name has been applied to a cell or range of cells that name can be used in a formula to refer to the contents of the cell or range. This can make formulae easier to understand: for example =B2*VATRate rather than =B2*$A$2. It is also easy to locate named cells as the names are listed in the name box dropdown, and selecting a name from this dropdown will select the chosen cell or cell range:

For a general introduction to Excel range names, have a look at Gail Perry's article: 10 Quick Range Name Tricks.

New range name features in Excel 2007
Names used to be rather hidden away as an option of the main Insert menu, in Excel 2007 they have their own group on the Formulas ribbon called 'Defined Names':

Define Name
The Define Name option includes the Define Name and Apply Name options. Apply Name is unchanged from previous versions and allows names to be applied to existing formulae where they will replace any direct references to the named cells or ranges. Define Name however does include substantial changes. As well as the abilities to Add a name by entering the chosen name and setting the cell reference to which it relates, and to Delete an existing name, the Excel 2007 New Name screen includes a Comment section that enables you to document your names. In addition, the Scope dropdown allows you to restrict the scope of the name to a particular sheet or to make it a 'global' name available throughout the workbook.

Names must be unique within their scope. So you cannot have two identical names in one sheet, but you can set the same name up in different sheets. This could be very useful – a similar formula on several different sheets could refer to the range name defined for that sheet as shown below:

Related material in ExcelZone

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Thanks

JohnM12 | | Permalink

Thanks. That worked! Also it may be beneficial to others to know that my solution had to NOT have any functions for the chart expressions. All my expressions had to be included in the named ranges. Only after that change it started working.

shurst's picture

Range names in charts

shurst | | Permalink

Hi John - I think you need to apply the names on a series by series basis. So, up to Excel 2003, click on the element in your chart that represents the series you want to base on a name. The series formula will appear in the formula bar, just replace the cell reference with the range name. In Excel 2007 this seems to work better if you use the Series, Edit button from the Select Data Source option of the Chart Tools, Design ribbon. You can go further and use names that refer to dynamic range formulae created using Offset() for example. Have a look at: http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

Can't use ranges in a chart

JohnM12 | | Permalink

Help!
Is there any way to make Excel use my range in a chart for its data, labels, etc? Every time I try, it replaces the range with the explicit sheet cell references. When the data is refreshed programmatically in those other sheets the references break.

shurst's picture

Apply names

shurst | | Permalink

Hi Peter - it's not very obvious. You have to click the Define Name drop down (not the 'button' itself) in the Formulas ribbon, Defined Names group. Apply names is one of the two options there.

Range Name Apply in Excel 2007

pdealwis | | Permalink

Where is Range Name Apply (Excel 2003) equivalent command in Excel 2007?.

Not yet had a chance to play with Excel 2007

AnonymousUser | | Permalink

but nice to see that at long last it has a name manager facility built in.

For those of us mortals on lesser versions of Excel, there is a really excellent Name Manager add-in written by guru Jan Karel Pieterse, available from

http://www.jkp-ads.com/officemarketplacenm-en.asp

Personally I can't live without it. I don't know how it compares with the version built into Excel 07, but I see that he has written an updated version dedicated to 07, so perhaps someone can do a comparison.