How References Adjust as You Copy

When you write =A2 in a formula, the spreadsheet does not really remember "cell A2." It remembers a direction and distance, like "the cell two columns to my left."

This is called a relative reference. Because the reference is described relative to the formula's own position, it adjusts whenever you copy the formula somewhere new. This single behavior is the reason filling works so well.

Set up two columns of numbers:

• A2 = 100, B2 = 50
• A3 = 200, B3 = 80

In C2 type =A2+B2. It shows 150. Now copy C2 down to C3. The result is 280, because the formula became =A3+B3.

You did not edit anything. The references followed the formula down one row.

When you copy a formula downward, only the row numbers change. The column letters stay put.

Starting from =A2+B2:

• One row down becomes =A3+B3
• Two rows down becomes =A4+B4
• Three rows down becomes =A5+B5

The columns A and B never move because you are not moving sideways.

Copying a formula sideways changes the column letters and leaves the row numbers alone.

Suppose B2 holds =B1*2. Copy it right into C2 and D2:

• C2 becomes =C1*2
• D2 becomes =D1*2

The row stays 1 because you only moved across, not down. Move both directions at once and both parts shift together.

A helpful mental model: a relative reference stores an offset from the formula cell.

If C2 contains =A2, the offset is "two columns left, same row." Copy that formula to C5 and the offset is reapplied from the new home: two columns left of C5, same row, which is A5.

The arrow between the formula and its target always keeps the same length and direction.

Imagine a 1,000-row invoice. Each row needs quantity times price. Without relative references you would write 1,000 different formulas by hand.

With them, you write =A2*B2 once and fill down. Every row automatically points at its own quantity and price. One formula, a thousand correct answers, zero typos.

Relative references turn a tedious task into a single double-click.

You can prove this to yourself. After filling, click any of the copied cells and look at the formula bar at the top.

Copy =A2+B2 down five rows, then click the last one. The formula bar shows =A7+B7, not =A2+B2. Each cell holds its own adjusted version.

Clicking through the column and reading the formula bar is the best way to build intuition for how copying works.

Relative adjustment is usually what you want, but not always. Suppose every row should be multiplied by a single tax rate sitting in cell E1.

If you write =C2*E1 and fill down, the E1 part drifts to E2, E3, E4 and so on, landing on empty cells. The result breaks.

You want C2 to slide down but E1 to stay locked. That requires an absolute reference, covered in the next course.

Here is a sneak peek at the fix. Putting a dollar sign in front of a part of a reference locks it so it cannot shift.

Write =C2*$E$1 and fill down. Now C2 slides to C3, C4 and so on, while $E$1 stays frozen on the tax rate every time.

You will master this dollar-sign trick soon. For now, just know it exists.

Everything here works identically in Microsoft Excel and Google Sheets. Both default to relative references, both shift rows when you fill down, and both shift columns when you fill across.

The dollar-sign locking syntax is the same in both as well. Skills you build copying formulas in one app transfer directly to the other.

If a filled column shows wrong numbers, the cause is almost always a reference that shifted when it should have stayed.

Click a misbehaving cell and read the formula bar. Ask: is this pointing where I expect? If a reference that should be fixed has drifted, you have found your bug, and a dollar sign is the cure.

Check your understanding of how references shift.

You now understand the engine behind filling:

• References are relative by default, stored as an offset from the formula cell.
• Filling down shifts row numbers; filling across shifts column letters.
• This auto-adjusting is what lets one formula serve thousands of rows.
• When a reference must stay put, a dollar sign locks it, which you will learn next course.

Read the formula bar whenever a filled column looks wrong.