OmniDocLock Cell References in Excel Formulas With Dollar Signs
Learn how dollar signs lock cell references in Excel formulas so they keep pointing at the same cell when copied, with examples and a quick check.
If you have copied a formula down a column and watched it land on the wrong cell, you have run into the problem this article solves. Spreadsheet programs adjust cell references by default. Copy a formula one row down and every reference moves one row down with it. That works well for row-by-row math, but it breaks when a formula must keep pointing at one fixed cell, such as a tax rate, a unit price or an exchange rate.
People searching for "excel formula freeze cell reference" usually want that fixed cell to stay put when the formula is copied. Excel does not have a freeze command for this. The fix is a dollar sign in the reference, which turns it into an absolute or mixed reference. This article explains what the dollar signs do, the three reference types, and how to check your results.
You will leave with a clear rule for choosing which parts of a reference to lock, a short practice routine, and answers to common mix-ups, including the difference between locking references and freezing rows on screen.
How do you freeze a cell reference in an Excel formula? The short answer
Put a dollar sign in front of the column letter and the row number of the cell you want to keep fixed, such as $B$2, so the reference does not change when the formula is copied.
| Your situation | What to do | Why |
|---|---|---|
| One cell, such as a tax rate, feeds every row | Write the reference as $B$2 | Both the column and row are locked, so every copy points at B2 |
| You copy across columns but move down rows | Write the reference as $B2 | The column is locked, and the row can still change as you copy down |
| You copy down a table but move across columns | Write the reference as B$2 | The row is locked, and the column can still change as you copy across |
| You need a formula to stop changing its result | Paste the result as values | Dollar signs lock where the formula points, not the number it returns |
| Each copy should shift to the next cell | Keep the plain reference, such as B2 | Relative references adjust automatically with each copy |
What do the dollar signs actually do?
Every cell reference has two parts: a column letter and a row number. A dollar sign placed before either part tells the spreadsheet not to change that part when the formula is copied. Without a dollar sign, the part adjusts based on where the formula lands.
Relative references are the default
A plain reference like B2 is relative. If you write =B22 in cell C2 and copy it to C3, the copy becomes =B32. The reference moved down one row because the formula moved down one row. This is what you want when each row uses its own input.
Absolute and mixed references
An absolute reference, such as $B$2, locks both parts. Copying it anywhere still points at B2. A mixed reference locks only one part:
$B2locks the column. Copy it across and it still uses column B, but copy it down and the row number changes.B$2locks the row. Copy it down and it still uses row 2, but copy it across and the column letter changes.
Mixed references are useful in grids, such as a multiplication table, where one header row and one header column each need to stay fixed in one direction.
How does copying change each type?
Suppose column A holds 12 prices in A2 through A13, and the tax rate sits in cell B1. In cell C2, you type:
=A2*$B$1
Copy that formula down to C13. Each row now reads its own price and the same tax rate:
- C3 becomes
=A3*$B$1 - C4 becomes
=A4*$B$1 - and so on, down to C13 becomes
=A13*$B$1
Now try the same formula without dollar signs, =A2B1. Copied to C3, it becomes =A3B2. Cell B2 is empty in this layout, so the result is zero, and the error gets worse further down. The dollar signs prevent this.
Common mix-ups
- Dollar signs do not freeze results. A locked reference still reads the cell's current value. If the rate in B1 changes, every formula in column C updates.
- Freeze panes is a different feature. Freezing rows or columns keeps headers visible while you scroll. It does not change any formula.
- Typing one dollar sign is not enough. A reference like
$B2locks only the column. If you need both parts fixed, add a dollar sign before the column and the row.
📊 At a glance
| Reference | Example | Column when copied across | Row when copied down |
|---|---|---|---|
| Relative | B2 | Changes | Changes |
| Absolute | $B$2 | Stays fixed | Stays fixed |
| Mixed, column locked | $B2 | Stays fixed | Changes |
| Mixed, row locked | B$2 | Changes | Stays fixed |
Putting it to use
- Decide which cell should stay fixed. In the tax example, that is the rate in B1.
- In the first formula cell, type the formula and select the cell you want to lock, or type its reference by hand.
- Add the dollar signs. You can type them directly before the column letter and row number, or place the cursor inside the reference and press F4. In many spreadsheet programs on Windows, each press of F4 cycles through the four reference types. Check your program's shortcut if it behaves differently.
- Press Enter, then copy the formula down or across to the rest of the range.
- Click three or four copied cells, including one near the end of the range. Confirm each formula points at the expected input cells.
- Spot-check one result by calculating it by hand. If the numbers are important, verify them yourself before you share the file.
🎯 Which approach fits you?
- One input for the whole sheet: Use a fully locked reference, such as
$B$1. This suits rates, fixed costs and settings. - Lookups across a table: Lock the column or row that must stay constant and leave the other part relative. This keeps the formula in step with each row or column.
- Data that lives in a PDF: If your numbers come from a PDF table, the PDF editor in the OmniDoc desktop client can export the content to Excel format. You can then add your formulas in your spreadsheet program. Download the client at download.omnidoc.top.
❓ FAQ
1. Does a dollar sign freeze the value in a cell?
No. It locks the reference, so the formula still reads whatever the referenced cell contains. If you need a fixed result, copy the formula cells and paste them as values.
2. What is the difference between freeze panes and absolute references?
Freeze panes keeps rows or columns visible on screen while you scroll, and it does not change any formula. Absolute references change how a formula is written so it keeps pointing at one cell.
3. Why do my copied formulas show zeros or wrong numbers?
Usually a reference that should be fixed moved during the copy. Click a copied cell, check which references shifted, and add dollar signs where needed.
4. How do I lock a whole range, such as A1 through A10?
Put dollar signs on both ends of the range, like $A$1:$A$10. The range stays the same when you copy the formula to other cells.
📝 Summary
An excel formula freeze cell reference is handled with dollar signs rather than a freeze command. Use $B$2 to lock a cell completely, or $B2 and B$2 to lock only one direction. Copy a formula, then check a few results to confirm it points where you expect.
