Although referencing data by name is convenient, it's sometimes more helpful to store a constant value or even a formula, especially if you've been creating custom functions in VBA.
Assume you have a tax rate of 10%,
which you need to use throughout your workbook for various
calculations. Instead of entering
into each formula that requires this tax rate, you can enter the word
TaxRate and Excel automatically will know that
TaxRate has a value of 0.1. Here is
how to do this.
Select Insert → Name
→ Define, and in the Names in Workbook: box, type
TaxRate. In the Refers To: box, enter
=0.1 and then click Add.
From this point on, you can enter any formula into any cell, and instead of adding 10% as part of the calculation, you can use the word TaxRate. Probably one of the biggest advantages to using this method is that if and when your tax rate increases or decreases, and your formulas need to reflect this new percentage, you can select Insert → Name → Define, then select the name TaxRate and just modify it to suit.
To take things a step further with this concept, you can use formulas
as your Refers To: range rather than a cell address or constant
value. Suppose you want to create a
name that, when entered into a cell, automatically returns the
SUM of the 10 cells immediately above it.
Select cell A11 on any worksheet and then select Insert →
Name → Define. In the Names
in Workbook: box, type the name
Total. In the ...