Range Names are a lot of fun. Lots of “A1” cell reference people don’t know about the benefit of using Range Names – but with Excel 2007’s new in-your-face Formula Tab with Name Manager, we hope more people will use this feature.
One wrinkle in an otherwise simple explanation of Range Names, is the scope of the Name. You see, there is a global-to-workbook scope and a local-to-worksheet scope. Here is Simon Hurst’s attempt at explaining it:
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
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.
Training Matters has an interesting article on effective use of Range Names – and explains how to replace cell references in pre-existing formulae that didn’t use a Range Name previously
The Define Name option includes the Define Name and Apply Name options. Apply Name is unchanged from previous versions of Excel and allows names to be applied to existing formulae where they will replace any direct references to the named cells or ranges.
It is also possible to create dynamic range names. You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.