Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box

Example 1:

Add a formula for calculating the number of the previous year: = YEAR(TODAY())-1
To define a Name that returns the result of a formula:

1. Press Ctrl+F3 for the Define Name dialog box.
2. In the Names in workbook box, type LastYear.
3. Type the formula =YEAR(TODAY())-1 in the Refers to box, and then click OK.

To enter the formula into a cell:

1. Type the = sign, and then press F3 (for the Paste Name dialog box.
2. Select the Name LastYear, and then click OK.

Example 2:
Define a name that stores an exchange rate of 0.82 for 1 Euro vs. 1 Dollar.

To define a Name that stores a value:

1. Press Ctrl+F3 for the Define Name dialog box.
2. In the Names in workbook box, type Euro.
3. Type the formula =0.82 in the Refers to box, and then click OK.
4. Enter a dollar amount in cell A1.
5. In cell B1, type the formula =A1*Euro.

To update a value saved as a Name:

1. Press Ctrl+F3 for the Paste Name dialog box.
2. Select the Name Euro.
3. Change the value of the exchange rate in the Refers to box, and then click OK.
Screenshot // Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box

2 thoughts on “Saving a Frequently Used Formula/Numeric Value in the Define Name Dialog Box”

1. “Great tip! but….
Problem- After saving the name and selecting the same, it adds a space between ‘Last’ and ‘Year’ and results in #NAME? I need delete the space in the formula bar, to correct the nuisance. Any ideas on this?
Thank you!”