Naming Ranges in Your Excel Financial Model

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.

Understanding why you may want to use a named range in your financial model

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 reasons 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 difficult 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 profit.
• 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 files. When the cell reference in the source file changes (such as when someone inserts a row), the formula linking to it will automatically update, even if the file 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.

How to create a named range in your financial model

To create a named range, follow these steps:

1. Select cell B2.
2. In the Name box in the upper-left corner, 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.
3. 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 formulas. Simply highlight the range instead of a single cell, and type over the name.

Finding and using named ranges

Clicking the drop-down arrow next to the Name box shows all the defined names in the workbook.

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 below, 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.

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 Defined 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 in your financial model

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.