Excel Formula: Keep a Cell Fixed When You Copy It

To keep a cell reference fixed when copying an Excel formula, add dollar signs to it: with a value in A2 and a rate in B1, enter =A2*$B$1. The $B$1 reference continues to point to B1 when you fill the formula elsewhere, while the relative reference A2 adjusts to the new row.

What keeping a cell fixed means

Excel normally adjusts cell references when you copy or fill a formula. This behavior is useful when a calculation should use the corresponding value on each row, but it can cause errors when one input must remain the same throughout a range. A fixed, or absolute, reference tells Excel to keep the referenced row and column unchanged as the formula moves.

For example, a worksheet might list prices in column A and store one tax rate in B1. Each row’s calculation needs a different price but the same rate. Making B1 absolute keeps the rate reference steady as you copy the calculation down. The formula still recalculates when the value in B1 changes; the dollar signs fix the address, not the value.

This is not the same as locking a worksheet cell so people cannot edit it. Absolute references control how formulas adjust when copied. Worksheet protection controls whether users can change cells. These features solve different problems.

How dollar signs work in Excel references

A normal reference such as A1 is relative: both its column and row can adjust. Put a dollar sign before a part of the address to hold that part steady. Excel supports three useful forms:

$A$1 fixes both column A and row 1. This is an absolute reference.
A$1 fixes row 1 but lets the column change. This is a mixed reference.
$A1 fixes column A but lets the row change. This is also a mixed reference.

The dollar signs do not change the value in the referenced cell. They only affect how Excel rewrites the address when you copy the formula. If a reference should move in both directions, leave it without dollar signs. If neither part should move, use both.

Example: keep one input fixed while filling down

Suppose A2:A5 contains prices and B1 contains a tax rate of 8%. You want column B to calculate the tax amount for each price. With prices of 100, 50, 80, and 25 in A2:A5 and the rate in B1, enter this in B2:

With prices in A2:A5 and the tax rate in B1, enter =A2*$B$1 in B2. For A2 equal to 100 and B1 equal to 8%, the result is 8.

Copy B2 down through B5. Excel adjusts the price reference to A3, A4, and A5, but leaves the rate reference as $B$1. The resulting tax amounts are 8, 4, 6.4, and 2. If you later change B1 to 9%, the amounts update using the new rate. This is usually the most useful pattern when one assumption is shared by many calculations.

To fill the formula down, select B2 and drag its fill handle—the small square at the lower-right corner of the selection—down to B5. You can also copy B2, select B3:B5, and paste. The same reference rules apply with either method.

When to use a mixed reference

Sometimes only the row or only the column should stay fixed. Mixed references are especially useful in a two-way calculation table, where formulas are copied both across and down. Consider a grid that multiplies each product amount by a different rate across the top. The amount is in column A, while the rates are in row 1.

With amounts in A2:A10 and rates in B1:E1, enter =$A2*B$1 in B2. If A2 is 200 and B1 is 10%, the result is 20. Copy the formula across and down: $A2 keeps the amount column fixed while allowing the row to change; B$1 keeps the rate row fixed while allowing the column to change. The formula can therefore calculate each amount-rate combination without editing every cell.

Use $A1 when a formula should always look in column A but should move to the relevant row. Use A$1 when it should always use row 1 but should move across columns. If a formula is only being copied down a single column, a full absolute reference is often simpler for the shared input.

How to add or remove the dollar signs

You can type the dollar signs directly while entering a formula. For example, click the formula bar or edit the cell, place the insertion point in the reference, and change B1 to $B$1. You can also select the cell reference within the formula and cycle through its reference styles with a keyboard shortcut.

Start entering or editing the formula in the cell.
Move the cursor to the reference you want to change, or select that reference in the formula.
On Windows, press F4 to cycle through relative, absolute, and mixed reference forms.
Confirm the formula, then copy or fill it to the intended cells.

For example, selecting B1 in a formula and pressing F4 cycles through $B$1, B$1, $B1, and B1. The order can be useful when you know which part should remain fixed. On Excel for Mac, Command+T cycles reference styles while editing a formula; some keyboard configurations may require the Fn key to access function-key behavior. Excel for the web uses the same dollar-sign reference syntax, though shortcut behavior can depend on the browser and keyboard settings.

Common copying mistakes and how to fix them

A frequent mistake is fixing the wrong part of the reference. If you use $B1 when both the row and column must stay at B1, copying down changes the row. If you use B$1 and copy down, row 1 stays fixed, but copying across changes the column. Check both parts of the address against the direction you intend to fill.

Another common issue is placing dollar signs around the wrong cell reference. In the tax example, the rate is the shared input, so the rate cell is absolute and the price cell stays relative. If you make the price absolute instead, every row may calculate using the first price. A quick check is to copy the formula one cell in the intended direction and inspect the resulting references.

Also remember that an absolute reference does not prevent a formula from changing its result. If the fixed cell’s contents change, formulas pointing to it recalculate. And if you move or delete cells, Excel may update references to reflect those changes; dollar signs are not a way to preserve a cell’s original contents or location under every worksheet edit.

If a copied formula shows the same result on every row, check whether the changing input was accidentally made absolute. If the formula returns an unexpected value, click the cell and inspect the formula bar, or use Formulas > Trace Precedents to see which cells feed the calculation. The ribbon path is available in desktop Excel for Microsoft 365 on Windows.

Fixed references versus protected cells

If your goal is to stop someone from overwriting a rate or other input, dollar signs are not enough. By default, worksheet cells are marked as locked, but that setting only takes effect after you protect the sheet. To prevent edits to selected inputs, you can leave the cells you want users to edit unlocked, then use Review > Protect Sheet. Protection options let you choose what users can do while the sheet is protected.

Protecting a sheet does not turn relative references into absolute ones, and absolute references do not protect a worksheet from edits. You might use both: an absolute reference so every calculation points to the intended assumption, and sheet protection to reduce accidental changes to that assumption. Keep an editable copy of important workbooks before changing protection settings, particularly if multiple people rely on the file.

Frequently Asked Questions

What symbol keeps a cell fixed in an Excel formula?
Use a dollar sign before the column letter and row number, as in $A$1. This fixes both parts of the reference when you copy or fill the formula.

How do I keep a cell fixed when dragging a formula down?
Make the reference absolute by adding dollar signs to both the column and row, then drag the fill handle down. For example, use $B$1 for a value that every row should reference.

What is the difference between $A$1 and A$1?
$A$1 fixes both column A and row 1. A$1 fixes row 1 only, so the column can change when the formula is copied sideways.

Does F4 work to lock a cell reference?
In desktop Excel for Windows, F4 cycles the selected reference through absolute and mixed forms while you edit a formula. On a Mac, Command+T is the usual shortcut. You can always type the dollar signs yourself.

Does an absolute reference stop the cell value from changing?
No. It keeps the formula pointing to the same address when copied. If the contents of that cell change, the formula’s result can change too; use worksheet protection if you need to limit edits.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.