Those new to named ranges sometimes struggle to see the benefits of including them in financial models. It’s true that most of the time named ranges are not entirely necessary, but there are some good reasons to use named ranges when building a model:
- It makes 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 =TotalIncome-TotalExpenditure.
- They don’t need absolute referencing. By default, a named range is an absolute reference, so you don’t need to add any in.
- Macros. You can also use ranges in writing visual basic code.
- Linking to external files. It’s best practice to use named ranges when linking to external files. This means that when the source file changes, the data linking to it will automatically update.
- Dynamic Named Ranges. If you need a range of data that will automatically expand or contract (and a structured reference table won’t do the trick) then you can create a dynamic named range by including an OFFSET function within the name manager. This is particularly useful in charts (but not for the faint-hearted!)
In general, named ranges just make your life easier as a modeller. They make your formulas neat and tidy, easier to read and follow. There is no rule to say that you must have named ranges in your model, but any experienced financial modeller will make good use of named ranges in their models.
Extracted from Using Excel for Business Analysis, Chapter 5
What is a Named Range, Anyway?
Excel allows you to select a single cell, or a range of cells, and give it a name. You can then use the name to move to the cells and select them, or include them in a formula.
In Figure 1, we have used an absolute reference to anchor our formula to the consistent price of $450.
Download a copy of this workbook
This cell is called B2, and this won’t change; however, we can also change the name of it to something else.
How to Create a Named Range
- Select the cell, in this example B2.
- Go to the Name box in the top left-hand corner. See Figure 2.
- Type over the name and call it something else, like Price, and press Enter. Alternatively, you can select Define Name from the Defined Names group on the Formulas Tab, but typing over the cell reference in the name box is faster.
Note that the name must not contain any spaces or special characters. For instance, if you want to call it Year 1 Price, you’d need to call it Year1Price or Year1_Price.
Named ranges don’t necessarily need to be confined to only a single cell; you can also create named ranged 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 as shown above.
Finding and Using Named Ranges
Clicking on the drop-down arrow next to the name will show all the defined names in the workbook (as shown in Figure 3). Clicking on the name will take you directly to the cell(s).
Excel will then move to and select the range. It does not matter what sheet you are in when you select the name. This can make finding your way around the named ranges in a model much faster.
Having created a range name, you can now use that name in a formula instead of cell references. For example, if you have created the range name called TotalIncome for the cell containing the total income result, and a range name TotalExpenditure for cell containing the total expenditure result, you can create a formula =TotalIncome- TotalExpenditure (just by typing this text into a cell).
Another example: If you have created the range name Costs for all the cells in a column containing your numerical costs data, you can calculate the total costs using the formula =Sum(Costs).
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 it’s not good practice to have too many. They can be confusing, especially if you have not used a consistent naming methodology. It’s also quite easy to accidentally name the same cell twice. So in order to keep names neat and tidy, you might need to edit or delete them.
Also, if you have added more data to a spreadsheet, and a range name no longer expresses the correct range of cells, you can redefine the named range.
How to Edit or Delete a Named Range
On the Formula tab, in the Defined Names group, click on the Name Manager. Here you can add, edit, or delete existing named ranges.
In Excel 2011 for Mac, go to Insert Name Define.
The only difference between Windows Excel and Excel for Mac in this instance is that you can add comments to named ranges in Windows. Also, it is called a Name Manager in Windows, but otherwise works in exactly the same way as it does in Excel for Mac.
