Understanding the difference between relative and absolute references in Excel helps you copy formulas without changing the cells they depend on by accident.
A small dollar sign can keep a tax rate fixed while a formula adjusts to each row.
What is a cell reference in Excel?
A cell reference identifies a cell or range used by a formula.
For example, =A2+B2 adds the values in cells A2 and B2.
When you copy or fill a formula into another cell, Excel may adjust those references depending on their type.
Excel supports relative, absolute, and mixed references.
These behaviors apply in current desktop versions of Excel for Windows and Mac, Excel for the web, and Google Sheets.
Some menu locations and keyboard shortcuts vary by app and operating system.
What is a relative reference?
A relative reference changes based on where you copy the formula.
In =A2+B2, both references are relative because neither includes a dollar sign.
If you copy this formula from C2 to C3, it becomes =A3+B3.
Relative references are useful when the same calculation should use corresponding cells in each row or column.
For example, enter =B2*C2 in D2 to calculate a line total from quantity and price.
Fill the formula down through D3:D10, and Excel adjusts it to =B3*C3, =B4*C4, and so on.
What is an absolute reference?
An absolute reference stays pointed at the same cell when you copy a formula.
Add a dollar sign before both the column letter and row number to make a reference absolute: $F$1.
Suppose F1 contains a sales tax rate of 8%, and B2 contains a price of $50.
In C2, enter =B2*$F$1.
The result is $4.
If you copy the formula to C3, the formula becomes =B3*$F$1: the price reference changes, but the tax-rate reference remains fixed.
Absolute references are especially useful for shared inputs such as tax rates, commission percentages, exchange rates, thresholds, and lookup values stored in a fixed location.
What is a mixed reference?
A mixed reference locks either the column or the row, while allowing the other part to adjust.
Use $A2 to lock column A and A$2 to lock row 2.
$A2: column A stays fixed; the row can change.A$2: row 2 stays fixed; the column can change.
Mixed references are helpful in formulas filled across a grid.
For example, a multiplication table can put input values in A2:A6 and B1:F1.
Enter =$A2*B$1 in B2, then fill it across and down through F6.
The locked column keeps each row’s first value anchored in column A, while the locked row keeps each column’s header anchored in row 1.
Reference types at a glance
| Reference | Behavior when copied | Example use |
|---|---|---|
A1 | Column and row adjust | Row-by-row calculations |
$A$1 | Column and row stay fixed | Fixed rate or input |
$A1 | Column fixed; row adjusts | Calculations using one input column |
A$1 | Row fixed; column adjusts | Calculations using one header row |
How to change a reference with F4
While editing a formula, select or place the cursor on a cell reference and press F4 to cycle through reference types.
The sequence typically moves among A1, $A$1, A$1, and $A1.
On some laptops, you may need Fn+F4; Mac keyboard behavior can depend on system settings and the Excel version.
You can also type the dollar signs directly.
In Google Sheets, reference notation works the same way; its shortcut may differ by browser, keyboard, or operating system, so typing the symbols is a reliable alternative.
How to choose the right reference
Before filling a formula, ask which parts should move when the formula is copied:
- Use a relative reference if both the row and column should follow the formula’s new position.
- Use an absolute reference if the formula must keep pointing to one specific cell.
- Use a mixed reference if only the row or column should stay fixed.
For example, if E2 contains a discount rate and B2 contains an item price, =B2*(1-$E$2) calculates the discounted price.
Copying the formula down changes B2 to B3, while keeping the discount rate in E2.
If a formula is copied across a report where the input must always come from column B, =$B2 keeps that column fixed as the row changes.
Common mistakes and edge cases
- Locking too much: Using
=$B$2for a row-by-row calculation prevents both the row and column from adjusting.
Check whether you intended a fixed value or a changing row. - Locking only one part:
B$2fixes the row, not the column.
If you fill the formula down, the row stays 2; if you fill it across, the column changes. - Assuming references never shift: Inserting or deleting rows and columns can update formulas to preserve their references.
Absolute references prevent changes caused by copying, but they are not an instruction to ignore structural edits. - Copying between sheets: References can include a sheet name, such as
=Sheet2!$A$1.
The dollar signs lock the cell address; the sheet name identifies the worksheet. - Using a whole range: An absolute range such as
$A$2:$A$100keeps both endpoints fixed when copied.
This is common in lookup formulas, though newer Excel functions and Google Sheets may support different lookup approaches.
If a copied formula returns an unexpected result, select the destination cell and inspect the formula bar.
Compare each reference with the source formula to see which row, column, or range changed.