In this tutorial, Simon Hurst guides Excel users on how they can avoid some of the pitfalls of using range names.
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.
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, which reflects the importance of the issue for accountants. In this updated EZ guide, Simon Hurst will also cover:
Replacing a cell reference with a meaningful name - This makes it easier to understand the logic of any formulae that include the cell - such as "VATrate" rather than "B3". To do this, you would 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. Note also that the name would have to be "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.
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 formulate that adjust more automatically to worksheet changes
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.
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
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.