Everything you need to know about Excel range names
Almost since the beginning of time, as measured by AccountingWEB, Excel tips have been popular on both the U.S. and the UK AccountingWEB sites. And of all the myriad Excel tips that have been published on both sides of the Atlantic, none have been more well-read and virtually dog-eared than those about range names. Here is the latest Excel range name Q&A coming out of our UK site, with links to more of our outstanding Excel range name tips at the bottom.
It is possible to allocate a name to an Excel cell or a block of cells and even directly to an Excel formula. Names can be used to make navigation and selection easier and can be used in place of cell references to make formulae easier to understand. Until the 2007 version, Excel’s own tools for managing names were fairly minimal, although third-party add-ins are available. Excel 2007 introduced the new Name manager feature which provided a set of basic tools for managing names.
What’s the quickest way of allocating a range name to a cell or block of cells?
Names can be created by using the Formulas, Defined Names, Define Name option (Excel 2003 menu: Insert,-Name- Define). However, a quicker way to allocate a simple name to the currently selected cell or block of cells is to click in the Name Box to the left of the Formula Bar and type in the name. It is important to press Enter after typing the name – if you just click elsewhere, the name will not be saved.
If you have a block of cells with row and column labels, then you can use the Formulas, Defined Names, Create From Selection option (Excel 2003 menu: Insert-Name-Create) to create a whole set of names in one go. Just select the entire block, including the row and column labels and choose the Create option. You will be able to specify which rows and columns to use to create the names for the other rows and columns. If cell labels contain spaces, the spaces will be replaced by underscore characters and if they contain text that starts with a number they will be preceded by an underscore.
Can I type anything as range name?
Range names cannot include spaces and although they can include numbers, they mustn’t start with a number. A range name cannot be a cell reference. This might sound obvious, but because of the dramatic increase in the number of columns in Excel 2007/10 it’s quite easy to accidentally try and enter a cell reference – for example in Excel 2007 Sal1 or Pur1 are both cell references, though they would be perfectly valid range names in Excel 2003.
How do I include a range name in a formula?
If you know the name you want to use, you can just type it in where needed in the formula. However, names can also be selected from lists of available names.
In Excel 2007/10, when you start typing a name or a function into a formula, Excel will prompt with a list of matching items. The name can be selected from the list by double-clicking or, using the keyboard, by selecting with the cursor keys and then pressing Tab (not Enter or Return which will accept your half-completed formula).
You can also use the’ Use in Formula’ option in the Defined Names group of the Formulas ribbon.
Alternatively, in all recent versions of Excel you can use the keyboard shortcut F3 to display a list of names at any point while entering a formula.
How do I manage my range names?
To review or edit an existing name in Excel 2003 or before you need to use the Insert- Name-Define menu sequence and select the name to be edited from the list. You can then delete it, or change what it refers to in the “Refers To:” box. In Excel 2007/10 Formulas, Defined Names, Name Manager presents a list of names in use together with information about their Scope. Again a name can be selected and then the “Refers To:” changed or the Edit button used:
Selecting and Navigating using names
The Name Box also provides a quick way to select the cells that a name refers to. Clicking the Name Box dropdown will display the full list of cell names in the workbook and clicking on any one will set the selection to that name.
Can I use the same range name on different sheets?
By default, a name will apply to the whole workbook and must be unique within the workbook. However, it is possible to restrict the “scope” a name applies to a particular sheet. In Excel 2007/10 the Define Name dialog and the Name Manager display the Scope and in the case of the Define Name dialog allow you to set it to the Workbook or choose a particular sheet. If the scope of a name is restricted to a sheet, then the same name can be set up for other sheets or for the workbook as a whole. If you use the same name for a workbook scope name as a worksheet scope name, then in the same sheet just entering the name will refer to the worksheet scope name. In any other sheet the name will refer to the global name – to refer to the sheet, you must include the sheet reference:
In Excel 2003 and before, to restrict the scope of a name to a sheet, you must include the sheet name as above as part of the name when it is created.
How do I allocate a formula to a range name without using any cells and why would I allocate a formula directly to a range name?
Although names are most commonly used to refer to Excel cells or blocks of cells, a name can refer directly to an Excel formula. To achieve this, you will need to use the Define Name dialog and enter the formula in the “Refers To” box:
It's a good idea to make sure the formula works correctly in a cell before copying it to the Refers to: box.
You could use this technique to make it easy to include the result of a formula that you want to use frequently throughout your workbook. For example, the screen above shows a formula that will return a formatted version of the previous month end date.
In some cases, the use of a name referring directly to a formula is the only way to achieve what you want. For example, if you need to link a chart series to a block of cells that dynamically expands as you add more data, you can create a range name that uses the OFFSET() and COUNTA() functions in a formula and then use this as the data source for the chart series. Excel will not accept the formula itself being used directly as the data source.
Although this technique can be very useful in some situations, if would be a good idea to include details of all such names in the spreadsheet documentation. Not everyone may be aware that names can refer to anything other than cells and this could cause confusion when they can’t find the name in the Name Box list, although it will appear in the Name Manager or Define Name screen.
How do I find out more about named ranges?
For further information and ideas have a look at:
Voice of the Editor
Which isn’t completely true. I mean, occasionally I drop by when I manage to sneak out of the nonstop frat party over at Going Concern, but I’m mostly a wallflower over there. I’m happy to say that I’ve been given express permission (or explicit orders, if you like) to wander over here to AccountingWEB more often.
Why is that, you might ask? My job is to replace the irreplaceable Gail Perry as Editor-in-Chief. What does that mean? I don’t really know! I think it’ll be fun getting a feel for things, throwing in my own thoughts here and there, and listening to the discussions you’re having about the accounting profession.