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?
- 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.
- 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).
- Copy this formula up and to the left.
- 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.
- This formula already points to the top row.
- 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.