Introduction
The real power of formulas shows when you copy them. Write one formula, copy it down a thousand rows, and Excel adjusts it for each row. That works because references are relative by default. But sometimes a formula must keep pointing at one cell, such as a tax rate, and then you need absolute references. Understanding the difference is the single most important formula skill in Excel.
How it works
A relative reference such as B2 is stored as a position relative to the formula's own cell: "one column to the left, same row". When you copy =B2*C2 from D2 to D3, it becomes =B3*C3; copied to E2, it would become =C2*D2. That is usually exactly what you want for row-by-row calculations.
An absolute reference has dollar signs: $F$1 always means F1, wherever the formula is copied. Use it for a single input that every row needs, such as a tax rate or an exchange rate.
A mixed reference locks only one part. $A2 keeps column A but lets the row change; A$2 keeps row 2 but lets the column change. Mixed references are what make a multiplication table, or any grid that combines a row heading with a column heading, work from one formula.
While editing a formula, put the cursor in a reference and press F4 on Windows (on Mac, Command+T or F4, depending on your keyboard settings) to cycle through $A$1, A$1, $A1 and back to A1.
A named range gives a cell or range a meaningful name. Select F1, type TaxRate in the Name Box and press Enter. Now =D2*TaxRate reads like a sentence. Names are absolute, work on every sheet of the workbook, and are listed in the Name Manager on the Formulas tab.
To use a cell on another sheet, put the sheet name and an exclamation mark before the reference: =Prices!B2. If the sheet name contains spaces, wrap it in single quotes: ='Price list'!B2. Clicking the other sheet while writing a formula inserts this for you.
Example
An invoice adds 15% VAT, with the rate in F1:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Price | VAT | Total | 0.15 | |
| 2 | Pen | 200 | ||||
| 3 | Bag | 900 |
Line by line
- In C2 type
=B2*F1and copy it to C3. C3 shows 0, because the copied formula became=B3*F2and F2 is empty. - Fix C2: change it to
=B2*$F$1(type the dollar signs or press F4 after F1) and copy it to C3 again; C3 now holds=B3*$F$1. - In D2 type
=B2+C2and copy it to D3; relative references are right here. - Optionally name F1 TaxRate and write C2 as
=B2*TaxRate.
Expected result
After step 2, C2 shows 30 and C3 shows 135. After step 3, D2 shows 230 and D3 shows 1035. Changing F1 to 0.1 updates every VAT and total at once.
In the real world
Pricing sheets, payroll and financial models keep their assumptions, such as rates, targets and exchange rates, in a separate block of input cells, and every formula refers to them with absolute references or names. Errors from a missing dollar sign are among the most common spreadsheet mistakes found in audits, so experienced users check copied formulas with Ctrl+Grave accent before trusting them.
Common mistakes
- Forgetting the $ on a fixed input, so copies point at empty cells and show 0 or wrong values.
- Locking everything with $ so that copied formulas all repeat the first row's result.
- Deleting a row a formula refers to, which leaves
#REF!behind. - Typing a sheet name with spaces without quotes in a cross-sheet reference.
Best practices
- Keep inputs in clearly labelled cells and refer to them absolutely or by name.
- After copying a formula, check two or three copies to see that references moved as intended.
- Use names for important inputs; they document the model as you build it.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Cycle reference types while editing | F4 | Command+T |
| Paste a name into a formula | F3 | — |
| Open the Name Manager | Ctrl+F3 | — |
| Show or hide formulas to check copies | Ctrl+Grave accent | Control+Grave accent |
Summary
References are relative by default and shift when a formula is copied. A dollar sign locks the column, the row or both, making references absolute or mixed, and F4 cycles between them while editing. Named ranges give inputs readable names, and sheet references use an exclamation mark, with quotes around names that contain spaces.
Quick check
Key takeaways
- Relative references move with a copied formula; $ locks the column, the row or both.
- F4 (Windows) cycles a reference through $A$1, A$1, $A1 and A1 while you edit.
- Named ranges make formulas easier to read and are absolute by default.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.