Google Sheets web app (rolling service) · Calculation tools, not function references · Beginner
Copy formulas with fixed and relative references in Google Sheets
Copy formulas with fixed and relative references in Google Sheets with a worked fixture, expected results and safe recovery.
Documentation-reviewed candidate; native application verification not performed. · Editorial standards
Quick answer
Use dollar signs to fix the row, column or both in a formula reference before copying it. For a two-way price grid, =$A2*B$1 fixes the input column and rate row while allowing the other coordinate to move.
Prepare a two-direction fixture
Use an editable native Google Sheets file in a desktop browser. This walkthrough uses A1 notation and ordinary arithmetic, so it needs no special Workspace edition. Keep the exercise on a private tab with empty output cells; do not practice by filling across a live report.
Enter this grid, leaving B2:D4 blank:
| Quantity | 5 | 6 | 8 |
|---|---|---|---|
| 2 | |||
| 3 | |||
| 4 |
The table occupies A1:D4. A2:A4 holds quantities 2, 3 and 4; B1:D1 holds three possible unit prices. Each output is the quantity at the left multiplied by the price above. The expected complete matrix is:
| Quantity | Price 5 | Price 6 | Price 8 |
|---|---|---|---|
| 2 | 10 | 12 | 16 |
| 3 | 15 | 18 | 24 |
| 4 | 20 | 24 | 32 |
These nine independent products provide stronger evidence than checking only the first formula. Preserve the input fixture before trying deliberately broken references.
Build and inspect the mixed-reference grid
- Enter
=$A2*B$1in B2 and commit the formula. It should display 10. - Copy B2 and paste it into C2:D2. Inspect C2’s formula: it should be
=$A2*C$1. D2 should use D$1. - Select B2:D2, copy, and paste into B3:D4.
- Inspect D4: it should contain
=$A4*D$1and display 32. - Compare all nine outputs with the expected matrix above.
The dollar before A keeps every formula reading the quantity column, even when copied right. The dollar before 1 keeps every formula reading the price row, even when copied down. Neither locks the input’s value: changing A4 still changes formulas that refer to it.
Contrast relative and fully fixed references
A reference A2 moves both coordinates when copied. $A$2 fixes both. $A2 fixes only the column; A$2 fixes only the row. The dollar sign is part of the reference syntax, not a currency format or permission setting.
On a separate practice copy, replace B2 with =A2*B1 and copy it to C2. The copied formula becomes =B2*C1: it multiplies B2’s calculated 10 by C1’s 6 and returns 60, not the intended 12. This failure is useful because it produces a plausible number instead of an obvious error. Restore the correct formula in the main exercise, not just the displayed value.
For a fully fixed example, enter F1 = 0.1 and E2 = =B2*$F$1. Copy E2 down to E4. Results should be 1, 1.5 and 2, each using the same F1 while the B row advances. Do not type the percentage separately into each formula; one shared input makes later changes auditable.
Test changes and leave a verified final result
Change D1 from 8 to 10. The rightmost output column should become 20, 30 and 40; columns B and C should not change. Change F1 from 0.1 to 0.2: E2:E4 becomes 2, 3 and 4. Restore D1 = 8 and F1 = 0.1 for the final saved exercise.
While editing a formula, Google’s documented reference-toggle shortcut is F4 on PC, Fn+F4 on Mac and Search+4 on ChromeOS. Hardware function-key behavior can vary. Typing the dollar signs directly is the clearest way to produce the exact form required here; always inspect the resulting reference rather than counting shortcut presses.
Troubleshooting and recovery
If every row repeats the first quantity, check whether you accidentally fixed row 2 as well. If a rightward copy starts multiplying a previous result, check the missing dollar before A. If a downward copy points at an output instead of the rate row, check the missing dollar before 1.
Reopen after saving and inspect B2, D4 and E4’s formulas, not only their numbers. Recover a damaged grid by rebuilding it from the preserved inputs and the one known-good formula. Fixed references are not cell protection; they control copy behavior, not who can edit a cell.
Official references
- Use conditional formatting rules in Google Sheets - Computer - Google Docs Editors Help
- Keyboard shortcuts for Google Sheets - Computer - Google Docs Editors Help
- Add formulas & functions - Computer - Google Docs Editors Help
- Copy and paste text and images - Computer - Google Docs Editors Help
Documentation basis: first-party help snapshots captured 20 October 2026. Fixture arithmetic and stated changes were checked independently. No native Google Sheets session, Excel round trip, screenshot, sharing action or export was executed for this article.