Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Defined Names > Formula In Name
Deze pagina in het Nederlands

Range Names In Excel

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:

=SUM(B$2:B4)

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.