Copying formulas

In many spreadsheets, you may need to create similar formulas that use different data. For example, you may have a spreadsheet that needs to add the same number of cells in adjacent columns.

You could type nearly identical formulas in multiple cells, but that’s tedious and error-prone. For a faster way, you can copy a formula and paste it in another cell; then Excel automatically changes the cell references, as shown in Figure below.



Rather than type repetitive formulas over and over again, Excel can copy a formula but automatically change the cell references.

In above figuer you can see that cell B9 contains the formula =B3+B4+B5+ B6+B7+B8, which simply adds the numbers stored in the six cells directly above the cell that contains the formula (B9). If you copy this formula to another cell, that new formula will also add the six cells directly above it. Copy and paste this formula to cell C9, and Excel changes the formula to
=C3+C4+C5+C6+C7+C8.

To copy and paste a formula so that each formula changes cell references automatically, follow these steps:

1. Select the cell that contains the formula you want to copy.
2. Press Ctrl+C (or click the Copy icon under the Home tab).
   Excel displays a dotted line around your selected cell.
3. Select the cell (or cells) where you want to paste your formula.
   If you select multiple cells, Excel pastes a copy of your formula in each of those cells.
4. Press Ctrl+V (or click the Paste icon under the Home tab).
   Excel pastes your formula and automatically changes the cell references.
5. Press Esc or double-click away from the cell with the dotted line to make the dotted line go away.

[Back] [Contents] [Next]