Copying Formulas

ANOTHER THING THAT’S COOL about Excel’s formulas is how easy it is to reuse them. For example, after you carefully type in a formula that calculates the number of black bean taquitos available for sale during August, taking into account any deliveries or returns during the month, you don’t want to repeat the process with the next item in your inventory. Luckily, Excel allows you to copy formulas, automatically adjusting the cell references for you. For example, if you copy that formula for black bean taquitos to the jalapeño-apple salsa row, the cell references are automatically adjusted so you calculate the adjusted opening inventory for salsa and not taquitos. In Figure 2-8, the formula in cell E12, which calculates the adjusted opening inventory for taquitos, is =B12+C12−D12. When copied to cell E15 (the salsa row), the formula is adjusted so that it uses similar cells in row 15 instead: =B15+C15–D15.

Figure 2-8. When formulas are copied, cell references are automatically adjusted to reflect the formula’s new location.

[View full size image]

Copying with AutoFill

Typically, you will want to copy formulas to nearby cells so they can use similar data. For example, in Figure 2-9, you might type the formula for September total expenses in cell B11, and then copy that formula across row 11 to the other month columns. The simplest way to copy formulas to adjacent cells is to use AutoFill. With AutoFill, you simply drag from the source cell to copy its data to adjacent cells. In this case, you will copy a formula from one cell to nearby cells, simply by dragging over them with something called the AutoFill handle.

Figure 2-9. Use AutoFill to copy formulas to adjacent cells.

[View full size image]

Follow these steps:

Copying with Copy and Paste

AutoFill is perfect to use if you want to copy formulas to adjacent cells. But what do you do if the cells are not next to each other? Using the Copy and Paste commands, you can copy a formula anywhere in your worksheet—even from one worksheet or workbook to another. Follow these steps:

1.	Click the cell containing the formula you want to copy. A dark border appears around the cell, indicating that it’s the active cell.
2.	Click the Copy button on the Home tab to copy the formula, or press Ctrl+C. A dashed “marching ants” border appears around the formula cell, as shown in Figure 2-10. Figure 2-10. Marching ants surround the cell(s) to be copied. [View full size image]
3.	Click the cell to which you want to copy the formula. If you want to copy the formula to multiple adjacent cells, you can drag over them; to copy the formula to a series of non-adjacent cells, click the first cell, press and hold Ctrl, and click each additional cell. Each cell you select is highlighted.
4.	Click the Paste button on the Home tab or press Ctrl+V to copy the formula to the selected cell(s). The cell references in the original formula are adjusted to reflect the new column/row to which the formula is copied.

Copying Values Instead of Formulas

Formulas adjust automatically when you copy them, but that may not be always what you want. For example, suppose a worksheet contains last year’s household budget versus actual expense results, and you want to copy those values elsewhere in the worksheet so you can plug in estimated amounts for this year and analyze the new results using the formulas you’ve already created. Follow these steps:

1.	Select the cell(s) containing the formula(s) whose value(s) you want to copy. A dark border appears around selected cells.
2.	Click the Copy button on the Home tab or press Ctrl+C. The dark border changes to a dashed “marching ants” border.
3.	Select the cell(s) to which you want to copy the formula result(s).
4.	Click the arrow on the Paste button on the Home tab. On the palette menu that appears (see Figure 2-11), select exactly what you want to copy: Figure 2-11. Excel offers various options for pasting the results of your formulas. [View full size image] Values: Pastes the formula result Values & Number Formatting: Pastes the formula result and its number format Values & Source Formatting: Pastes the formula result, its number format, and its cell formats