What the Dollar Sign Does

When you copy a formula, the spreadsheet usually shifts the cell references to match the new spot. Drag =A1+B1 down one row and it becomes =A2+B2 automatically.

This is normally exactly what you want. But sometimes you need a reference to stay put no matter where you copy it. That is where the dollar sign comes in.

The dollar sign ($) is a tiny lock you place inside a cell reference. It tells the spreadsheet: do not let this part change when the formula is copied.

A reference has two parts: the column letter and the row number. You can lock either one, both, or neither.

Every cell can be referenced in four styles, depending on where you put the dollar signs:

• A1 nothing locked (relative)
• $A$1 column and row both locked (absolute)
• $A1 column locked, row free (mixed)
• A$1 row locked, column free (mixed)

The dollar sign always goes before the part it locks.

A plain reference like A1 is relative. It has no dollar signs, so both the column and the row are free to move when copied.

Think of it as a direction such as "the cell two columns to my left." When you copy the formula, the direction stays the same but the target shifts.

Putting a dollar sign before both parts, like $A$1, makes the reference absolute. It always points to that one exact cell, wherever you copy the formula.

This is perfect for a single value that many rows need, such as a tax rate sitting in one cell.

A mixed reference locks only one part. $A1 keeps the column on A but lets the row move. A$1 keeps the row on 1 but lets the column move.

Mixed references are the secret behind grids and tables, where you want to slide in only one direction.

Suppose cell D1 holds a discount of 0.10, and prices sit in column B starting at B2. To find each discounted amount you write:

=B2*$D$1

Copy it down the column. The B2 part walks down to B3, B4 and so on, while $D$1 stays glued to the discount cell.

If you had written =B2*D1 instead and copied it down, the D1 would slide to D2, then D3 empty cells.

Your results would suddenly turn to zero or errors, because the formula stopped pointing at the discount. The missing dollar signs are the cause.

You do not have to type dollar signs by hand. In Excel, click inside a reference and press F4 to cycle through the styles:

• First press: $A$1
• Second press: A$1
• Third press: $A1
• Fourth press: back to A1

In Google Sheets the same shortcut is F4 as well.

When you open someone else's sheet, the dollar signs tell you their intent. Seeing $E$1 repeated everywhere usually means a shared setting like a rate or constant.

A reference with no dollar signs is meant to follow each row. Learning to read these signs makes any spreadsheet easier to understand.

Ask yourself one question before copying a formula: should this reference stay or move?

• Stay on one fixed cell? Use absolute $A$1.
• Move with every row? Use relative A1.
• Lock only a row or only a column? Use mixed A$1 or $A1.

Answering this first saves you from broken copies later.

Let's make sure the dollar sign clicked.

The dollar sign is a lock inside a reference:

• A1 relative, nothing locked
• $A$1 absolute, both locked
• $A1 and A$1 mixed, one part locked

The $ always locks the part directly after it. Press F4 to add it quickly. Next you'll lock a single cell with a full absolute reference.