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:
Related material in ExcelZone