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.

In this guide

• 1. What a Relative Reference Does When You Drag It
• 2. The Broken Drag — a Worked Example
• 3. Lock a Reference with the Dollar Sign
• 4. Mixed References — Lock the Column or the Row
• 5. Cycle Through All Four with F4
• 6. Use a Named Range for a Readable Alternative
• 7. Which Reference Type to Use When

Before You Start

• 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 formula	Excel shifts the reference
Down one row	Row number goes up by one
Up one row	Row number goes down by one
Right one column	Column letter advances by one
Left one column	Column 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.

Row	A	B	C	E
1	Item	Price	Tax	0.0825
2	Keyboard	42.00
3	Monitor	289.00
4	Headset	68.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 as	What is locked	What moves	Reads as
A1	Nothing	Column and row	Relative
$A$1	Column and row	Nothing	Absolute
$A1	The column	The row	Column-locked
A$1	The row	The column	Row-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.

Row	A	B	C	D
1		5%	10%	15%
2	42.00
3	289.00
4	68.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

4. 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

5. 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 at	Use	Example
Data on the same row as the formula	Relative	=B2*C2
One rate, total, or threshold used by every row	Absolute	=B2*$E$1
A lookup table every row searches	Absolute	=VLOOKUP(A2,$G$2:$H$50,2,FALSE)
Values down the left of a grid you fill both ways	Column-locked	=$A2*B$1
Values across the top of that same grid	Row-locked	=$A2*B$1
Anything a colleague will have to read next quarter	A 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 / Error	Potential Cause	Solution
Dragged formula returns 0 down the column	Reference to a rate or total shifted onto empty cells	Lock it as $E$1 and fill down again — see Step 3.
Every row shows the same answer	The reference that should move got locked	Remove 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 work	A referenced row or column was deleted	Dollar 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 numbers	Both halves locked, or neither	Use 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 only	Lookup range not locked	Change the range to $A$2:$D$500 so every copy searches the same table.
F4 does nothing	Function key claimed by the laptop or the browser	Press Fn + F4 on Windows, Command + T on a Mac, or type the dollar signs by hand.
That name is not valid when naming a cell	Name has a space or looks like an address	Rename it without spaces and not in the pattern of a cell address — Q1Sales rather than Q1.

Related Articles

• How to Fix Common Excel Formula Errors
• How to Use Named Ranges in Excel
• How to Use VLOOKUP in Excel
• How to Use SUMIF and COUNTIF in Excel
• How to Split Text into Columns in Excel
• How to Freeze Panes and Split Views in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?