Absolute and relative references in Excel

An absolute reference in Excel is a cell reference with dollar signs: $A$1. The $ sign locks the column and the row, so the reference doesn’t change when you copy or fill the formula. A regular, relative reference like A1 shifts together with the formula. There are also mixed references: $A1 locks only the column, A$1 only the row. The fastest way to add dollar signs is the F4 key: put the cursor on a reference in the formula and press F4 until you get the type you need.

Relative references: the formula remembers the distance

By default, all references in Excel are relative. A formula stores not the address itself but an offset: “the cell two columns to the left in the same row”. So when you fill a formula down, the row numbers in it grow, and when you fill it right, the column letters change.

This is what makes Excel convenient: you write a formula once and fill it down thousands of rows. But sometimes part of the formula must not move — a reference to an exchange rate, a tax rate or a grand total. That’s what the $ sign is for.

Absolute references: the $ sign locks a cell

A dollar sign before the column letter stops the column from changing, before the row number — the row. $E$2 stays a reference to E2 wherever you copy it. A typical example is converting prices from dollars to euros at a rate stored in one cell.

And here’s what happens if you forget the dollar signs:

Mixed references: $A1 and A$1

Sometimes you need to lock only one dimension. $A1 can’t leave column A, but its row changes. A$1 sticks to row 1, but its column changes. Mixed references are needed when a formula is filled both down and right — for example, in a price × quantity grid.

How to add $ with the F4 key

You don’t have to type dollar signs. Select a reference in the formula bar, or put the cursor on it in edit mode (F2), and press F4. Each press cycles the reference type:

• If you select the whole formula or part of it in the formula bar, F4 toggles all references in the selection at once.
• A range like A2:C100 toggles as a whole: cursor anywhere on it, F4, and you get $A$2:$C$100.
• On laptops F4 is often a media key: press Fn+F4 or turn on Fn Lock.
• In Excel for Mac use ⌘+T instead of F4.
• Outside edit mode F4 does something else — it repeats the last action. If something changed in the cell instead of dollar signs appearing, press Ctrl+Z and enter the cell first.

More shortcuts are collected in the article on Excel hotkeys.

How to lock a cell in an Excel formula

“Lock a cell in a formula”, “keep a cell constant”, “anchor a cell reference” — all of these mean the same $ sign. The steps:

1. Select the cell with the formula and press F2 or click in the formula bar.
2. Put the cursor on the reference you want to lock.
3. Press F4 once — the reference becomes absolute, for example $E$2.
4. Press Enter and fill the formula.

Don’t confuse this with View → Freeze Panes: it keeps rows and columns visible on screen while you scroll and has nothing to do with formulas.

Where absolute references are needed: typical formulas

The $ sign appears in most working formulas. Here are the most common cases.

• The table in VLOOKUP: =VLOOKUP(A2,$E$2:$F$100,2,FALSE). Without dollar signs the lookup range slides down as you fill the formula, and the last rows return #N/A.
• Ranges in SUMIFS and COUNTIF: lock the ranges, keep the criterion relative: =COUNTIF($A$2:$A$500,D2).
• Constants: an exchange rate, a tax rate, a discount, a target — anything stored in one cell and used in many formulas.
• Conditional formatting: to highlight whole rows, lock the column but not the row: =$C2="Paid".

References to another sheet and another workbook

A reference to another sheet uses an exclamation mark: =Sheet2!B3. If the sheet name contains spaces or special characters, it goes in single quotes: ='Sales 2026'!B3. The $ sign goes only before the column and row: =Sheet2!$B$3 — never before the sheet name.

A reference to another workbook adds the file name in square brackets: ='[Rates.xlsx]Sheet1'!$B$2. When you click a cell in another workbook, Excel inserts an absolute reference; if you need to fill it, remove the dollar signs with F4. What all these characters mean is covered in the article on Excel special characters.

Named cells instead of the $ sign

Instead of $E$2 you can give the cell a name. Select it, type a name such as Rate in the Name Box left of the formula bar and press Enter. Now =B2*Rate reads without decoding, and the name always points to one cell, just like an absolute reference. Names are handy for constants and VLOOKUP lookup tables.

References in Excel tables

If the data is formatted as a table (Ctrl+T), Excel writes column names instead of addresses: =[@Price]*[@Qty]. Such references fill the whole column by themselves and need no dollar signs. But when you fill the formula to the right, a column reference shifts like a relative one. To lock it, repeat the column name as a range: =Sales[[Price]:[Price]].

The R1C1 reference style

If you see numbers instead of column letters and formulas like =R[-1]C*R2C5, the R1C1 style is on. In it, an absolute reference is R2C5 (row 2, column 5) and a relative one is R[-1]C[2] (one row up and two columns right). To switch back: File → Options → Formulas, clear “R1C1 reference style”.

How to copy a formula without changing references

Dollar signs matter when copying and filling. If you need to move a formula exactly as it is, without shifting references and without editing dollar signs, there are three ways:

• Through the formula bar. Select the formula text in the formula bar, press Ctrl+C, then Esc, go to the target cell and paste. The text is pasted as is.
• Ctrl+' (apostrophe). Copies the formula from the cell above into the active cell without changing references.
• Cut and paste. If you cut a cell (Ctrl+X) and paste it elsewhere, the references inside its formula don’t change — but references from other formulas to this cell do.

For a whole block of formulas: use Ctrl+H to replace “=” with “#=” so the formulas become text, copy the block, then replace “#=” back with “=” in both places.

How to make all references absolute at once

For one formula: select it entirely in the formula bar and press F4. For a hundred formulas a macro is faster — it converts references in all selected cells:

Sub MakeReferencesAbsolute()
    ' Makes all references in the formulas of the selected cells absolute
    ' Instead of xlAbsolute use xlAbsRowRelColumn (A$1), xlRelRowAbsColumn ($A1) or xlRelative (A1)
    Dim c As Range
    For Each c In Selection.Cells
        If c.HasFormula Then
            c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
        End If
    Next c
End Sub

Paste the code into a module (Alt+F11 → Insert → Module) and run it on the selected range. To use it in every workbook, store it in your Personal Macro Workbook. One limitation: ConvertFormula doesn’t handle formulas longer than 255 characters, so fix those by hand.

The $ sign doesn’t protect against inserted rows

An absolute reference stays the same only when copying. If you insert a row above E2 or delete a column, Excel rewrites $E$2 too, so that it keeps pointing to the same cell with the rate — now $E$3. Usually that’s exactly what you want.

If the formula must always look at address E2 no matter what happens to the sheet, use INDIRECT: =INDIRECT("E2"). The address is written as text, and Excel leaves it alone.

Why a formula is wrong after filling: common mistakes

• Zeros or #N/A at the bottom of the column. A reference to a constant or a lookup range isn’t locked: add $ with F4.
• Every row shows the same value. Too much is locked: for example, $B$2 instead of B2 for a value that should change from row to row.
• The table “drifts” when filling right. You need a mixed reference: $A2 if the column must stay put.
• F4 doesn’t add dollar signs. You’re not in edit mode (press F2), or the laptop needs Fn+F4; on a Mac use ⌘+T.
• The formula shows as text instead of a result. The cell is formatted as Text, or there’s a space or an apostrophe before =. See the apostrophe and equal sign at the start of a cell.
• The $ signs are there but the reference still changed. Rows were inserted or deleted: $ doesn’t protect against that, use INDIRECT.

Absolute and relative references: FAQ

What does the $ sign mean in an Excel formula?

The dollar sign locks part of a reference when the formula is copied: $A$1 locks the column and the row, $A1 only the column, A$1 only the row. It has nothing to do with currency.

How do I make an absolute reference in Excel?

Put the cursor on the reference in the formula and press F4: A1 becomes $A$1. Or type the $ signs by hand.

What is the difference between relative and absolute references?

A relative reference shifts by as many rows and columns as the formula was moved. An absolute reference always points to the same cell.

What is a mixed reference?

A reference with only one part locked: $A1 (column) or A$1 (row). It’s needed when a formula is filled both down and right.

How do I lock a cell in a formula on a laptop?

Press Fn+F4 while editing the formula. In Excel for Mac press ⌘+T.

How do I copy a formula without changing references?

Copy the formula text from the formula bar, press Esc and paste it into the target cell. Or press Ctrl+' in the cell below the formula.

Summary: absolute and relative references in Excel

I hope you found what you were looking for on this page and now place dollar signs in formulas with confidence. What the dollar sign, exclamation mark, quotes and square brackets mean in formulas is covered in the article on Excel special characters.