You can create names that are defined by constants and formulas instead of by cell references.

You can use absolute and relative references, numbers, text, formulas, and functions as name definitions.

For example, if you often use the value 8.3 percent to calculate sales tax, you can choose Insert, Name, Define, type the name Tax in the Names In Workbook box, and then type 8.3% (or .083) in the Refers To box. Then you can use the name Tax in a formula, such as =Price+(Price*Tax), to calculate the cost of items with 8.3 percent sales tax.

Note that named constants and formulas do not appear in the Name box in the formula bar, but they do appear in the Define Name dialog box.

You can also enter a formula in the Refers To box. For example, you might define the name Price with a formula, such as =Sheet1!A1*190%. If you define this named formula while cell B1 is selected, you can then type =Price in cell B1, and the defined formula takes care of the calculation for you. Because the reference in the named formula is relative, you could then type =Price into any cell in your workbook to calculate a price using the value in the cell directly to the left. If you enter a formula in the Refers To box that refers to a cell or range in a worksheet, Excel updates the formula whenever the value in the cell changes.

Using Relative References in Named Formulas

When you are creating a named formula that contains relative references, such as =Sheet1!B22+1.2%, Excel interprets the position of the cells referenced in the Refers To box as relative to the cell that is active when you define the name. Later, when you use such a name in a formula, the named formula uses whatever cell corresponds to the relative reference. For example, if cell B21 was the active cell when you defined the name Tax as =Sheet1!B22+1.2%, the name Tax always refers to the cell one row below the cell in which the formula is currently located.