To keep one cell constant in an Excel formula, add dollar signs to its reference, turning B1 into $B$1. That makes it an absolute reference, so it stays pointed at B1 when you copy or fill the formula to other cells. The quickest way is to click on the reference in the formula and press F4. Below is a worked example, the difference between locking the row or the column, and a named-range alternative.
Why the cell changes when you copy
Excel uses relative references by default. If C2 contains =A2*B1 and you fill it down to C3, Excel adjusts both references by one row, giving =A3*B2. That’s usually helpful, but not when B1 holds a single value everything should use, like a tax rate or exchange rate.
Worked example: apply one tax rate to a list of prices
- Put the tax rate in B1, for example 8%.
- Prices are in A3:A20. In B3, type
=A3*and click cell B1. - Press F4. The reference becomes $B$1, so the formula reads
=A3*$B$1. - Press Enter, then double-click the fill handle at the bottom-right of B3 to copy the formula down.
Every row now uses its own price (A4, A5, and so on) but the same tax rate in B1. See how to use the fill handle in Excel.
The four reference types
Each press of F4 while the cursor is on a reference cycles through:
- $B$1: absolute. Column and row are both locked.
- B$1: mixed. The row is locked; the column changes when copied sideways.
- $B1: mixed. The column is locked; the row changes when copied down.
- B1: relative. Both change.
Mixed references are useful for grids. For a multiplication table with numbers down column A and across row 1, use =$A2*B$1 in B2 and fill it across and down.
F4 on laptops and Macs
- On many laptops, press Fn + F4, because F4 controls a hardware function such as the microphone or display.
- On a Mac, press Cmd + T while editing the formula, or Fn + F4 in some versions.
- You can always type the dollar signs by hand.
Alternative: use a named range
Instead of dollar signs, you can name the constant cell:
- Click B1.
- Click in the Name Box (left of the formula bar), type a name such as TaxRate, and press Enter.
- Write formulas like
=A3*TaxRate.
Named ranges are always absolute and make formulas easier to read.
Keep a cell constant across sheets
References to other sheets work the same way: =A3*Settings!$B$1 stays fixed when copied. For tables, structured references like [@Price]*TaxRate also avoid the problem.
Troubleshooting
The results are all the same, or wrong
You may have locked the wrong reference. Check that only the constant cell has dollar signs and the cells that should change (like A3) don’t.
Pressing F4 repeats my last action
F4 also means “repeat last action” when you’re not editing a formula. Make sure the cursor is inside the formula, on the reference, before pressing it.
#REF! after moving cells
Deleting the constant cell breaks the reference. Moving it with cut and paste updates formulas automatically.
Frequently asked questions
Does this work in Google Sheets?
Yes. Dollar signs work the same way, and F4 toggles them on Windows.
Where is this used most?
Lookups such as VLOOKUP, percentages of a total, currency conversion, and any formula that refers to a single input cell.
Summary
Add dollar signs to the cell reference ($B$1) to keep it constant when copying formulas. Click the reference and press F4 to cycle between absolute, mixed, and relative references, or give the cell a name and use that in your formulas.
Related: How to Reference a Cell from Another Sheet in Excel.
Related: how to subtract one cell from another in Excel.
Related: copy conditional formatting rules in Excel.
Related: VLOOKUP for multiple columns.
Related: duplicate a column in Excel.
Related: enable iterative calculation in Excel.
Related: how to make an absolute reference in Excel on Mac.
Related: How to Create a Multiplication Formula in Excel.
Related: how to create a range in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.