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.

Introduction
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.

Create Excel range name from selected data
 
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).

Excel range name auto-complete option
 
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.

Excel paste range name dialogue
 
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:

Excel Name Manager
 
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:
=Sheet1!MySheetName

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:

Excel range name Edit name dialogue
 
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:

Using Range Names and ‘Implicit Intersections’
More range name articles and tips

 

 

You may like these other stories...

K2 Enterprises has announced its 2014 technology awards in 27 categories. The only clear message may have been that there was no clear message in a field marked by many good ideas, but no unanimous winners.The company, which...
We're all about QuickBooks this morning. First, read this late-breaking news from John Stokdyk, editor of AccountingWEB (U.K.), who is attending the QuickBooks Connect conference in San Jose, California. Then, for more...
Technology—specifically internet technology—has a record of disrupting tried-and-true methods of operation in ways that we often don't foresee. Look no further than the recent HBO announcement that they *gasp...

Already a member? log in here.

Upcoming CPE Webinars

Oct 30Many Excel users have a love-hate relationship with workbook links.
Nov 5Join CPA thought leader and peer reviewer Rob Cameron and learn ways to improve the outcome of your peer reviews while maximizing the value of your engagement workflow.
Nov 12This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.
Nov 18In this session Excel expert David Ringstrom, CPA tackles what to do when bad things happen to good spreadsheets.