📌 Why Using the $ Sign in Google Sheets & Excel Will Save You Hours
- marchbornscompany
- Aug 30, 2025
- 2 min read
Introduction
If you’ve ever copied a formula in Google Sheets or Excel and the results came out wrong, you’ve already discovered the importance of cell references. By default, formulas adjust when you copy them to a new row or column.
But what if you want a formula to always point to the same cell? That’s where the magic of the $ sign (absolute referencing) comes in.
In this guide, you’ll learn:
What the $ sign means in a formula.
The difference between relative, absolute, and mixed references.
Practical examples where $ can save you time and errors.
🔑 1. Understanding Cell References
When you enter a formula like:
=A1*B1
and drag it down, Google Sheets will adjust the references (A2B2, A3B3, etc.). This is called a relative reference.
But sometimes, you don’t want things to shift. That’s where $ comes in.
🔒 2. Types of References
Reference Type | Example | What It Means |
Relative | A1 | Changes when copied |
Absolute | $A$1 | Always points to A1 (row + column fixed) |
Mixed (Row) | A$1 | Column changes, row fixed |
Mixed (Column) | $A1 | Row changes, column fixed |
📊 3. Practical Examples
✅ Example 1: Fixing a Tax Rate
Suppose you’re calculating tax on sales:
Item | Price | Tax Rate | Total w/Tax |
Apple | 100 | 18% | =B2*(1+$C$1) → 118 |
Orange | 200 | 18% | =B3*(1+$C$1) → 236 |
👉 Without the $C$1, dragging the formula down would shift to C2, C3, etc. With $C$1, the formula always looks at the tax rate in C1.
✅ Example 2: Multiplication Table
Formula in B2:
=$A2*B$1
1 | 2 | 3 | |
1 | 1 | 2 | 3 |
2 | 2 | 4 | 6 |
3 | 3 | 6 | 9 |
👉 $A2 fixes the column (A), but lets the row change.👉 B$1 fixes the row (1), but lets the column change.
Perfect for generating a clean multiplication table!
✅ Example 3: Currency Conversion
You have a constant exchange rate in one cell (say D1 = 82).
Formula:
=B2*$D$1
Now every price in column B gets multiplied by the fixed exchange rate, no matter where you copy the formula.
🧠 4. When to Use Each Type
Absolute ($A$1): Use when a single value must remain constant (tax rate, interest rate, exchange rate).
Mixed ($A1 or A$1): Use for structured calculations like tables where one part of the reference must stay fixed.
Relative (A1): Use when you want formulas to adjust normally while dragging.
⚡ 5. Pro Tip: Shortcut to Add $
Instead of typing $, use:
F4 (Windows/Excel) → cycles through reference types ($A$1 → A$1 → $A1 → A1).
Cmd + T (Mac/Excel) works similarly.
In Google Sheets, you need to type the $ manually, but once you practice, it becomes second nature.
Conclusion
The $ sign in Google Sheets and Excel might look small, but it’s one of the most powerful tools in your spreadsheet toolbox. By controlling how cell references behave, you’ll avoid mistakes, save hours, and make your formulas bulletproof.
Comments