Naming Ranges
Many financial modelers like to include named ranges in their models. Named ranges are just a way of naming a cell, or a range of cells, to use it in a formula, instead of using cell references.
In Figure 6-5 earlier in this chapter, I used an absolute reference to anchor the formula to the consistent price of $450. This cell is called B2, and it won’t change. However, I can also change the name of it to something else, such as “price.” That’s what a named range is.
Understanding why you may want to use a named range
You don’t have to include named ranges in a financial model, and some of the best financial models don’t use them at all. Those who haven’t used them before
sometimes struggle to see the benefits of including them in financial models. Most of the time, named ranges aren’t really necessary, but there are a few rea- sons why you should consider using them in a financial model:
» Named ranges can make your formulas easier to follow. A formula containing lots of cell references can be confusing to look at and diffi to edit. But if the cell references are replaced by a range name, it becomes much
easier to understand. For example, the formula =SUM(B3:B24)-SUM(F3:F13) could be expressed as =SUM(Revenue)-SUM(Expenses) to calculate profi
» Named ranges don’t need absolute referencing. By default, a named range is an absolute reference, so you don’t need to add any in.
» Using named ranges is ideal when you’re linking to external fi s. When the cell reference in the source fi changes (such as when someone inserts a row), the formula linking to it will automatically update, even if the fi is
closed when the update is made.
» If you decide to use macros in your model, you should use named ranges when referring to cell references in the Visual Basic code. As with external links, this practice is more robust than using cell references.
In general, named ranges just make your life easier as a modeler. They make your formulas neat and tidy, easier to read and follow. You aren’t required to use named ranges in your model, but you should know what they are and how to edit them if you come across named ranges in someone else’s model.
To create a named range, follow these steps:
-
Select cell B2.
-
In the Name box in the upper-left corner (see Figure 6-9), type over the name and call it something else, like Price.
Note that the name you type must not contain any spaces or special characters. For instance, if you want to call it “Year 1 Price,” you need to name it “Year1Price” or “Year1_Price” or something along those lines.
-
Press Enter.
Named ranges don’t necessarily need to be confined to a single cell; you can also create named ranges for an entire range of cells, and these can be used in formu- las. Simply highlight the range instead of a single cell, and type over the name.
FIGURE 6-9:
The Name box.
Finding and using named ranges
Clicking the drop-down arrow next to the Name box shows all the defined names in the workbook, as shown in Figure 6-10.
FIGURE 6-10:
Finding a named range using the Name box.
Clicking the name in the drop-down box will take you directly to select that cell or range of cells included in the named range automatically. It doesn’t matter what sheet you’re in when you select the name. This can make finding your way around the named ranges in a model much faster. You can also press Ctrl+G to bring up a dialog box with all the names, or press F3 to paste names.
After you’ve created a range name, you can use that name in a formula instead of cell references. In the example shown in Figure 6-11, you can create the named range Price for cell B2 and the named range Units for the range A3:A7. In cell B3, you can use the formula =Price*Units to calculate the price, and then copy it down the column, as shown in Figure 6-11.
FIGURE 6-11:
Using named ranges in a formula.
You can use a named range in a formula in several different ways:
» Simply type =price in a cell.
» Type = and then select cell B2 with the mouse to pick up the name of the cell.
» Press F3 and then double-click the name to paste it into a cell.
» Select the Formulas tab on the Ribbon and, in the Defi Names section, select the name you want to use from the Use in Formula drop-down list.
If you’re planning to use named ranges in your model, create them first, before you build your formulas. Otherwise, you’ll need to go back and rebuild your formulas to include the named ranges.
A cell does not need to be an input field in order to assign a name to it, although it often is in financial models. The cell can also contain a formula as well as a hard-coded input value.
Named ranges can be useful, but you don’t want to have too many. They can be confusing, especially if you haven’t been consistent in your naming methodology. It’s also quite easy to accidentally name the same cell twice. So in order to keep names neat and tidy, be sure to use the Name Manager to edit or delete any named ranges that are no longer being used. Note that copying sheets into a model can copy named ranges, which can also contain errors as well as external links you’re not aware of. This can slow down the file, so it’s a good idea to look through the Name Manager every now and then to tidy it up.
Editing or deleting a named range
You can manage all the named ranges you’ve created in the Name Manager, which can be found in the Defined Names section on the Formulas tab on the Ribbon. It’s easy to create a named range and forget it’s there, so try to keep your names tidy. If you need to remove a named range or find that you’ve accidentally named the wrong cell, you can add, edit, or delete existing named ranges in the Name Manager.
