Pin Me

Microsoft Excel: Copy a Formula That Contains Relative References

written by: Mr Excel•edited by: Michele McDonough•updated: 4/3/2010

Using Microsoft Excel's capability of handling relative references, you can quickly copy a formula to an entire row or column. This method can literally save you hours of work and improve productivity in your home office.

  • slide 1 of 3

    Copying Excel Formulas

    Fig. 167 - Original Excel Formula Problem: You have 5,000 rows of data. After entering a formula to calculate Gross Profit Percent for the first row, as shown in Fig. 167 (click any image for a larger view), how do you copy the formula down to other rows?

    With Excel's ability to handle relative cell references, this task becomes simple. In the next section, we'll explain the solution to this problem.

  • slide 2 of 3

    Extending the Formula to Other Rows and Columns

    Strategy: All of the cell references in the formula are known as relative references. The amazing thing about Excel is that when you copy a formula, all of the relative cell references are automatically adjusted. If you copy a formula from row 2 down to row 3, as shown in Fig. 168, then every reference pointing at row 2 will change to point at row 3.

    Fig. 168 - Copying the Excel Formula 

    So, the solution to the problem is simply to copy the formula down to all the other rows. A shortcut for doing this is to select the cell and then double-click the Fill handle to copy the formula down to all rows with values in the adjacent column.

    Additional Details: Relative references will move in all four directions. In Fig. 169, if you copy the formula in cell F7 to E6, the referenced cell will change from D3 to C2.

    Fig. 169 

    In Fig. 170, you can see how the formula copied from F7 to E6:G8 will change.

    Fig. 170 

    Hint: Fig. 170 was shot in Show Formula mode. To enter Show Formula mode, hit Ctrl+~. To toggle back to regular mode, hit Ctrl+~ again.

    Gotcha: It is possible to copy a formula so that it will point to a cell that does not exist.? As shown in Fig. 171, what would happen if you copied C4 to B3?

    Fig. 171 

    The reference to A1 would have to point to the cell one row above and one column to the left of A1. This cell does not exist, so Excel will return a #REF error, as shown in Fig. 172.

    Fig. 172 

    Summary: The "miracle" of Excel is that you can enter a formula in one place and copy it to many other places and it will still work. This is because a regular cell reference, such as B1, is a relative reference.

    Note: If your formula contains an absolute, or fixed, reference, you may need to modify it slightly before you can copy it to other cells. To find out how to do this, see Microsoft Excel: Copy a Formula While Keeping One Reference Fixed.

  • slide 3 of 3

    Additional Excel Resources

    For more tips, be sure to check out the hundreds of other Microsoft Excel reference guides available at Bright Hub, including the following selections.

    91 Tips for Calculating with Microsoft Excel – Learn quick and easy tips for calculating loan payment scenarios, using Excel functions, and more in this collection of tutorials written by Bill Jelen, also known as Mr. Excel. You can even use Excel to play Craps and other dice games!

    Mr. Excel's Formatting Tips – Need to make changes to a chart or add a pop-up picture to a cell? Discover how to do these and other things with Mr. Excel's top formatting tips.

    Basic Microsoft Excel Cheat Sheet – Discover several time-saving keyboard shortcuts that can help improve productivity and efficiency when working in Microsoft Excel. Also, learn about some of Excel's most common formulas and how to use them.