About this lesson
Copying formulas and locking references with $ in Google Sheets
In Google Sheets, put a $ before the column letter and row number (for example $G$1) to lock a reference so it doesn't move when you copy the formula. While typing a formula, F4 adds the $ signs for you (fn+F4 on a MacBook).
Using Excel? Do this lesson in Excel.
=B2*$G$1Worked example
Translate regional revenue from AUD to USD with one FX rate, copy the formula down, and see why $G$1 stays put.
| Cell | What it works out | Formula | Result |
|---|---|---|---|
| C2 | New South WalesRevenue (US$m) | =B2*$G$1 | 315.0 |
| C3 | VictoriaRevenue (US$m) | =B3*$G$1 | 202.3 |
| C4 | QueenslandRevenue (US$m) | =B4*$G$1 | 265.3 |
| C5 | Western AustraliaRevenue (US$m) | =B5*$G$1 | 90.3 |
| C6 | New ZealandRevenue (US$m) | =B6*$G$1 | 44.8 |
The data
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Revenue (A$m) | Revenue (US$m) | AUD/USD | 0.65 | ||
| 2 | New South Wales | 450 | |||||
| 3 | Victoria | 289 | |||||
| 4 | Queensland | 379 | |||||
| 5 | Western Australia | 129 | |||||
| 6 | New Zealand | 64 |