Partner The Knowledge Base
Share this content

EZ guide to Excel Range Names

28th Sep 2016
Partner The Knowledge Base
Share this content
Working people in the office
istock_seb_ra

In this tutorial, Simon Hurst guides Excel users on how they can avoid some of the pitfalls of using range names.

Objective

Make Excel formulae easier to create and to understand by allocating names to cells or ranges of cells. Understand the Range Name management features introduced in Excel 2007. Avoid some of the pitfalls of using Range Names.

Overview

It is possible to allocate a name to a cell or a range of cells (or even directly to an Excel formula). This name can be used in place of the normal column/row cell reference to make formulae more easily understood and often easier to enter. Although sensible use of Range Names can improve many aspects of an Excel workbook, names can also be used inappropriately. Using too many Range Names can result in a workbook becoming more complex and more difficult to understand.

Before Excel 2007, Excel itself had only basic tools for working with Range Names and those that made full use of Range Names often used a third-party Add-In to help work with and manage names. Excel 2007 introduced its own Name Manager feature. However, third-party Add-ins can still provide additional functionality.

An Excel Zone post from November 2007 is one of the most read Excel posts on the AccountingWEB with approaching 500,000 views to date. This may reflect the importance of the subject matter or perhaps its inclusion of one of the most contrived puns ever to find its way into print. Coincidentally, in the year that marks the 400th anniversary of the death of Shakespeare, said pun makes an unlikely connection between the Excel Range Names, the great bard and Quentin Tarantino. What's in a name? indeed…

Source: Simon Hurst
Source: Simon Hurst

Replacing a cell reference with a meaningful name

Let's take VAT as an obvious example. You may have a workbook that includes many cells that calculate VAT either as a separate value or part of a net-to-gross or gross-to-net calculation. You will have entered the appropriate VAT rate in a cell on one of the workbook sheets. Each time you need to refer to the VAT rate in a formula you will need to click on the appropriate sheet tab and then on the VAT rate cell or, even worse, you could type the reference in. E.g.:

=B23*Data!B3

Someone unfamiliar with the structure of your workbook would need to go to the Data sheet so see what cell B3 contained in order to understand exactly how your formula worked.

As an alternative, you could select cell B3 on the Data sheet and type a descriptive name into the Name Box to the left of the formula bar. Having entered the name, you need to press Enter to accept it, if you just click outside of the Name Box it will not be saved:

Replacing a cell reference with a meaningful name

Let's take VAT as an obvious example. You may have a workbook that includes many cells that calculate VAT either as a separate value or part of a net-to-gross or gross-to-net calculation. You will have entered the appropriate VAT rate in a cell on one of the workbook sheets. Each time you need to refer to the VAT rate in a formula you will need to click on the appropriate sheet tab and then on the VAT rate cell or, even worse, you could type the reference in. E.g.:

=B23*Data!B3

Someone unfamiliar with the structure of your workbook would need to go to the Data sheet so see what cell B3 contained in order to understand exactly how your formula worked.

As an alternative, you could select cell B3 on the Data sheet and type a descriptive name into the Name Box to the left of the formula bar. Having entered the name, you need to press Enter to accept it, if you just click outside of the Name Box it will not be saved:

Source: Simon Hurst
Source: Simon Hurst

You will notice that we have entered the name as VATrate without using any spaces. There are several things that you are not allowed to include in a Range Name and a space is one of them. This table might help explain what you can and can't use as a Range Name:

 

Valid

Invalid

Notes

VATrate, VAT_Rate, VAT.Rate VAT rate No spaces are allowed but underscores and full stops are. A Range Name can start with an underscore but cannot start with a full stop.
VATrate20 20VATrate Names cannot begin with a number but can include a number
  VAT20 Range names cannot be the same as a cell reference. Since Excel 2007 dramatically increased the number of columns to 16,384 many, otherwise valid, Range Names contravene this rule.
  VATrate+, VATrate- Range names cannot include mathematical operators

Having managed to enter a valid Range Name, it can be used in a formula in place of the equivalent cell reference:

Source: Simon Hurst
Source: Simon Hurst

Because the name refers to a particular cell, the formula can be copied to other cells without needing to worry about making the reference to the VAT rate cell absolute rather than relative.

Another of the advantages of using a Range Name is that it will appear in the Excel AutoComplete list as you start typing the name and you just need to double-click on it to include it in the formula. You can also use the F3 keyboard shortcut to display the list of names as you enter a formula – this will work in earlier versions of Excel that didn't include the AutoComplete list functionality.

As well as making it easier to create the formula in the first place, the use of a descriptive name, rather than a cell reference, can make it much easier for someone else to understand what the formula is intended to do.

Easier navigation

Once a Range Name that refers to a cell or block of cells has been created, the cell or cells to which it refers can be selected with only a couple of clicks. The name will be displayed in the Name Box dropdown so, regardless of which worksheet is active, you can click the dropdown and then click on the relevant name to go straight to the worksheet and cells that the name refers to.

Create formulae that adjust more automatically to worksheet changes

External links

Referring to a cell, or range of cells, in one workbook from another workbook or from another Office application, can be dangerous. If the location of the cell in the target workbook is changed, by inserting or deleting rows or columns for example, then, unless it is open when the change is made, the workbook or document that refers to it will not adjust for the change and will be left referring to a different cell. One way to address this issue is to name the cell or block of cells and create a reference to the name rather than using cell references. The 'host' workbook keeps track of where the named range ends up so the external workbook or document will still refer to the correct location.

Generally, when you create a reference to a named cell or range, an Office application will automatically create the reference using the name. However, it is well worth checking the formula once it has been created to make sure this is the case. In some updates of different Office versions references are created to cell references rather than the name and have to be adjusted manually.

In Word for example, the keyboard shortcut Alt+F9 will toggle between showing the result of a linked field and the underlying code.

Cell ranges

Many Excel workbooks include formulae that refer to one or more ranges of cells. Often these ranges need to expand to accommodate added rows. A simple Excel range within a formula will adjust if cells are inserted within the range but will not automatically include data added at the bottom of the range. Although the same is true of a Range Name, there are two significant advantages of using a name rather than a direct cell reference in this case. First, if you have multiple formulae that refer to the same range using direct cell references, all would need updating manually if the range changed. By using a Range Name instead, you would only need to edit the Range Name and all the formulae that used that name would be updated in one go.

The Formulas, Ribbon tab, Defined Names group includes the Name Manager. This will display all existing Range Names and allow the range to which each refers to be edited:

Source: Simon Hurst
Source: Simon Hurst

Here, once we expand the ranges that the names ExpValue and ExpAnalysis refer to, to include the added row 31, then both of our formulae that use those range names will be changed in one go. In contrast, we would need to edit both of the formulae that use direct references individually.

Of course, as covered in the EZ guide to Excel Tables, we could convert our block of cells into an Excel Table and Excel would then use its structured Table names within the formulae enabling auto expansion without the use of any Range Names. However, although this works with ranges in cell formulae, the implementation of structured naming in Excel is far from consistent. For example, Data Validation list sources and Conditional Format ranges do not use structured names.

In these situations, the answer is often to combine the use of Tables and Range Names. If a Range Name is applied to the entire contents (excluding the header row) of a Table column, as the column expands, the Range Name reference will automatically expand. This means that a Data Validation list source could be set to the Range Name and it will expand automatically as new data is added.

Source: Simon Hurst
Source: Simon Hurst

The Name Manager allows you to see all the names in use together with their scope and any comments that you have entered. In addition, it includes a Filter button that lets you display names by whether they are 'scoped' to the worksheet or the workbook, whether they have errors or not and whether they are defined names or table names. You can click on a name and click the Edit button to display the Edit Name dialog for that particular name.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.