Tax rate cell
=C2*(1+$E$1) fills down column D while E1 rate stays fixed for every row.
What is an absolute reference in Google Sheets?
Absolute references lock row, column, or both with dollar signs so a formula keeps pointing at the same cell when you copy it. $A$1 locks both axes. A$1 locks the row. $A1 locks the column. Mixed locks are how tax rates stay in one settings cell while a formula fills down a thousand rows.
When to use it
Use absolute refs for constants, tax rates, exchange rates, date boundaries, and lookup table corners when dragging formulas across grids.
When to skip it
Skip locking everything; overusing $A$1:$Z$1000 fights intentional relative movement when you copy sideways through months.
How it works
-
1
Type $ before column letter and/or row number in the formula bar, or press F4 while the cursor is on the reference (on supported keyboards).
-
2
Drag-fill down a column with row-relative and column-locked refs for running calculations against fixed Settings!$B$1 rate.
-
3
VLOOKUP tables use $ on the lookup array so copy-down does not slide the table address.
-
4
Combine with named ranges to reduce dollar-sign noise on templates many people maintain.
-
5
In conditional formatting custom formulas, lock columns that must stay fixed while rows move.
-
6
Test copy right and copy down after building the first formula cell.
Examples in Google Sheets
VLOOKUP table anchor
=VLOOKUP(A2,$G$2:$H$500,2,FALSE) copied down keeps lookup table glued to G2:H500.
Running total boundary
=SUM($B$2:B2) locks start row for cumulative sum pattern down column C.
Build this without starting from a blank cell
Use a Better Sheets tool for Absolute reference ($), then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Locking the wrong coordinate so copy-down shifts the rate cell reference anyway.
- No locks on VLOOKUP range, so row 50 looks up against a table that slid to empty cells.
- F4 not available on some laptops without Fn key; manual $ typing errors.
- Absolute refs to deleted rows after Settings tab moves without updating formulas.
- Conditional formatting formula with no $ on column letter so rules drift across columns incorrectly.
Frequently asked questions
- $A$1 meaning?
- Column A and row 1 both locked when formula copies in any direction.
- A$1 vs $A1?
- A$1 locks row 1 only. $A1 locks column A only. Pick based on copy direction.
- Absolute vs relative?
- Relative refs shift with copy. Absolute refs stay on the locked parts.
- Named range instead of $?
- Yes. =rate*Amount with named rate cell reads cleaner than $E$1 everywhere.
- Absolute in ARRAYFORMULA?
- Locks still apply per copied logical row inside array logic; test spill output carefully.
- Cross-sheet absolute?
- ='Settings'!$B$1 works. Lock the cell on the other tab the same way.
- Dollar sign in text?
- Different from ref lock. Currency in strings uses quotes, not formula $ syntax.
- Paste only formulas?
- Paste special formulas preserves absolute/relative structure from source cell.
Related Tutorials
Watch how Absolute reference ($) works
Ultimate Checklist in Google Sheets
Google Sheet Basics - The Absolute Basics
Created a GPT function in Sheets
How do I reference a different spreadsheet in Apps Script?
Related blog posts
Guides that explain Absolute reference ($) in more depth.
Absolute Basics of Google Sheets
Learn from scratch: What are rows, columns, and cells in Google Sheets? Master basics, tips & tricks in 3 videos. Get started now!"
Read post →Most Common Errors and How To Handle Them in Google Sheets
Most Common Errors and How To Handle Them in Google Sheets #N/A error #REF! error #NAME? error #DIV/0! Error #VALUE! Error #NUM! error #NULL! error
Read post →The Simplest Bestest Checklist in Google Sheets
Build the ultimately, best, and simplest checklist. Designed well, and FUN to fill out!
Read post →Related glossary terms
Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Open range reference
An open range reference points at an entire row, column, or unbounded slice such as B:B or 2:2 instead of a fixed rectangle like B2:B500. Formulas with open ranges include every cell in that line, which is convenient for growing tables but expensive and risky when stray values live far below your data.
Read guide →VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →ARRAYFORMULA
ARRAYFORMULA tells Google Sheets to treat a formula as an array operation so one expression fills many cells at once. It is common with IF, VLOOKUP, and math on full columns before dynamic arrays made many patterns automatic. It still matters for legacy sheets and explicit control.
Read guide →Done reading about Absolute reference ($)?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.