MrExcel.com - Your One Stop for Excel Tips & Solutions

Copy a Formula That Contains Relative References

November 23, 2021 - by

Copy a Formula That Contains Relative References

Problem: I have 5,000 rows of data. After entering a formula to calculate gross profit percent for the first row, how do I copy the formula down to other rows?

A Gross Profit Percent formula in G2 currently points to several cells in row 2.
Figure 170. Copy a formula down to all rows.

Strategy: All of the cell references in the figure above 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 below, then every relative reference pointing at row 2 will change to point to row 3.

Copy that formula down to all rows and look at the formula in row 3. Any references that had been pointing to row 2 are now pointing to row 3. This happens automatically.
Figure 171. E2 changes to E3. F2 changes to F3.

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. The fill handle is the square dot in the lower right corner of the selection rectangle.

Additional Details: Relative references will move in all four directions. In the figure below, if you copy the formula in cell E5 to D4, the referenced cell will change from D3 to C2 (up one cell, and one cell to the left).

This figure is the first to illustrate how a reference will be copied. The starting formula in cell E5 is =D3.
Figure 172. Copy this formula up and to the left.
Copy that formula to the 9 cells surrounding E3. The worksheet is in show formulas mode, so you can see the 9 formulas now read =C2, =D2, =E2, =C3, =D3, and so on down to =E4. In each case, the formula is pointing two rows up and one column to the left.
Figure 173. The original D3 reference changes as the formula is copied.

In the figure above, you can see how the formula copied to nine other cells will change.

Additional Details: The figures above were shot in Show Formulas mode. To enter Show Formulas mode, press Ctrl+`. (On a U.S. keyboard the grave accent is on the same key as the tilde, ~, just below the Esc key.) To toggle back to regular mode, press Ctrl+` again.

Gotcha: It is possible to copy a formula so that it will point to a cell that does not exist. The formula in the figure below is pointing at cell A1. If you copy that formula up one row, it would need to point to row zero. Since row zero does not exist, it becomes a #REF! error.

The relative nature of formula references could work against you. In this case, a formula in C4 is =A1. That formula essentially points two columns to the left and three rows up. This example continues in the next figure.
Figure 174. This formula already points to the top row.
Copy the =A1 formula from C4 and paste in B3. This formula wants to point to row 0 and the column to the left of column A. Since neither of those exist, you get a =#REF! error.
Figure 175. Copying the formula up will point to an invalid cell.

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

This article is an excerpt from Power Excel With MrExcel

Title photo by Gemma Evans on Unsplash