Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

New Range Name features in Excel 2007. By Simon Hurst

by
14th Nov 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

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:

Help!
You are also meant to be able use the scope setting to define the same name at local worksheet level and global workbook level. If you refer to the name on a sheet where it has been defined for that specific sheet, then the local reference will be used. If you refer to the name on a sheet that doesn't have that name defined locally then the global workbook reference will be used. If you want to refer to the global name from a sheet that has the same name used locally, then you have to prefix the name with the workbook name, for example: ='2007 range names.xlsx'!profit.

I must admit I had great problems with this – on one workbook I couldn't seem to force Excel to use the global reference, whatever I entered it reverted to the local name.

On another workbook the local/global reference seemed to work as expected but with a small twist. One of the nice features of Excel 2007 is that, as you type in a formula, Excel presents an 'AutoComplete' list where you can double-click one of the displayed options to complete the formula. Where a name is available in two different scopes, both are listed in the AutoComplete list. However, if you choose the 'workbook' variant, Excel incorporates the workbook name as a prefix, but omits the apostrophes that are required when a workbook name includes spaces. This results in Excel creating its own formula that it then reports as invalid.

The apostrophes then have to be added manually: ='2007 range names.xlsx'!profit.

Surely that can't be how it's meant to work can it?

Back to the positives – Name manager
The Defined Names group includes the new 'Name Manager' option:

This allows you to see all the names in use together with their scope and any comment you have entered. There is also a 'Filter' button that lets you display certain categories of names, by scope or just table names for example. You can click on a name and click the Edit button to edit the name.

Paste names
Previous versions of Excel allowed you to paste names into functions or formulae by pressing the F3 button. This still works, and in addition you can use the 'Use in Formula' option to display a list of names and click on the one you want to include. The Use in Formula option also includes a Paste Name option that itself includes a Paste List button that pastes a list of names and associated references into your worksheet.

Quill Bill
It may not be that often that Messrs Gates and Shakespeare co-operate on online help text, but the help for range names in Excel 2007 seems to be a rare example of just that:

Related material in ExcelZone

Subscribe to the ExcelZone newswire
Subscribe to the ExcelZone NewswireTo keep up with all our Excel 2007 news, tips and tutorials, click the graphical button to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

Replies (6)

Please login or register to join the discussion.

avatar
By JohnM12
12th Jun 2009 16:51

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.

Thanks (0)
Simon Hurst
By Simon Hurst
10th Jun 2009 09:08

Range names in charts
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

Thanks (0)
avatar
By JohnM12
09th Jun 2009 18:39

Can't use ranges in a chart
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.

Thanks (0)
Simon Hurst
By Simon Hurst
03rd May 2009 22:15

Apply names
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.

Thanks (0)
avatar
By pdealwis
29th Apr 2009 00:48

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

Thanks (0)
avatar
By AnonymousUser
15th Nov 2007 21:17

Not yet had a chance to play with Excel 2007
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.

Thanks (0)