A Step Further: A Formula In A Defined Name
Up until now, the names in this article referred to ranges of cells. In reality, a defined name in fact just holds a formula (like in a cell!) and you can use any of Excel's extensive list of worksheet functions in names. This opens up a world of possibilities!
Have a look at fig. 12. Here a name called ColumnSum has been defined, using cell B5 as the anchor cell. The principle of using relative references has been used here as well, as shown in a previous section. The formula for ColumnSum is:
Or the sum of all cells as from row 2 down to the row immediately above the cell in which the name has been applied to, restricted to the same column as the name is used in.
Fig. 12: Formula in a name
So when you enter =ColumnSum into cell B5, in reality Excel will compute =SUM(B2:B4) for you (See Fig 13). Similarly when entered into cell D20, Excel calculates =SUM(D2:D19). The advantage of using this technique shows especially with complex formulas. Should you need to change anything to the logic of the formula, then it suffices to just change the formula in the defined name as opposed to having to update multiple cells.
Fig. 13: Named Formula in cell
Another nicety about named formulas, is that they behave as array formulas by default.