How do relative, absolute, and mixed cell references differ in Excel?
Relative references change when a formula is copied, absolute references remain fixed, and mixed references keep either the row or column fixed. The dollar sign $ determines which part of a cell reference stays constant.
Read full answerHide full answer
Applies toMicrosoft Excel 365
How References Behave
Excel uses the column letter and row number to locate a cell. Copying a formula adjusts those references according to the reference type.
| Reference type | Example | Behaviour when copied one column right | Behaviour when copied one row down |
|---|---|---|---|
| Relative | =A1 |
Becomes =B1 |
Becomes =A2 |
| Absolute | =$A$1 |
Remains =$A$1 |
Remains =$A$1 |
| Mixed: fixed column | =$A1 |
Becomes =$B1 |
Remains =$A2 |
| Mixed: fixed row | =A$1 |
Remains =A$1 |
Becomes =A$2 |
The dollar sign does not make the cell’s value constant; it controls how the formula responds when it is copied.
Copying References: Expected Results
Suppose these formulas are placed in cell B1:
Formula in B1 |
After copying to C1 |
After copying to B2 |
|---|---|---|
=A1 |
=B1 |
=A2 |
=$A$1 |
=$A$1 |
=$A$1 |
=$A1 |
=$B1 |
=$A2 |
=A$1 |
=A$1 |
=A$2 |
While editing a formula in Excel 365, select a cell reference within the formula and press F4 on Windows or Control+T on Mac to cycle through relative, absolute, and mixed references.
Business Example
Consider the following sales data:
| A | B | |
|---|---|---|
| 1 | Employee | Sales |
| 2 | Alice | 100 |
| 3 | Bob | 250 |
| 4 | Carol | 500 |
Enter the shared commission rate of 8% in cell F1. In D2, enter:
=B2*$F$1
Copy the formula down through D4:
| Cell | Formula | Commission |
|---|---|---|
D2 |
=B2*$F$1 |
8 |
D3 |
=B3*$F$1 |
20 |
D4 |
=B4*$F$1 |
40 |
The sales reference changes relatively so that each employee’s sales value is used, while $F$1 remains fixed. Without the dollar signs, copying the formula down would change the rate reference to F2, F3, and F4.
When to Use Each Type
-
Relative references: Use for calculations that should follow each row or column, such as
=B2*C2. -
Absolute references: Use for constants, fixed lookup ranges, or parameters that must not move, such as
=$F$1. -
Mixed references: Use when copying a formula across rows or columns while retaining one reference dimension, such as
=A$1for a fixed row. -
Formulas combining reference types: Use when one part changes and another remains fixed, such as
=B2*$C$1.
Best Practices and Common Pitfalls
-
View the formula in the formula bar to check whether its references contain
$markers. -
Use named ranges for important assumptions instead of repeatedly relying on absolute references. A formula such as
=B2*CommissionRateis easier to audit than=B2*$H$1. -
Use Excel Tables and structured references when possible because they adjust automatically as rows are added.
-
Lock references only when required; unnecessary dollar signs make formulas harder to maintain.
-
Pasting a copied formula into a cell that already contains a value replaces that value with the formula. Use Paste Special → Formulas when transferring only the formula without the source formatting.