Using Names for Constants or Formulas

In addition to using Names to name cells or ranges, you can also name constants or formulas, these constants or formulas don’t have to appear in a cell. This is a good way of storing a constant or a formula that may have to be used several times.

Naming a constant

Imagine having to work out someone’s holiday leave loading. You might have the leave loading in a cell (H6 in the example below), so that you can use this value in a formula. By having the value in a separate cell, if the leave loading rate ever changes you would only have to change the value in H6 and all formulas referring to this cell would update. The formula in this case is =(D5/52)*4*$H$6, i.e. the salary divided by 52 weeks, multiplied by 4 weeks and then multiplied by 17.5%.

By using a name in the formula rather than a cell address, you save space in the spreadsheet and make the formula more readable i.e. what is easier to read?

=(D5/52)*4*$H$6; or
=(D5/52)*4*loading

To give the leave loading of 17.5% (which is a constant) a name you:

1. Open the DEFINE NAME dialog box (INSERT, NAME, DEFINE or CTRL + F3).
2. Give the constant a name i.e. Loading
3. In the REFERS TO: section enter the amount of the constant (without an “=” sign, Excel will add this in for you).
4. Click on ADD, then CLOSE to close the DEFINE NAME dialog box.
5. When creating the formula, use F3 when it is time to refer to the loading, select the name Loading and ENTER to accept the setting (or click on OK), then continue creating the formula in the normal way. (As mentioned previously, you can type the name of the range but if spelled incorrectly, this formula will not work).

Naming a Formula

Take the above example one step further - instead of referring to the leave loading to do the last bit of multiplying, why not put the whole part of working out the leave loading into one name? i.e. Take the cell containing the salary and multiply it by loading. The formula would look like: =Loading

To name a formula you:

1. Open the DEFINE NAME dialog box (INSERT, NAME, DEFINE or CTRL + F3).
2. Give the formula a name i.e. Loading
3. In the REFERS TO: section

• type an “=” sign to start off the formula
• open a left bracket “(“
• in the spreadsheet click on the cell containing the salary (i.e. D5 in the example used previously)
• NOTE: this cell address is ABSOLUTE (i.e. it says $D$5). Use the F4 key to make the reference relative i.e. D5. If you don’t, when you copy the formula to all the other staff members, each formula will refer to Fred Smith’s salary.
• type /52)*4*17.5%
• the formula should read =('NAMING FORMULAS'!D5/52)*4*17.5% (if you used the exercise provided). If you used a new sheet it will look something like =(Sheet1!D5/52)*4*17.5%

    4.   Click on ADD, then CLOSE to close the DEFINE NAME dialog box.
    5.   When creating the formula make sure you are in appropriate cell, then either:

• use F3 to select the name ‘LOADING’ and ENTER to accept the setting (or click on OK), ENTER again or click on the green √ to finish the formula; or
• Type =loading then ENTER or click on the green √ to accept.