A Range Name Is Really A Named Formula

Category: General Weirdness | [Item URL]

Excel users often refer to named ranges and named cells. This terminology is not quite accurate. When you create a name, you are actually creating a named formula -- a formula that doesn't exist in a cell. Rather these named formulas exist in Excel's memory.

When you work with the Define Name dialog box, the Refers to field contains the formula, and the Name In workbook field contains the formula's name. You'll find that the contents of the Refers to field always begin with an equal sign -- a sure indication that it's a formula.

As you can see in the figure below, the workbook contains a name (InterestRate) for the cell B3 on Sheet3. Whenever you use the name InterestRate in a formula, Excel actually evaluate the formula with that name and returns the result.

If you understand this concept, you'll realize that you can create more complex (and useful) names. For example, you can create a name called ThisMonth (see below) which doesn't refer to any cells.

After this name is defined, entering the following formula will display the name of the current month:

=ThisMonth

Furthermore, named formulas are treated as if they were array-entered. Consider, for example, the following array formula, which returns TRUE if A1:A11 is in sequence, and FALSE if not.

=AND(A2:A11>A1:A10)

This is an array formula, so it must be array-entered with Ctrl+Shift+Enter.

Now, created a named formula, say InSeq, which is defined as follows:

  =AND(Sheet1!$A$2:$A$11>Sheet1!$A$1:$A$10)

After defining this name, you can use a formula like this, without array-entering it:

=InSeq