How to Anchor in Excel
To anchor a cell in Excel, put a dollar sign before the column letter and row number in the reference — $A$1 — so it stays fixed when you copy the formula or drag the fill handle. The fastest way to add those dollar signs is to click the reference in the formula bar and press F4 on Windows (or Command+T on a Mac), which cycles the reference through its absolute, mixed, and relative forms.
“Anchoring” almost always means locking a cell reference so a formula keeps pointing at the same cell. But people also use the word for two unrelated tasks: pinning a picture or chart to a cell so it moves with the data, and keeping header rows visible while you scroll. This guide covers the main meaning in depth, then disambiguates the other two so you land in the right place.
Why you anchor a reference
When you type a formula, Excel stores cell references relative to the formula’s position. Enter =A1+B1 in C1, copy it down to C2, and Excel rewrites it as =A2+B2 — each reference shifts to match the new row. That automatic adjustment is what makes a formula like =A2*0.07 work when you fill it down a whole column.
The problem starts when one input shouldn’t move. Suppose your tax rate sits in E1 and you want every row of column C to multiply its price by that single rate:
| Cell | Formula you type | What you want |
|---|---|---|
| C2 | =B2*E1 | B2 times the rate in E1 |
| C3 | (copied down) | B3 times the rate in E1 |
| C4 | (copied down) | B4 times the rate in E1 |
If you copy =B2*E1 down, Excel turns it into =B3*E2, then =B4*E3 — the rate reference slides off E1 into empty cells and your math breaks. Anchor it as =B2*$E$1 and the $E$1 part stays put no matter how far you copy. B2 still moves to B3, B4 and so on, which is exactly what you want. This is the single most common reason a copied formula returns wrong numbers, and anchoring is the fix.
How to anchor a cell with the dollar sign
A dollar sign locks whatever comes immediately after it. There are two parts to a reference — the column letter and the row number — so you can lock one, the other, or both.
- Double-click the cell to edit the formula, or click once and edit in the formula bar.
- Click inside the reference you want to anchor (e.g., click on the
E1in=B2*E1). - Type a dollar sign before the column letter, before the row number, or before both:
$E1,E$1, or$E$1. - Press Enter, then copy the formula or drag it with the fill handle.
You can also type the dollar signs as you build the formula in the first place — there’s nothing special about editing an existing one.
How to anchor with the F4 key
Adding dollar signs by hand is fiddly. The F4 key does it for you and cycles through every reference type in turn.
- Edit the cell so the formula is active.
- Put the cursor in (or just to the right of) the reference you want to change — for example, click on
E1. - Press F4 repeatedly. Each press steps to the next form:
- First press:
$E$1— fully absolute (column and row locked). - Second press:
E$1— row locked, column free (mixed). - Third press:
$E1— column locked, row free (mixed). - Fourth press:
E1— back to fully relative.
- First press:
- Stop on the form you want and press Enter.
If you select an entire reference rather than just clicking inside it, F4 applies the change to that whole reference. Highlight several references at once and F4 will toggle all of them together.
On a Mac, F4 is different
This is where a lot of guides get it wrong. On a Mac, F4 is often claimed by macOS or by the keyboard’s media controls (brightness, Mission Control, etc.), so pressing it in Excel may do nothing or trigger a system action. The reliable Mac shortcut for cycling reference types is Command+T. If you’d rather use the function key, press fn+F4 so the keyboard sends a true F4 instead of its media function. Either works — Command+T is the one Microsoft documents for Excel on Mac.
| Action | Windows | Mac |
|---|---|---|
| Cycle reference types | F4 | Command+T (or fn+F4) |
The four reference types at a glance
Every reference is one of four combinations. Reading them is simple once you know the rule: a dollar sign freezes whatever follows it.
| Reference | Column | Row | Behavior when copied |
|---|---|---|---|
A1 | free | free | Both shift relative to the new position (a plain relative reference). |
$A$1 | locked | locked | Neither changes — a true absolute reference. |
A$1 | free | locked | Column moves across columns; row stays on row 1. Use when filling sideways. |
$A1 | locked | free | Column stays on A; row moves down. Use when filling down. |
The two middle rows are mixed references — exactly one coordinate is anchored. They shine in two-way grids like multiplication tables or commission matrices, where a formula such as =$A2*B$1 reads the row label from a locked column A and the column header from a locked row 1, so a single formula fills an entire table correctly.
Named ranges: anchoring without dollar signs
If juggling dollar signs feels error-prone, a named range gives you the same fixed behavior with a friendly label. A name like TaxRate always points to the same cell, so =B2*TaxRate behaves just like =B2*$E$1 — copy it anywhere and it keeps referring to the right cell.
To create one, select the cell, type a name in the Name Box to the left of the formula bar (or use Formulas → Define Name), and press Enter. Names are absolute by default, which makes formulas far easier to read and audit — =Price*TaxRate tells you what the math means, while =B2*$E$1 makes you go hunting. The trade-off is setup time, so reserve named ranges for constants and lookup tables you reference repeatedly.
”Anchoring” an object or image to a cell
The second meaning of the word: keeping a picture, shape, or chart attached to the cells beneath it so it behaves sensibly when you insert rows or resize columns. This has nothing to do with formulas.
After you insert a picture or chart, right-click it and choose Format Picture (or Format Object / Size and Properties), then open the Properties section. You’ll see three object-positioning options:
- Move and size with cells — the object grows, shrinks, and shifts as the cells under it change. Choose this to keep an image glued to a specific data region.
- Move but don’t size with cells — the object slides when rows or columns are inserted but keeps its own dimensions. This is the default and usually what you want for logos.
- Don’t move or size with cells — the object floats in a fixed spot on the sheet regardless of what happens to the grid.
Pick the behavior that matches whether the object should travel with the data or stay anchored to the page.
”Anchoring” headers with Freeze Panes
The third meaning: keeping a header row or label column locked in view while the rest of the sheet scrolls. That is Freeze Panes, not cell anchoring. Go to View → Freeze Panes and choose to freeze the top row or first column, or select a cell and freeze everything above and to the left of it. When you’re done, the same menu lets you unfreeze the panes. Freezing only changes what stays on screen — it never touches your formulas or references.
A related point of confusion: anchoring a reference is not the same as protecting a cell. Anchoring stops a reference from shifting; it does nothing to stop someone editing the cell. To prevent edits you lock cells and protect the worksheet, which is a separate feature entirely.
Troubleshooting
F4 does nothing on my Mac. macOS or your keyboard’s media keys are intercepting it. Use Command+T instead, or press fn+F4 to send a real F4.
F4 changes the whole formula, not one reference. Click inside (or select) just the reference you want before pressing the key. With nothing specific selected, Excel applies the toggle to the reference nearest the cursor.
My copied formula still shifts even though I added dollar signs. Check that you anchored the right part. $A1 keeps the column fixed but lets the row move; if your fill direction is wrong for that pattern, the reference still drifts. Match the lock to the direction: $A1 for filling down a column, A$1 for filling across a row, $A$1 to lock both.
The reference jumps to a #REF! error after I copy. A relative reference ran off the edge of the sheet (e.g., copied up past row 1). Anchor the input that should stay put with $ so it can’t slide out of range.
I anchored a cell but the value still changes. Anchoring fixes the reference, not the value. If the data in the anchored cell changes, every formula pointing at it updates too — that’s the intended behavior. To freeze a value, copy the cell and use Paste Special → Values.
Frequently Asked Questions
How do I anchor a cell in Excel?
Add a dollar sign before the column letter and the row number in the reference — $A$1 — directly in the formula. The quickest method is to click the reference in the formula bar and press F4 on Windows or Command+T on a Mac, which inserts the dollar signs for you and cycles through the reference types.
How do I anchor a formula so it doesn’t change when I copy it?
Anchor the references inside the formula that should stay fixed. A reference such as $E$1 won’t shift when you copy or fill, while a plain reference like B2 will move with each new row or column. Anchor only the parts that must stay constant — typically a tax rate, total, or lookup cell — and leave the rest relative so the formula still adjusts correctly.
What does it mean to lock a cell reference?
Locking a cell reference means fixing it so Excel doesn’t auto-adjust it when you copy the formula elsewhere. You lock a reference by placing dollar signs in front of its column and/or row, turning it into an absolute or mixed reference. Note this is different from locking a cell to prevent editing, which uses worksheet protection.
What is the difference between $A$1, A$1, and $A1?
$A$1 is fully absolute — both the column and the row are locked. A$1 locks only the row (column A can shift), and $A1 locks only the column (the row can shift). The two mixed forms are useful when you fill a formula in one direction and need just one coordinate to hold steady.
What is the F4 key shortcut for on a Mac?
On a Mac, F4 is frequently captured by macOS or the keyboard’s media controls, so it may not cycle references the way it does on Windows. Use Command+T in Excel for Mac to toggle between absolute, mixed, and relative references, or press fn+F4 to send a literal F4 keystroke.
Can I anchor a cell without using dollar signs?
Yes. Create a named range — give a cell a name like TaxRate through the Name Box or Formulas → Define Name — and use that name in your formulas. Named ranges are absolute by default, so =B2*TaxRate stays anchored to the same cell wherever you copy it, with no dollar signs and far more readable formulas.
