Absolute Cell Reference in Excel & Google Sheets: How to Lock Cells So Formulas Don’t Break

Stop your Excel formulas from breaking when you copy them. Learn how to use absolute cell references with $ signs + the F4 shortcut, with real examples for tax, commission, and price lists.

Why Your Formulas Break When You Copy Them

I learned this the hard way. I had a price sheet with 200 products. Each one needed 8% tax added. I put the tax rate in cell E1 and wrote =B2*E1 to calculate tax for the first product.

Then I dragged the formula down.

Product 2 showed $0. Product 3 showed $0. All 199 rows were wrong.

The problem? Excel was doing exactly what I told it to. When I copied the formula down, E1 became E2, then E3. And cells E2 and E3 were empty.

The fix took 2 seconds: add two dollar signs. =B2*$E$1

That’s an absolute cell reference. It tells Excel “no matter where you copy me, always look at cell E1.”

If you use spreadsheets for anything beyond a grocery list, this is the one skill that stops 90% of your formula errors.

Relative vs Absolute: What Excel Does by Default

By default, Excel uses relative references. That’s actually smart.

Say you have quantity in column A and price in column B. In C2 you write =A2*B2 to get the total. When you drag that down, you want A2 to become A3, A4, A5. Excel shifts the reference for you. That’s relative referencing and it works great for row-by-row calculations.

The trouble starts when your formula needs to grab a number from one specific cell every time. Like a tax rate, commission %, currency exchange rate, or discount code.

If that number lives in D1, and you write =A2*D1 then drag down, here’s what happens:

Excel shifted D1 to D2, D3, D4 because you didn’t tell it not to.

How to Lock a Cell with the Dollar Sign

To lock a cell so it never changes, put a $ before the column letter AND the row number.

=A2*$D$1

Now when you copy that formula down, A2 will change to A3, A4, A5… but $D$1 stays $D$1 every time.

Real example: Commission calculator
You’ve got sales rep names in A, sales amounts in B, and commission rate of 12% in cell E1.

In C2, type: =B2*$E$1

Drag it down. Every rep’s commission now correctly uses the 12% from E1. If the commission changes next quarter, you only update E1 once and the whole sheet recalculates. That’s why people call this a “scalable” formula.

The F4 Shortcut: Stop Typing Dollar Signs

Don’t type $ manually. It’s slow and you’ll mess it up.

1. Click into your formula bar and put your cursor on the cell reference, like D1
2. Press F4 on your keyboard

Each time you press F4, Excel cycles through 4 options:

1. D1 → $D$1 — Both column and row locked. This is absolute. Use this 90% of the time.
2. $D$1 → D$1 — Only the row is locked. The column can still change.
3. D$1 → $D1 — Only the column is locked. The row can still change.
4. $D1 → D1 — Back to relative. Nothing locked.

On a Mac: Use Cmd + T in Google Sheets, or Fn + F4 in Excel.

On Google Sheets: F4 works too, but sometimes you need to use Fn + F4 depending on your keyboard.

When to Use Mixed References: A$1 vs $A1

You need “mixed” references when you’re building a grid or table where one thing stays fixed horizontally, and another stays fixed vertically.

Best example: Multiplication table

Put numbers 1-10 across row 1: B1 to K1
Put numbers 1-10 down column A: A2 to A11

In cell B2, you want to multiply the column header by the row header. Formula: =B$1*$A2

• B$1 — The $1 locks row 1, so when you drag down, it always grabs the numbers from row 1. But the B has no $, so it shifts to C, D, E as you drag right.
• $A2 — The $A locks column A, so when you drag right, it always grabs from column A. But the 2 has no $, so it shifts to 3, 4, 5 as you drag down.

Now drag that one formula across the whole 10×10 grid. You just built a full multiplication table with one formula. That’s the power of mixed references.

3 Common Mistakes That Still Break Your Sheet

1. Forgetting to lock the reference at all — This is 99% of errors. If a formula gives weird results, check if you missed the $.
2. Locking the wrong part — Using A$1 when you meant $A$1. Use F4 and watch what gets highlighted.
3. Using Cut-Paste instead of Copy-Paste — If you cut D1 and paste it to F5, Excel is smart and will update $D$1 in your formulas to $F$5. If you want it to stay D1, use Copy-Paste, not Cut-Paste.

Pro tip to debug: In Excel, click the cell with the bad formula. Go to the Formulas tab → Trace Precedents. Blue arrows will show you which cells it’s pulling from. If an arrow points to an empty cell, you forgot your $.

Better Than Dollar Signs: Use Named Ranges

Once you’re comfortable with $D$1, level up to named ranges.

Instead of remembering that tax is in D1, click D1, then type TaxRate in the name box left of the formula bar. Press Enter.

Now your formula becomes: =B2*TaxRate

Named ranges are automatically absolute. Plus, =B2*TaxRate is way easier to read 6 months from now than =B2*$D$1.

In Google Sheets: Data → Named ranges → Add a range.

Quick Cheat Sheet

Key Takeaway

If you only remember one thing: put $ signs around any cell that holds a constant — tax rates, prices, percentages — before you copy a formula.

It takes 1 second with the F4 key and saves you 20 minutes of debugging later.

Once this clicks, your spreadsheets stop being fragile and start being reliable tools you can trust.

Try this now: Open a blank sheet. Put 0.08 in A1. In B1 put 100. In C1 type =B1*$A$1. Now copy C1 down to C5. Change A1 to 0.10. Watch every row update. That’s the whole trick.