No Ticket Needed

Appearance

11 min read Recently reviewedBeginner

By , IT support technician

Understanding Absolute and Relative Cell References in Excel ​

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the web); Excel 2016, 2019, 2021 and earlier
Article Type: Informational
Last Updated: 2026-07-29

Summary ​

"My formula worked in the first row and gave nonsense in the rest" is almost always one missing dollar sign. This article explains what Excel does to your references when you copy a formula, what each of the four reference styles is for, and how to fix a broken drag in about ten seconds.

Prerequisites ​

  • None required. Cell references work the same way in every version of Excel, on Windows, Mac, and the web.

Instructions ​

1. What a Relative Reference Does When You Drag It ​

A1 is a relative reference. Excel does not store it as "cell A1" — it stores it as a direction and a distance from wherever the formula sits.

Put =A1 in cell B1, and Excel records "the cell one to my left". Copy that formula to B5 and it reads =A5, because one to the left of B5 is A5. Copy it to D1 and it reads =C1.

This is the behaviour you want almost all of the time. It is why you can write one formula at the top of a column, drag it down two thousand rows, and have every row calculate from its own data instead of from row 2.

You copy the formulaExcel shifts the reference
Down one rowRow number goes up by one
Up one rowRow number goes down by one
Right one columnColumn letter advances by one
Left one columnColumn letter goes back by one

Note: Cutting is different from copying. Ctrl + X followed by Ctrl + V moves a formula without adjusting anything — every reference keeps pointing where it pointed before. Only copying and filling shift references.

2. The Broken Drag — a Worked Example ​

Here is the exact situation that sends people looking for this article. A price list, with the sales tax rate held once in cell E1.

RowABCE
1ItemPriceTax0.0825
2Keyboard42.00
3Monitor289.00
4Headset68.50

In C2 you write the formula that works:

=B2*E1

It returns 3.47. Correct. Now you drag it down to C3 and C4 and the numbers turn into zeros.

Click C3 and look at the formula bar. It reads:

=B3*E2

Excel did what you asked. B2 moved down to B3 — which is what you wanted — and E1 moved down to E2 at the same time, which is not. E2 is empty, empty counts as zero, and anything multiplied by zero is zero. C4 reads =B4*E3 and is zero for the same reason.

Nothing is broken and no error appears. That is what makes this one expensive: a column of plausible zeros in a report that gets sent out.

3. Lock a Reference with the Dollar Sign ​

An absolute reference is a cell address with dollar signs in it. $E$1 means "cell E1, always" — copy it anywhere and it still reads $E$1.

Rewrite the formula in C2:

=B2*$E$1

Drag it down and the copies read =B3*$E$1 and =B4*$E$1. Each row uses its own price and all of them use the one tax rate. The tax column now shows 3.47, 23.84, and 5.65.

The dollar sign has nothing to do with currency. It is a lock symbol that happens to reuse the same character, and it never appears in the cell's result.

Reach for an absolute reference whenever a formula refers to something there is only one of:

  • A single rate, multiplier, or threshold held in its own cell.
  • A grand total that every row is measured against for a percentage.
  • A lookup table that every row searches — the table_array in a VLOOKUP is the classic case, and locking it is covered in How to Use VLOOKUP in Excel.
  • A criteria cell that every row of a SUMIF or COUNTIF compares against. See How to Use SUMIF and COUNTIF in Excel.

Note: Locking a reference does not protect it from deletion. Delete row 1 and every $E$1 in the workbook turns into #REF!, because the cell it pointed at no longer exists. The dollar sign controls copying, not survival — more on that error in How to Fix Common Excel Formula Errors.

4. Mixed References — Lock the Column or the Row ​

The two mixed forms lock one half of the address and let the other move. They look fussy until you have to fill a grid, and then they are the only thing that works.

Written asWhat is lockedWhat movesReads as
A1NothingColumn and rowRelative
$A$1Column and rowNothingAbsolute
$A1The columnThe rowColumn-locked
A$1The rowThe columnRow-locked

The way to remember it: the dollar sign locks whatever comes immediately after it. $A1 has the dollar before the A, so the column is pinned. A$1 has it before the 1, so the row is pinned.

Where this genuinely matters: a grid where one set of values runs down the left and another runs across the top. Discount percentages across row 1, list prices down column A, and a discounted price in every cell of the block between them.

RowABCD
15%10%15%
242.00
3289.00
468.50

One formula fills the entire block. In B2:

=$A2*(1-B$1)
  • $A2 is column-locked. Fill it right across the row and it stays on column A, so every cell in row 2 uses the price at the start of that row. Fill it down and the 2 becomes 3, 4, and so on.
  • B$1 is row-locked. Fill it down and it stays on row 1, so every cell in column B uses the percentage at the top of that column. Fill it right and the B becomes C, D, and so on.

Drag B2 across to D2, then drag that whole row down to row 4. Nine correct results from one formula. With plain A2 and B1 the block fills with garbage, and with $A$2 and $B$1 every cell in it shows the same number.

5. Cycle Through All Four with F4 ​

Typing dollar signs by hand is fine for one formula and tedious for twenty. F4 does it for you.

  1. Click the cell holding your formula, then click inside the formula bar above the column headers.
  2. Place the cursor anywhere inside the reference you want to change, or select the whole reference.
  3. Press F4. The reference cycles through the four styles in this order, one press at a time:
A1  →  $A$1  →  A$1  →  $A1  →  back to A1
  1. Stop on the one you need and press Enter.

Tip: This works while you are still typing a formula, not only when editing an existing one. Click a cell to add it to your formula and press F4 straight away, before typing the next operator.

Note: On a Mac, F4 is often claimed by the system for screen brightness. Press Command + T instead, or hold Fn while pressing F4. On a Windows laptop with a media-key row, use Fn + F4. In Excel for the web some browsers claim F4 for the address bar — type the dollar signs there.

6. Use a Named Range for a Readable Alternative ​

=B2*$E$1 is correct and tells you nothing about what it calculates. A named range gives the cell a word instead of an address.

  1. Click cell E1, the one holding the tax rate.
  2. Click inside the Name Box — the small white box immediately to the left of the formula bar, which currently reads E1.
  3. Type TaxRate and press Enter. The box now shows TaxRate whenever that cell is selected.
  4. Write your formula using the name:
=B2*TaxRate
  1. Drag it down. Every copy still reads =B2*TaxRate, =B3*TaxRate, and so on.

Names are absolute by default, so there is nothing to lock and nothing to forget. They also survive being read by someone else six months later, which $E$1 does not.

Naming rules worth knowing before you hit an error message:

  • No spaces. Use TaxRate or Tax_Rate, not Tax Rate.
  • Cannot start with a number, and cannot be a single letter R or C.
  • Cannot look like a cell address, so Q1 is rejected. Use Q1Sales.
  • Names apply to the whole workbook by default, so a name defined on one sheet works in formulas on every sheet.

To review, edit, or delete names, press Ctrl + F3 to open the Name Manager, or click Formulas > Name Manager in the Defined Names group. Naming every column of a table in one move, confining a name to a single sheet, and repairing a name that has broken are covered in How to Use Named Ranges in Excel.

Tip: Converting a range to a table with Ctrl + T gives you the same readability for columns. A formula written as =[@Price]*TaxRate inside a table refers to the price on its own row, and it copies down correctly without a dollar sign anywhere.

7. Which Reference Type to Use When ​

A quick decision table for the moment you are staring at a formula wondering what to lock.

What the reference points atUseExample
Data on the same row as the formulaRelative=B2*C2
One rate, total, or threshold used by every rowAbsolute=B2*$E$1
A lookup table every row searchesAbsolute=VLOOKUP(A2,$G$2:$H$50,2,FALSE)
Values down the left of a grid you fill both waysColumn-locked=$A2*B$1
Values across the top of that same gridRow-locked=$A2*B$1
Anything a colleague will have to read next quarterA named range=B2*TaxRate

The test that settles it: ask what happens to this reference when the formula moves one row down and one column right. If the answer is "it should move with it", leave it relative. If the answer is "it must stay exactly where it is", lock both halves. If the answer is "one of those but not the other", you need a mixed reference.

Troubleshooting ​

TIP

Before you rewrite a formula that is producing wrong numbers, click one of the copies — not the original — and read the formula bar. Ninety per cent of the time the reference that was supposed to stay still has crept down the sheet, and you can see it in one glance. Excel also shows you this in colour: double-click a cell and each reference is outlined on the grid in the same colour as its text in the formula.

Symptom / ErrorPotential CauseSolution
Dragged formula returns 0 down the columnReference to a rate or total shifted onto empty cellsLock it as $E$1 and fill down again — see Step 3.
Every row shows the same answerThe reference that should move got lockedRemove the dollar signs from the reference that must follow each row. Press F4 until it reads plainly, such as B2.
#REF! appears in formulas that used to workA referenced row or column was deletedDollar signs do not protect against deletion. Rebuild the reference, or use a named range so there is one place to repair — see How to Fix Common Excel Formula Errors.
Filling a grid sideways gives wrong numbersBoth halves locked, or neitherUse the mixed pattern =$A2*B$1 so the row source stays on its column and the column source stays on its row — see Step 4.
VLOOKUP works in the first row onlyLookup range not lockedChange the range to $A$2:$D$500 so every copy searches the same table.
F4 does nothingFunction key claimed by the laptop or the browserPress Fn + F4 on Windows, Command + T on a Mac, or type the dollar signs by hand.
That name is not valid when naming a cellName has a space or looks like an addressRename it without spaces and not in the pattern of a cell address — Q1Sales rather than Q1.