Perform calculations on data

Copy and move formulas

After you create a formula, you can copy it and paste it into another cell. When you do, Excel changes the formula to work in the new cells. For instance, suppose you have a worksheet in which cell C7 contains the formula =SUM(C2:C6). If you copy cell C7 and paste the copied formula into cell D7, Excel enters =SUM(D2:D6). Excel knows to change the cells used in the formula because the formula uses a relative reference—a reference that can change if the formula is copied to another cell. Relative references are written with just the cell row and column—for example, C14.

Relative references are useful when you summarize rows of data and want to use the same formula for each row. As an example, suppose you have a worksheet with two columns of data, labeled Sale Price and Rate, and you want to calculate a sales representative’s commission by multiplying the two values in a row. To calculate the commission for the first sale, you would enter the formula =A2*B2 in cell C2.

Use formulas to calculate values such as commissions

Selecting cell C2 and dragging the fill handle down through cell C7 copies the formula from cell C2 into each of the other cells. Because you created the formula by using relative references, Excel updates each cell’s formula to reflect its position relative to the starting cell (in this case, cell C2). The formula in cell C7, for example, is =A7*B7.

Copying formulas to other cells to summarize additional data

When you enter a formula in a cell of an Excel table column, Excel automatically copies the formula to the rest of the column and updates any relative references in the formula.

If you want a cell reference to remain constant when you copy a formula to another cell, use an absolute reference by inserting a dollar sign ($) before the column letter and row number or a mixed reference by inserting a dollar sign before either the column letter or row number.

One quick way to change a cell reference from relative to absolute is to select the cell reference in the formula bar and then press F4. Pressing F4 cycles a cell reference through the four possible types of references:

• Relative columns and rows (for example, C4)

• Absolute columns and rows (for example, $C$4)

• Relative columns and absolute rows (for example, C$4)

• Absolute columns and relative rows (for example, $C4)

To copy a formula without changing its cell references

1. Select the cell that contains the formula you want to copy.

2. In the formula bar, select the formula text.

3. Press Ctrl+C.

4. Select the cell in which you want to paste the formula.

5. Press Ctrl+V.

6. Press Enter.

To move a formula without changing its cell references

1. Select the cell that contains the formula you want to copy.

2. Point to the edge of the selected cell until the pointer changes to a black four-headed arrow.

3. Drag the outline to the cell where you want to move the formula.

To copy a formula and change its cell references

1. Select the cell that contains the formula you want to copy.

2. Press Ctrl+C.

3. Select the cell in which you want to paste the formula.

4. Press Ctrl+V.

To create relative and absolute cell references

1. Enter a cell reference into a formula.

2. Do either of the following:

   • Enter a $ in front of a row or column reference you want to make absolute.

   • Select within the cell reference, and then press F4 to advance through the four possible combinations of relative and absolute row and column references.