Absolute and mixed references
One dollar sign decides whether a copied formula keeps looking at the tax rate in B1 or slides down to an empty cell.
Why the tax column goes wrong after one fill
Start with a puzzle. The tax rate, 10%, sits in B1, and four prices sit in A4 to A7. Someone types =A4*B1 in B4, gets 2, which is right, and fills it down to B7. Now B5 shows 0, B6 shows #VALUE! and B7 shows 25. Before reading on, try to say why each of those three cells went wrong.
Click B5 and read its formula: =A5*B2. Filling down moved every ordinary reference one row. The price reference went from A4 to A5, as it should, but the rate reference went from B1 to B2 as well, and B2 is empty. An empty cell counts as 0 in multiplication, so B5 quietly shows 0. B6 now multiplies by B3, which holds the heading Tax; Microsoft's #VALUE! page notes that operators like + and * may not be able to calculate cells that contain text, hence the error. B7 multiplies by B4, the first tax result, and gets 12.5 × 2 = 25. That one looks believable, which makes it the worst of the three. One fill, three wrong answers, and only one of them is an error message.
The fix is to tell the spreadsheet that B1 must never move. A dollar sign in front of part of an address pins that part. $B$1 pins both the column and the row, which makes it an absolute reference: it means B1 wherever the formula ends up. Write the first tax as =A4*$B$1, fill down, and you get =A5*$B$1, =A6*$B$1 and so on. Microsoft's own example is =$B$4*$C$4, which stays exactly the same when copied from D4 to D5. The sign has nothing to do with money. It is a marker.
Keeping the rate in its own cell pays off a second time. If the rate changes from 10% to 12%, you edit B1 and every row recalculates at once. Had the rate been typed into each formula as *0.1, you would have to hunt down every copy, and the one you miss leaves a wrong figure that looks perfectly normal. So put anything that might change (rates, limits, exchange rates) in labelled cells near the top. Point at them with absolute references.
Drag to turn. Scroll or pinch to zoom.
Pinning only the row, or only the column
You can pin just one part. In A$1 the row is pinned and the column moves; in $A1 it is the other way round. Microsoft's table copies each form two cells down and two cells right: $A$1 stays $A$1, A$1 becomes C$1, $A1 becomes $A3, and A1 becomes C3. You will seldom type the signs by hand. While editing a formula, put the cursor on a reference and press F4. Each press steps to the next form (A1, $A$1, A$1, $A1) and then back to A1. Google's shortcut list gives F4 for Sheets as well, and Fn+F4 on a Mac keyboard.
| Reference | Column | Row | Copied 2 down, 2 right |
|---|---|---|---|
| A1 | moves | moves | C3 |
| $A$1 | fixed | fixed | $A$1 |
| A$1 | moves | fixed | C$1 |
| $A1 | fixed | moves | $A3 |
Where do mixed references earn their keep? In a grid with one input along the top and another down the side, and a multiplication table is the classic case. Put 1 to 5 in B1:F1 and again in A2:A6, then write =B$1*$A2 in B2. Fill it across to F2 and down to F6. Each of the twenty-five copies still reads its top number from row 1 and its side number from column A. One formula fills the whole grid correctly.
Drag to turn. Scroll or pinch to zoom.
- Filling =A4*B1 down without a $. The rate reference slides to B2, B3 and B4 along with the prices, giving 0, #VALUE! and a wrong 25 instead of three taxes.
- Putting the $ on the wrong part: =A4*B$1 fills down fine, =A4*$B1 does not. $B1 pins only the column, so the row still moves. Decide which way the formula will be copied, then pin the part that would move that way.
- Typing the rate into every formula as *0.1. It works until the rate changes. Then every copy has to be found and edited, and a missed one still looks normal.
- Pressing F4 in Google Sheets outside a formula and waiting for a $. F4 switches reference types only while you are entering a formula. Elsewhere it is one of the Redo keys.
- =A4*$B$1 is copied one row down. What does it become?
- In the formula bar, the cursor is on A1. You press F4 once. What does A1 become?
- And after pressing F4 a second time?
- =A$1 is copied two cells down and two cells right. What does it become?
- =$A1 is copied two cells down and two cells right. What does it become?
- =B$1*$A2 in B2 is copied to C3. What does it become?
- In that table, what value does =E$1*$A5 give?
- B1 holds 10% and B4 holds =A4*B1. You fill B4 down to B7. What goes wrong?
- The rate reference slides off B1, so B5 shows 0 and the rows below go wrong too
- Every row shows #REF!
- Every row shows the same price
- Nothing goes wrong
- Which reference keeps the row fixed but lets the column move?
- A$1
- $A1
- $A$1
- A1
- Which key switches reference types on a Mac in Google Sheets?
- Fn+F4
- Ctrl+D
- Ctrl+~
- Shift+F2
- $ fixes the part after it: $A$1 both, A$1 the row, $A1 the column.
- Use $B$1 for a single input cell that every row shares.
- F4 cycles A1, $A$1, A$1, $A1 (Fn+F4 on a Mac in Google Sheets).
- Mixed references let one formula fill a whole grid.
Sources for this course (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Checked: 2026-10-19