Tips for Working with Formulas

In this section, I offer a few additional tips and pointers relevant to formulas.

Don’t hard-code values

When you create a formula, think twice before you use any specific value in the formula. For example, if your formula calculates sales tax (which is 6.5 percent), you may be tempted to enter a formula, such as the following:

+A1*.065

A better approach is to insert the sales tax rate in a cell—and use the cell reference. Or you can define the tax rate as a named constant, using the technique presented earlier in this chapter. Doing so makes modifying and maintaining your worksheet easier. For example, if the sales tax rate changed to 6.75 percent, you would have to modify every formula that used the old value. If you store the tax rate in a cell, however, you simply change that one cell—and Excel updates all the formulas.

Using the Formula bar as a calculator

If you simply need to perform a calculation, you can use the Formula bar as a calculator. For example, enter the following formula—but don’t press Enter:

=(145*1.05)/12

If you press Enter, Excel enters the formula into the cell. But because this formula always returns the same result, you may prefer to store the formula’s result rather than the formula itself. To do so, press F9—and watch the result appear in the Formula bar. Press Enter to store the result in the active cell. (This technique also works if the formula uses cell references or worksheet functions.)

Making an exact copy of a formula

When you copy a formula, Excel adjusts its cell references when you paste the formula to a different location. Sometimes, you may want to make an exact copy of the formula. One way to do this is to convert the cell references to absolute values, but this isn’t always desirable. A better approach is to select the formula in Edit mode and then copy it to the Clipboard as text. You can do this in several ways. Here’s a step-by-step example of how to make an exact copy of the formula in A1—and copy it to A2:

1.	Double-click A1 (or press F2) to get into Edit mode.
2.	Drag the mouse to select the entire formula. You can drag from left to right or from right to left. To select the entire formula with the keyboard, press Shift+Home.
3.	Choose Home Clipboard Copy(or press Ctrl+C). This copies the selected text (which will become the copied formula) to the Clipboard.
4.	Press Esc to get out of Edit mode.
5.	Select cell A2.
6.	Home Clipboard Paste (or press Ctrl+V) to paste the text into cell A2.

You also can use this technique to copy just part of a formula, if you want to use that part in another formula. Just select the part of the formula that you want to copy by dragging the mouse, and then use any of the available techniques to copy the selection to the Clipboard. You can then paste the text to another cell.

Formulas (or parts of formulas) copied in this manner won’t have their cell references adjusted when they are pasted to a new cell. That’s because the formulas are being copied as text, not as actual formulas.

Converting formulas to values

If you have a range of formulas that will always produce the same result (that is, dead formulas), you may want to convert them to values. If, say, range A1:A20 contains formulas that have calculated results that will never change—or that you don’t want to change. For example, if you use the RANDBETWEEN function to create a set of random numbers and you don’t want Excel to recalculate those random numbers each time you press Enter, you can convert the formulas to values. Just follow these steps:

1.	Select A1:A20.
2.	Choose Home Clipboard Copy (or press Ctrl+C).
3.	Choose Home Clipboard Paste Values.
4.	Press Esc to cancel Copy mode.