Copying formulas: relative references

Copy =B4*C4 from D4 to D5 and it arrives as =B5*C5. Microsoft uses this exact example to explain relative references.

References remember a direction

Write a formula once, then copy it down a column, and it works on every row. This is possible because an ordinary reference is relative. Microsoft explains it with A2 seen from C2: the reference really means "the cell two columns to the left, in the same row". Copy the formula somewhere else and it still means two columns to the left of wherever it now sits. So =B4*C4 in D4, which means "the two cells to my left", becomes =B5*C5 when copied to D5.

The rule is simple arithmetic on the address. Move the formula down by some rows and every row number in it grows by the same amount. Move it right by some columns and every column letter moves the same number of letters. Microsoft's table copies =A1 two cells down and two cells to the right and gets =C3. Copying =A2+1 from B2 up to B1 gives =A1+1. Nothing about the formula's meaning changes, only where it looks.

Three ways to copy

You can copy and paste the cell as usual. You can drag the small square at the bottom-right corner of the selected cell, called the fill handle, down or across. Or you can select the formula together with the empty cells below it and press Ctrl+D to fill down, or select it with the cells to its right and press Ctrl+R to fill right. Google's shortcut list gives Ctrl+D and Ctrl+R for Sheets (⌘+D and ⌘+R on a Mac), and Excel on Windows uses the same keys. The lab has buttons for these, plus a Select range button for touch screens.

To see what actually happened, show the formulas instead of the results. Google Sheets does this with Ctrl+~ according to its shortcut list, and Excel has a Show Formulas button on the Formulas tab. Reading a column of formulas side by side is the quickest way to spot the one row that does not follow the pattern, for example a cell where someone typed a number over the formula.

Copying only moves the references, not the values. If a formula refers to a cell that is meant to stay put, such as a tax rate in one cell at the top, a relative reference will slide away from it as you fill down, and the results will be wrong without any error showing. That problem has its own fix, the absolute reference, which opens the Formulas course.

Falling off the edge

A relative reference can be pushed somewhere that does not exist. Copy =A1+1 from B1 one column left into A1 and the reference would have to point left of column A. There is nothing there, so the program writes #REF! into the formula itself, and the cell shows the #REF! error. The same thing happens when you copy a formula that looks one row up into row 1. When a copied formula suddenly shows #REF!, show formulas and check which reference ran off the sheet. The next course shows how a dollar sign stops a reference from moving at all.