If you refer to the same value in an Excel sheet often, treat it as a constant.
Anyone who uses Excel, even just a bit, knows how to name a cell or range and then use that name in formulas. It’s easier to remember and enter descriptive and meaningful names than cell references. Formulas and functions are more readable and users are less likely to make errors. What you might not know is that you can name a constant the same way.
A constant is a value that doesn’t change (or rarely changes). Because a constant doesn’t change, you could just enter the value right into the formula. For instance, if you want to determine 10% commission on sales, you could use the formula =Sales*.10.
You might decide to name a cell instead of entering a literal value. The following sheet stores a commission rate of 10% in cell A8. After naming that cell Commission, the formula = B6*Commission returns the total sales for Smith multiplied by 10%. (I've copied the formula to C7:E7.)
That change doesn't seem like a big advantage in this particular case; entering Commission instead of .10 isn’t easier, but ease of use isn’t always the deciding factor. The one advantage to this particular technique is that you can update the value in A8 instead of editing every formula that evaluates the commission value of 10%. But you already know how to do this—I’m not showing any of you anything you don't already know.
There’s a third way to enter a constant value, without actually entering a value into your worksheet. Do so as follows:
- If you're working with the example sheet, delete the 10% value in A8 and the formulas in B7:E7 will return 0 because you deleted the commission percentage value.
- Open the Insert menu and choose Name | Define.
- Enter Commission in the Names In Workbook control.
- Enter .10 in the Refers To control. (If you're working with the example sheet or a previously defined name, delete the cell reference first.)
- Click OK.
If you're using Excel 2007 or 2010 with the example sheet, do the following:
- Click the Formulas tab.
- In the Defined Names group, click Name Manager. (If you're not changing the existing name, Commission, click Define Names to create a new name.)
- Highlight Commission and click Edit.
- Delete the reference in the Refers To control and enter .10 and click OK.
- Click Close.
Once again, the formulas in B7:E7 return 10% of the total sales figure. Now, they're evaluating the cell-less constant, Commission.
This simple trick is a good way to protect or hide a value. It won’t keep someone who really knows what they’re doing from finding the constant and changing or even deleting it, but it will keep most users from making an honest mistake.
TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!