Copying formulas and cell references
Understand relative, absolute and mixed references so formulas copied down or across keep working, with real before-and-after results.
In this lesson you will
- Explain relative references
- Lock cells with dollar signs
- Use mixed references
- Check copied formulas
About 14 minutes
The formula that broke when I copied it
Leela has a price list and wants to add tax to every item. She types the first formula, =B4*B1, where B1 holds the tax rate. It works. She drags it down to the next rows and the answers turn into nonsense. Nothing is broken in Excel; she has just met relative references. Understanding this one idea will save you hours for the rest of your spreadsheet life.
Relative references: Excel adjusts them when you copy
By default a cell address in a formula is relative. It means ‘the cell this far away from me’. When you copy the formula one row down, every relative address moves one row down too. That is brilliant when you want it, as in a Total column where each row should use its own price. It is a problem when you want one address to stay fixed, like the tax rate in B1.
Absolute references: the dollar sign locks
A dollar sign in front of the column letter or the row number locks it. $B$1 means ‘always column B, always row 1’, wherever the formula is copied. You can type the dollar signs, or select the address in the formula and press F4, which cycles through the options.
| Type | Looks like | When you copy |
|---|---|---|
| Relative | B1 | Column and row both move |
| Absolute | $B$1 | Neither moves |
| Mixed | $B1 or B$1 | Only the unlocked part moves |
Seeing the difference with real numbers
In a test sheet the tax rate 0.18 was in B1 and four prices from row 4 down were 100, 250, 80 and 400. Column C used $B$1, while column D used a relative B1 that was copied down. These results were really calculated:
| Row | Price | Locked tax cell | Relative tax cell |
|---|---|---|---|
| Row 4 | 100 | =B4*$B$1 -> 18 | =B4*B1 -> 18 |
| Row 5 | 250 | =B5*$B$1 -> 45 | =B5*B2 -> 0 |
| Row 6 | 80 | =B6*$B$1 -> 14.4 | =B6*B3 -> 0 |
| Row 7 | 400 | =B7*$B$1 -> 72 | =B7*B4 -> 40000 |
In the locked column every row multiplies by the tax rate. In the relative column, as the formula was copied down, the reference slid from B1 to B2, B3 and B4. B2 and B3 are empty, so rows 5 and 6 multiplied by nothing and showed 0, and row 7 multiplied a price by another price (400 times 100) to give 40000. The first row looks right, which is why this mistake hides so well. Always test the last row, not just the first.
Mixed references and a multiplication grid
Sometimes you want to lock only the row or only the column. A classic example is a multiplication grid with numbers along the top and down the side. The formula =$A2*B$1 locks column A for the side numbers and row 1 for the top numbers, so one formula copied across and down fills the whole grid. A real recalculation of a two-row, three-column grid, with 1, 2, 3 across the top and 10 and 20 down the side, gave:
| 1 | 2 | 3 | |
|---|---|---|---|
| 10 | 10 | 20 | 30 |
| 20 | 20 | 40 | 60 |
Copying formulas quickly
- Type the formula in the first cell and press Enter.
- Select that cell and drag the fill handle (the small square at its bottom-right) down the column. Or double-click the handle to fill as far as the neighbouring data goes.
- Click a few of the filled cells and read their formulas to confirm the references moved the way you intended.
Choosing the right type: a quick test
Ask: ‘When this formula is copied, which cells should stay put?’ The tax rate, a fee, an exchange rate or a total you compare against should be locked. The price on the same row should move. Say it in words, then add dollar signs where something must stay fixed.
Practice
Build a price list with a discount percentage in one cell. Write a Sale Price formula that multiplies each price by one minus the discount, locking the discount cell. Copy it down ten rows and check the last one. Then try the multiplication grid for 1 to 5 using one formula only.
The sale price formula is =B4*(1-$B$1) if the discount is in B1 and the price is in B4. For the grid use =$A2*B$1 copied across and down. If the grid shows the same number everywhere, you have probably locked both parts of an address.
Checking only the first row after copying a formula. A relative reference that should be absolute looks correct in the first row and goes wrong further down.
Naming a cell to make formulas readable
When one cell, like a tax rate, is used everywhere, you can give it a name. Select the cell, click the Name Box at the top left, type TaxRate and press Enter. Now a formula can say =B4*TaxRate, which reads clearly and never slides when you copy it. Names behave like absolute references automatically. They are optional at this stage, but they are a friendly step up from dollar signs once you are comfortable with them.
Cells on other sheets
References to another sheet work the same way: =Prices!$B$1 points to B1 on the sheet named Prices. Locking the address is just as important there, because a copied formula would otherwise slide on that sheet too. Keep inputs such as rates on one clearly labelled sheet, and point your calculations at them.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does $B$1 mean?
- Cost in dollars
- B1 plus one
- A text label
- Always column B and row 1
Dollar signs lock the address.
Which key cycles between relative and absolute?
- F4
- F1
- F12
- Tab
Select the address in the formula and press F4.
A formula refers to B1 and is copied one row down. What does it refer to now?
- B1
- C1
- B2
- A1
Relative references move with the copy.
Which reference locks only the row?
- $B1
- $B$1
- B1
- B$1
The dollar sits before the row number.
Which row should you check after copying a formula down?
- Only the first
- The last one
- None
- The title
Errors from relative references show up further down.
Summary
- Relative references move when copied; absolute ones do not.
- Dollar signs (or F4) lock the column, the row or both.
- Mixed references fill grids with one formula.
- Always check the last row after copying.
Questions
What is the fastest way to copy a formula down a long column?
Double-click the fill handle. Excel fills down as far as the data next to it goes.
Why do my copied formulas show the same number?
Calculation may be set to manual, or you locked an address that should have moved. Check the formula in a few cells.
Do references work the same in Google Sheets?
Yes. Relative, absolute and mixed references, and the dollar signs, work identically.
