To demonstrate this, let’s assume we want to create a name ‘Sales’ referring to $B$6:$I$13 as shown in the example table below but we don’t want to use Excel tables for some reason:

Then, let us open the Name Manager (Ctr+F3), and examine the results:

The reference to the range has an ‘equals’ sign in front which indicates that, it is in fact a formula, rather than range.

Even if we referred to a single cell, the reference would still contain ‘=’.

Therefore, it would be more correct to call this a ‘Named Formula’ … not a ‘Named Range’.

Advanced Named Formulas

We can design formulas of various levels of complexity after the ‘equals’ sign, and even refer to another named formula inside of them.

To illustrate this, let’s create a dynamic range ‘sales_dynamic’, which will automatically grow to include additional records after the product K-500, or additional year columns after 2015 as new data is added.

The formula in the ‘Refers to:’ section could look something like this:

=Sheet1!$B$6:INDEX(Sheet1!$B$6:$Z$1000, MATCH(9.99999999999999E+307,Sheet1!$B$6:$B$1000,1), MATCH(9.99999999999999E+307,Sheet1!$B$5:$Z$5,1))

Explanation (for those who haven’t seen INDEX used this way before):

The two MATCH statements calculate the number of rows and columns that the table has by doing an approximate match on the largest number Excel can calculate in row 5 and then in column B.

The INDEX formula then returns a reference to a range on Sheet1 which starts in cell B6 and extends to this last row and column (assuming they won’t be past column Z or row 1000).

Now let us create named formulas to dynamically calculate the number of rows and the columns:

rows_count:= MATCH(9.99999999999999E+307,Sheet1!$B$6:$B$1000,1
columns_count:= MATCH(9.99999999999999E+307,Sheet1!$B$5:$Z$5,1)

Now, we can replace the ’MATCH’ components of the ‘sales_dynamic’ formula with the ones we have just created to simplify this a little:

Note: the named formulas rely on row 6 and column B to work so these can never be deleted.

Other Examples of Named Formulas

Frequently used Random Numbers

RandomSales     =RANDBETWEEN(0,100000)

Allows you to create random numbers between zero and 100,000

Simplify Complex Formula for your Users

You could also use a named formula to make a very complex formula much more accessible to your users e.g. a complex INDEX/MATCH/MATCH or VLOOKUP could be simplified to =CalculatePrice

This removes the need for your users to understand how to write these formulas.  All they have to do is enter very simple formulas instead (provided these are entered in designated areas that work with the way you’ve designed your named formula)

Named Constants

One last point is, that you can also design constants as a named formula, which would not refer to any range at all.
In the following example, I have added a name ‘gst_rate’ and put 10% in the ‘Refers to:’ section.

You can now use the name anywhere in your spreadsheet as a constant. However, the presence of the ‘equals’ sign before the value indicates, that constants designed this way are still, in fact, ‘named formulas’.

While it is possible to use named constants this way, most of the time, it is preferable to put the constants into cells so they can be more easily accessed and amended.  Named constants can be a useful place to store values you don’t want the user accessing as easily however.