Using Excel Named Ranges

Overview

Any range of cells in Excel can be associated with a name that can be used to reference that range anywhere in the workbook. Naming ranges is particularly useful if you are referencing a particular cell, such as a Start Date on the Options sheet, in several worksheets.

Entering a name like StartDate in the Name Box in the upper left corner of the Excel screen makes referencing the date cell much easier as you design the rest of the report.

You must press Enter after typing in the name, or Excel will not save it.

There is one caveat to using Named Ranges which occurs when you are nesting formulas as parameters for other formulas. An example of this is the IF() function below which outputs "More" if the sum of B5:B10 is greater than 10 and "Less" otherwise:

=IF(SUM(B5:B10)>10,"More","Less")

Because the SUM() function is nested inside of the IF() function, the cell references in the above formula cannot be named ranges, they have to be cell references like they are now.

However, you could still use a named range if you used a separate cell for the SUM function and had the IF() function reference that cell.