A Range Name Is Really A Named Formula
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:
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.
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:
After defining this name, you can use a formula like this, without array-entering it:
Excel is a complex program, and has been around for a long time. Consequently, it has many obscure nooks and crannies to discover. Some of them are described here.
Keep in mind that some versions of Excel are odder than others. In other words, the things described here may not apply to all versions of Excel.
All Odd Stuff
Browse Oddities by Category
Seen Something Odd?
If you've discovered something weird about Excel, let me know.