Excel Cell References: Relative, Absolute, and Mixed

Learn how Excel cell references work, when to use dollar signs, and how relative, absolute, and mixed references change when formulas are copied.

By: Ethan Mercer Published: October 9, 2026

A cell reference tells Excel where to find a value. The reference B2, for example, points to the cell at the intersection of column B and row 2. What matters when you copy a formula is whether that column, that row, or both should move with the formula.

The rule is compact: a dollar sign fixes the part of the address immediately after it. With no dollar signs, both parts can change; with two, neither part changes. Once that rule is clear, relative, absolute, and mixed references become predictable.

Excel cell references at a glance

Reference	What stays fixed when copied	What can change	Typical use
`B2`	Nothing	Column and row	A calculation repeated for each record
`$B$2`	Column B and row 2	Nothing	One fixed rate, setting, or input
`$B2`	Column B	Row	A value column used across a two-dimensional grid
`B$2`	Row 2	Column	A header row used across a two-dimensional grid

B2 is a relative reference, $B$2 is an absolute reference, and the other two forms are mixed references. The dollar sign affects copying or filling a formula. It does not protect the referenced cell, freeze its displayed value, or prevent someone from editing it.

Microsoft’s guide to switching between reference types provides the current definitions and another copy-offset example.

How Excel reads an A1-style reference

Excel uses A1 reference style by default. Letters identify columns and numbers identify rows, so D7 means column D, row 7. A colon identifies a rectangular range: B2:B10 covers cells B2 through B10.

A formula can also point to another worksheet. An exclamation mark separates the sheet name from the cell or range:

=Sheet2!B2
='Price List'!$B$2

Single quotation marks are required around a sheet name when it contains a space or certain non-alphabetic characters. The dollar signs apply only to the cell address after the exclamation mark.

Microsoft’s overview of formulas and references documents A1 references, ranges, other-sheet references, and the alternative R1C1 reference style.

Predict a copied reference by following the offset

A relative reference is not tied to its displayed address. It describes a position relative to the formula cell. If a formula is copied one row down, every relative row in that formula also moves one row down. If the formula is copied two columns right, every relative column moves two columns right.

Suppose a formula contains B2. Copy the formula one row down and two columns right, and the reference becomes D3. Apply the same move to all four reference types:

Original reference	After copying one row down and two columns right	Reason
`B2`	`D3`	Both parts are relative.
`$B$2`	`$B$2`	Both parts are absolute.
`$B2`	`$B3`	The column is fixed; the row moves.
`B$2`	`D$2`	The row is fixed; the column moves.

This offset rule applies whether you copy and paste the formula or extend it with the fill handle.

Use relative references for row-by-row calculations

Assume an order sheet stores quantity in column B, unit price in column C, and line total in column D. In D2, enter:

=B2*C2

Both references are relative. When you copy the formula from D2 to D3, Excel changes it to:

=B3*C3

That is the desired behavior: each total uses the quantity and price from its own row. If you copy the original formula across to E2, however, it becomes =C2*D2 because both columns move one place right. A correct reference depends on the direction in which the formula will be copied.

Enter the formula in the first result cell.
Select that cell and point to the small square at its lower-right corner.
Drag the fill handle down through the required rows, or copy and paste the formula into the destination range.
Select the first copied cell and inspect its formula before filling a large dataset.

Relative references are the default when you select cells while building a formula. You add dollar signs only when a row or column should remain fixed.

Use an absolute reference for one fixed input

Now suppose H1 contains a tax rate that applies to every order. If the pre-tax total is in D2, this formula calculates the amount including tax:

=D2*(1+$H$1)

When you fill the formula down, D2 becomes D3, D4, and so on. The reference $H$1 remains unchanged because both its column and row are absolute.

Formula cell	Formula after filling
`E2`	`=D2*(1+$H$1)`
`E3`	`=D3*(1+$H$1)`
`E4`	`=D4*(1+$H$1)`

If you used H1 instead, the copied formulas would point to H2, H3, and later rows. Those cells may be blank or contain unrelated data, producing incorrect results without necessarily producing an error message.

An absolute reference keeps the address fixed while a formula is copied. If the value in H1 changes, every formula that refers to $H$1 can still recalculate with the new value.

Use mixed references for a two-dimensional grid

Mixed references matter when a formula is copied both across columns and down rows. Imagine a pricing grid with base costs in B2:B5 and multipliers in C1:F1. In C2, enter:

=$B2*C$1

The formula needs two different anchors:

$B2 always reads the base cost from column B, but its row changes as the formula moves down.
C$1 always reads the multiplier from row 1, but its column changes as the formula moves across.

After filling across and down, the formulas adjust like this:

Destination	Resulting formula	What changed
`C2`	`=$B2*C$1`	Starting formula
`D2`	`=$B2*D$1`	The multiplier column moved right.
`C3`	`=$B3*C$1`	The base-cost row moved down.
`D3`	`=$B3*D$1`	Both relative parts moved.

A useful way to choose a mixed reference is to ask two separate questions: “Should the column move?” and “Should the row move?” Put a dollar sign before the answer that should stay fixed.

Add dollar signs with a keyboard shortcut

You can type dollar signs directly, or cycle through the four reference forms while editing a formula.

Select the cell containing the formula and enter edit mode.
Click inside a cell reference in the formula bar, or select the whole reference.
On Windows, press F4. In Excel for Mac, use Command+T or F4.
Continue pressing the shortcut until the reference has the required combination of dollar signs.

The usual cycle is B2, $B$2, B$2, and $B2, then back to B2. If a laptop uses its function keys for brightness, volume, or other system controls, you may need to hold the Fn key with F4. Microsoft lists the current Windows and Mac options in its Excel keyboard shortcut reference.

Copying and moving a formula are different

Reference types control what happens when a formula is copied. When you copy and paste a formula or use the fill handle, its relative parts adjust for the destination.

When you move a formula with Cut and Paste, the references inside that formula stay the same, regardless of whether they are relative, absolute, or mixed. The formula has changed location, so you should still check that its unchanged references point to the intended cells.

This distinction is easy to miss when reorganizing a worksheet. Microsoft’s guide to moving or copying formulas documents both behaviors and the available paste options.

Reference ranges and cells on other sheets

Dollar signs can be applied to each endpoint of a range. For example, $B$2:$B$10 keeps both ends fixed when the formula is copied. A partially fixed range is also possible, but use one only when the expanding or shifting behavior is intentional.

The same patterns work with another worksheet:

=SUM('January Sales'!B2:B10)
=B2*Settings!$B$1

In the first formula, the range is relative and may shift if the formula is copied. In the second, the local value B2 can change by row while the rate on the Settings sheet remains fixed at $B$1.

Excel tables use a different syntax called structured references. Inside a table named Orders, for example, a calculated-column formula could be =[@Quantity]*[@[Unit Price]]. These references use table and column names and follow their own rules when copied or filled. Microsoft’s structured reference guide is the appropriate resource when your formula contains square brackets rather than A1-style addresses.

Fix common cell-reference mistakes

Problem	Likely cause	Correction
A fixed rate moves down with the formula	The rate uses a relative reference such as `H1`.	Use an absolute reference such as `$H$1`.
A grid works down but fails across	The source column was not fixed.	Test a mixed reference such as `$B2`.
A grid works across but fails down	The header row was not fixed.	Test a mixed reference such as `C$1`.
The formula returns `#REF!`	A referenced cell, row, column, or sheet may have been deleted or become invalid.	Find `#REF!` in the formula and restore the intended reference.
Pasting shows only numbers	The destination received values rather than formulas.	Use normal Paste or Paste Special > Formulas, depending on what else should be retained.
F4 changes a system setting	The keyboard treats function keys as hardware controls.	Use `Fn+F4`, the Mac shortcut, or type the dollar signs directly.

Do not add dollar signs to every reference as a general fix. An absolute address can stop a formula from adapting where it should. Decide separately whether the column and row need to move.

Check a formula before filling the entire range

Write the first formula and confirm its result with known inputs.
Decide whether each referenced column should move when copied across.
Decide whether each referenced row should move when copied down.
Add a dollar sign only before a column letter or row number that must stay fixed.
Copy the formula one cell in every direction you plan to fill.
Inspect those copied formulas before extending them through the full range.

That small test catches most reference errors while the formula is still easy to understand.

Frequently asked questions

What does the dollar sign mean in an Excel formula?

It makes the next part of a cell address absolute when the formula is copied. In $B2, column B is fixed. In B$2, row 2 is fixed. In $B$2, both are fixed.

Does an absolute reference stop a cell value from changing?

No. It keeps the reference address from shifting during a copy or fill operation. The value in the referenced cell can still be edited, recalculated, or replaced.

Why did Excel change my formula when I dragged it down?

New A1-style references are relative by default, so their row numbers adjust as a formula is filled down. Add a dollar sign before the row number only if that row must stay fixed.

Can one formula contain different reference types?

Yes. The formula =D2*(1+$H$1) deliberately combines a relative reference for the row’s total with an absolute reference for one shared rate.

Do dollar signs change a formula before it is copied?

No. In its original location, =B2 and =$B$2 read the same cell. The difference appears when the formula is copied or filled elsewhere.

Lock only what must stay fixed

Good cell references describe the movement a formula needs. Use relative references for calculations that repeat by row or column, absolute references for a single shared input, and mixed references when a formula must travel across a two-dimensional grid.

When in doubt, copy the formula one cell and inspect the result. The answer is usually visible in the part of the address that moved—or the part that did not.