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':
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:
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.
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.
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
- Focus on Excel 2007
- Excel 2007 Tips: Find your way around the new system
- The Excel Compendium - Excel 2007
- Excel 2007 - Get ahead with conditional formatting
- Excel 2007 - A new approach to PivotTables
- Excel 2007 Tables - Not just a pretty format
- Excel 2007 SUMIFS(): The accountant's favourite Excel function gets an overhaul
- Excel 2007: Is the upgrade worth it?
- Office 2007 six months on: Where are the baked beans?
Subscribe to the ExcelZone newswire
To 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.