Copying and Pasting Formulas

You can copy and paste formulas just as easily as you copy and paste data entries. When you paste a formula, however, Excel adjusts the cell references in the formula to reflect their new positions in the worksheet. Figure 6.3 illustrates how Excel treats cell references when you paste a formula. Cell B8 contains the formula =B4+B5+B6+B7, which determines the total sales revenue for the Northern District. If you copy that formula to cell C8 (to determine the total sales revenue for the Southern District), Excel automatically changes the formula to =C4+C5+C6+C7. This is usually what you want Excel to do when you copy or move formulas.

Figure 6.3. Excel adjusts cell references when you copy formulas.

Figure 6.3 shows a formula in which the cell references are relative: Excel changes the cell addresses relative to the position of the formula. In this example, the formula was moved one cell to the right, so all the addresses in the formula are also adjusted one cell to the right.

Sometimes, however, you might not want Excel to adjust a cell reference. Say, for instance, that your worksheet has a formula that includes a reference to a cell that contains the state sales tax percentage. You want that cell reference to remain unchanged no matter where you move that formula or paste a copy of it. In such a case, you can mark the cell address in the formula as an absolute reference to prevent Excel from changing the address when you copy or move the formula to another cell.

To mark a reference as an absolute, press the F4 key immediately after typing the reference, or move the insertion point inside the cell reference and press F4 . When you press this key, Excel places a dollar sign before the column letter and the row number, as shown in Figure 6.4. You can type the dollar signs yourself, but letting Excel do it is usually easier.

Figure 6.4. To prevent a cell reference in a formula from changing, mark it as an absolute cell reference.

You also can mark the column letter or the row number (but not both) as absolute. Doing so enables the column letter or row number to change when you copy or move the formula. Keep pressing F4 until you have the desired combination of dollar signs or type the dollar signs in the cell reference.