Absolute vs Relative References in Excel ($A$1)

The dollar signs in a reference like $A$1 tell Excel what to keep fixed when you copy a formula. Without them, a reference is relative: copy =F2/E2 one row down and it becomes =F3/E3. With them, it's absolute, and it points at the same cell wherever the formula goes. A dollar sign in front of only the column or only the row makes a mixed reference, which locks just that part.

Relative references are the default, and most of the time they're what you want. You need dollar signs when every copy of a formula has to use the same cell, such as a tax rate, a total or a lookup table.

The examples use this sales log in A1:F11:

#The four types of cell reference

A dollar sign before the column letter locks the column. A dollar sign before the row number locks the row. Here's what happens to each type when a formula in B2 is copied one cell down and one cell right, to C3:

The dollar sign has nothing to do with currency. It means "don't change this part", and it's the same in every language version of Excel.

#Relative references: the default

When you click a cell while writing a formula, Excel adds a relative reference. That's exactly right for row-by-row math. How much did each order charge per unit? In G2:

Now copy G2 down to G11. Drag the fill handle, the small square at the bottom-right corner of the cell, or double-click it to fill down as far as the data goes. Each copy points at its own row:

Laptops come out at 1200 per unit, monitors at 250 and keyboards at 60. You typed one formula and got ten answers.

This works because Excel doesn't really remember "F2". It remembers a direction: "the cell one column to the left, divided by the cell two columns to the left". From G3, those directions lead to F3 and E3, so the copied formula still makes sense.

Dollar signs only matter when a formula is copied or filled. A formula you type once and never copy works the same with or without them.

Your turn. This exercise uses the same Revenue column, but it asks for one answer that covers all ten orders, so the formula isn't copied anywhere. Drag across the cells to insert the range, and notice that the formula gets a relative reference.

#Absolute references: lock a cell with $A$1

Say each rep earns a 5% commission. Type 5% in H1, above the commission column, and in H2 multiply the revenue by it:

That's right for the first order. Copy it down, though, and H1 moves down a row, just like F2 does. Here's what H3 gets:

Instead of the rate, Excel multiplies by H2, the commission above, so the $1,250 order earns a commission of 225,000. Each row multiplies by the result above it, and the numbers keep growing. The first row was correct, which is why this mistake is easy to miss.

The fix is to lock H1, so every copy uses it:

F2 still moves down a row each time, but $H$1 stays put. Change H1 to 6% and every commission updates. Format column H as currency to show 62.5 as $62.50.

Lock a cell like this whenever every row has to use the same one: a tax or commission rate, an exchange rate, a target, or the total you divide by for a percentage of total. If E12 holds the total units, =E2/$E$12 copied down gives each order's share.

#How to use F4 to add dollar signs

You can type the dollar signs yourself, but F4 is quicker. While you're editing a formula, click inside a reference, or just after it, and press F4. Each press moves to the next type:

To lock a whole range, select it in the formula bar, such as F2:F11, and press F4. It becomes $F$2:$F$11.

F4 works the same way in Excel for the web. In Excel for Mac, press Cmd+T or F4. If F4 changes the volume or brightness instead, on a Mac or a Windows laptop, hold Fn and press F4.

#Mixed references: lock only the row or the column

Now compare three commission rates side by side. Put 5%, 8% and 10% in H1, I1 and J1, and fill H2:J11 with a single formula. Every cell needs the revenue from column F on its own row, and the rate from row 1 in its own column. So lock the column of the revenue and the row of the rate:

Type it in H2, fill it right to J2, then fill H2:J2 down to row 11. Here's how the first three rows come out:

$F2 always reads column F but moves down the rows. H$1 always reads row 1 but moves across the columns. It's the same trick that builds a times table: with 1 to 10 in A2:A11 and in B1:K1, type =$A2*B$1 in B2 and copy it across and down to fill the grid.

Get one dollar sign wrong and the grid is wrong, with no error to warn you. Here's what I2 shows when each version is filled right from H2, with the prices per unit from earlier still in column G:

If column G were empty, the second and fourth versions would show 0 instead.

#Copying across vs copying down

Which dollar signs you need depends on which way the formula is copied:

• Copying down changes the row numbers. The column letters stay the same.
• Copying across changes the column letters. The row numbers stay the same.
• Copying diagonally changes both.

So you only need to lock the part that would otherwise move. The single commission column is only copied down, and its column letter can't change, so =F2*H$1 works just as well as =F2*$H$1. $H$1 is still a good habit for one fixed cell, because the rate stays put even if someone later copies the formula sideways.

A few ways of copying don't change references at all:

• Moving a formula, with cut and paste or by dragging the cell's border, keeps every reference exactly as it was. Only copying adjusts them.
• Copying the text of a formula from the formula bar and pasting it into another cell pastes it as typed.
• Whole columns like B:B have no row number, so they stay the same when copied down. Copied across, B:B becomes C:C, so write $B:$B if the formula goes sideways.

#Lock the ranges in VLOOKUP, SUMIF and COUNTIF

Lookups and conditional formulas are where missing dollar signs do the most damage, because the wrong answers still look believable. The rule is simple. The value you look up, or the condition you test, changes on every row, so leave it relative. The range you search never changes, so lock it.

Say you want the number of orders for each region, with East, North, South and West in L2:L5. In M2:

Copied down, L2 becomes L3, L4 and L5, but the range stays B2:B11. Without the dollar signs, the range slides down a row with each copy and drops orders from the top:

The unlocked version misses Ava's order in row 2 and Leo's in row 3, and West only comes out right by luck. SUMIF and SUMIFS go wrong the same way, so lock every range and leave the criteria cell relative. For revenue by region, in N2:

The SUMIF and SUMIFS guide and the COUNTIF guide cover the conditions themselves.

Lookup tables need the same treatment. Here's a price list in P1:Q4, with the per-unit prices worked out earlier:

To show each order's list price, put this in K2 and copy it down:

Without the dollar signs, the table slides down too. Row 3 still finds Monitor in P3:Q5, but row 4 looks for Laptop in P4:Q6, which only holds Keyboard:

XLOOKUP and INDEX and MATCH work the same way. Lock both ranges:

The VLOOKUP, XLOOKUP and INDEX and MATCH guides cover each function in detail.

#Common mistakes with cell references

#Frequently asked questions about cell references

#What does the $ sign mean in an Excel formula?

It locks the part of the reference that comes right after it. $H$1 locks both the column and the row, $H1 locks only the column, and H$1 locks only the row. It doesn't change the value or the formatting, and it doesn't mean dollars.

#Does $A$1 change when I insert or delete rows?

Yes. Dollar signs stop a reference from changing when you copy a formula, but Excel still updates it when the sheet changes shape. Insert a row above row 1 and $H$1 becomes $H$2, so it keeps pointing at the rate. If you delete the row that holds the cell, the formula returns #REF!.

#Why didn't my references change when I moved the formula?

Moving isn't copying. Cut and paste, or dragging a cell by its border, keeps the formula exactly as it was. Copy and paste, the fill handle and Ctrl+D in Excel for Windows all adjust the relative references.

#Is there a way to avoid dollar signs?

Two. You can name the cell: click H1, type a name such as CommissionRate in the Name Box to the left of the formula bar, and press Enter. Then =F2*CommissionRate copies down without any dollar signs, because a name always points at the same cell.

In Microsoft 365 and Excel 2021 or later, you can also skip copying. One formula in H2 works on the whole column and spills the results into the cells below:

#Do references work the same in every version of Excel?

Yes. Relative, absolute and mixed references work the same way in every version of Excel, on Windows and Mac, and in Excel for the web. Google Sheets uses the same A1, $A$1, $A1 and A$1 references, and they change the same way when you copy a formula. For Microsoft's own examples, see Switch between relative, absolute, and mixed references.

For more practice, the basics topic has a free average order size exercise on the same sales data, plus Premium exercises on revenue per unit and share of the total. The free SUMIF exercise is a good next step for working with ranges.

#Key takeaways

• A relative reference like A1 changes when you copy a formula. An absolute reference like $A$1 doesn't.
• $A1 locks only the column and A$1 only the row. Use them when a formula is copied both down and across.
• Press F4 while editing a reference to cycle through the four types. In Excel for Mac, Cmd+T works too.
• Lock rates, totals, lookup tables and criteria ranges, and leave the reference to the current row relative.