How to Assign / Define Names To Formulas in Excel – Use Formulas Easily


Formulas are very much useful in Excel Sheets to calculate complex equations easily. Microsoft Excel has no way to paste commonly-used formulas. You can make your formulas more accessible by assigning names to your formulas. This tip will let you use any of your excel formulas by just using the defined name in the space of those long formulas. It can be done in the following manner..

  • Enter your formula.
  • Select the cell containing the formula and press [F2]. This brings Excel to Edit mode.
  • Hold down [Shift] as you use the cursor control keys to select the entire formula, including the equals sign at its beginning.
  • Press [Ctrl] + [C].
  • Then press [Esc] to get out of edit mode.
  • The cell with the formula is still selected. Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box.

Assign name to excel formulas

  • In the Names in Workbook box, enter the name you want assigned to the formula. Select whatever is in the Refers To box at the bottom of the dialog box, and press [Ctrl] + [V]. The cell reference is replaced with the formula on the Clipboard.

You need to make sure there are no dollar signs in the formula. If there are, select and delete them. This method of using formulas, therefore, does not work well with absolute references. Now, whenever you want to use the formula, you simply enter an equal sign and the name you gave to the formula.

Even though the name shows in the cell, the formula assigned to the name is actually used in doing the calculation. Since the formula uses only relative references since you got rid of the dollar signs, it is always relative to where you use the name in the worksheet.

Check out other tips and tricks about Microsoft Excel

Filed under: Tips n Tricks
Tags: , , , ,
October 17, 2009 by: Prasanth Chandra

Leave a Reply