Entering Formulas Quickly by Copying and Using AutoFill
In many worksheets, you'll need to enter related formulas in several cells. For example, say you have the worksheet shown in Figure 5–4, which lists a range of products with their prices and sales. Column D needs to show the total revenue derived by multiplying the Units figure by the Price value.
Figure 5–4. When a worksheet needs similar formulas in a column or row, you can enter one formula manually and then use AutoFill or Copy and Paste to enter it quickly in the other cells.
Each cell in column D needs a different formula: Cell D2 needs =B2*C2, Cell D3 needs =B3*C3, and so on. Because the formula is the same except for the row number, you can use either AutoFill or Copy and Paste to enter the formula from cell D2 into the other cells as well.
To enter the formula using AutoFill, click the cell that contains the formula (here, cell D2), and then drag the AutoFill handle (the blue square at the lower-right corner of the active cell) down through cell D5. Excel automatically fills in the formulas, adjusting each for the change in row.
To enter the formula using Copy and Paste, click the cell that contains the formula, and then give the Copy command (for example, press Cmd+C). Select the destination cells, and then give the Paste command (for example, press Cmd+V).
NOTE: If you need to copy a formula to a different row or column but have it refer to the original column or row, create the formula using mixed references. If you need to keep the column the same, make the column absolute (for example, =$B2); if you need to keep the row the same, make the row absolute (for example, =B$2). If you need the formula to refer to the same cell or range always, create it as an absolute reference.