How to Set an Absolute Cell Reference in Excel Formulas

To set an absolute cell reference in Excel, add a dollar sign before both the column letter and row number, as in $B$1. While editing a formula on Windows, select the cell reference and press F4 to cycle through absolute and mixed reference styles. This keeps the chosen row, column, or whole cell fixed when you copy the formula. You will need a workbook with a formula or a cell where you can enter one; the steps below use Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

Excel for Microsoft 365 on Windows, or another recent desktop version of Excel.
A workbook with the values and cells you want to use in a formula. Save a copy first if you are making changes to an important workbook.
A keyboard with an F4 key. On some laptops, you may need to hold Fn while pressing F4.

Step-by-Step

Identify the cell that must stay fixed. An absolute reference is useful when a formula should keep pointing to the same cell as you fill or copy it elsewhere. Common examples include a tax rate, exchange rate, commission percentage, or a fixed input used throughout a worksheet. Check that the value is in the intended cell before building the formula. If the value is in B1, the absolute reference for it is $B$1: the first dollar sign locks column B, and the second locks row 1.
Start or open a formula. Click the cell where you want the result, type an equal sign, and enter the formula you need. For example, with pre-tax amounts in A2:A10 and a tax rate of 8% in B1, enter this formula in C2: =A2*$B$1. If A2 contains 100 and B1 contains 8%, the result in C2 is 8. The amount reference A2 is relative, so it can adjust for each row; the tax-rate reference $B$1 is absolute, so it stays fixed.
Select the reference inside the formula. Before confirming the formula, place the insertion point within the B1 reference. You can click between the characters in the formula bar, or double-click the formula cell and position the cursor within B1 in the cell. You do not need to highlight the whole formula. The important part is that Excel can tell which cell reference you want to change. If you have already entered the formula, select the cell and press F2 to edit it, then position the cursor within the reference.
Toggle the reference style. Press F4 on a Windows keyboard. If the cursor is within B1, Excel changes the reference to $B$1, locking both the column and row. Press F4 again to cycle through the other styles: B$1 locks only the row, $B1 locks only the column, and B1 is fully relative. Stop when the style you need appears. If F4 changes your laptop’s brightness or another system setting, try Fn+F4; keyboard behavior depends on the device’s function-key settings.
Confirm the formula. Press Enter to finish editing. In the example, C2 should display 8 if A2 is 100 and B1 is 8%. If Excel displays the formula text instead of a result, check that the cell is not formatted as Text and that the formula begins with an equal sign. You can also click the cell and look at the formula bar to verify that the reference includes both dollar signs.
Copy the formula to other rows. Select C2 and drag the fill handle, the small square at the lower-right corner of the selection, down through the rows that need a result. You can also copy C2, select the destination cells, and paste. The amount reference will move with each row, while $B$1 remains unchanged. For instance, the next row will use A3 for its amount but still use the tax rate in B1. Check a copied cell in the formula bar to make sure the relative part moved and the absolute part did not.
Use the dollar signs directly if needed. You can type $B$1 into a formula instead of using F4. This is often the quickest method for a short formula or when a laptop shortcut is inconvenient. To add a dollar sign without changing other references, edit the formula and place it immediately before the column letter or row number you want to lock. Then confirm the result and copy the formula as usual.

On Mac: The dollar-sign notation works the same way in Excel for Mac. While editing a formula, select the reference and use Command+T to cycle reference styles. Depending on the Mac keyboard and its function-key settings, F4 may also be available through the Fn key. If the shortcut does not cycle the reference, type the dollar signs into the reference yourself. In Excel for the web, editing and reference notation work similarly, but the browser or keyboard can intercept a shortcut; manual dollar signs are a reliable alternative.

Common Mistakes

Locking the wrong part of a reference. $B$1 fixes both the column and row. B$1 fixes only the row, while $B1 fixes only the column. A reference without dollar signs, B1, can change in both directions when copied. Choose the form based on how the formula will be filled, rather than adding dollar signs automatically to every reference.
Pressing F4 when the cursor is not on a reference. F4 works on the reference being edited. If the cursor is after the reference, or you have already confirmed the formula and are not in edit mode, the shortcut may repeat an action or do something unrelated. Edit the formula and position the cursor within the cell address before pressing the key.
Locking the formula cell instead of the input cell. In the tax example, B1 is the fixed tax-rate input, while A2 should change as the formula moves down. If you accidentally make A2 absolute, every copied row may calculate tax on the same amount. Review the formula structure before filling it.
Assuming an absolute reference protects the value. The dollar signs lock the address, not the contents of the cell. If someone changes the value in B1, formulas referring to $B$1 use the new value. Absolute references do not prevent edits, deletion, or changes to a worksheet’s layout.
Adding dollar signs to a range incorrectly. To lock a range, both its start and end references need the appropriate markers. For example, $B$2:$B$10 fixes both endpoints. Locking only one endpoint can cause the other to shift when copied, which may produce a different range than intended.

Tips

Use the mixed styles deliberately. A row-only reference such as B$1 is useful when copying a formula down rows while allowing the column to change across a table. A column-only reference such as $B1 is useful when copying across columns while allowing the row to change. These are called mixed references because one part moves and the other stays fixed.
Think about the direction of copying. If a formula will be copied down, ask whether its row numbers should change. If it will be copied across, ask whether its column letters should change. This quick check helps you choose between $B$1, B$1, $B1, and B1 before filling a large area.
Use F4 as a cycle, not a one-time lock button. The first press may make the reference absolute, but later presses change it to a mixed style and eventually back to relative. Look at the reference after each press and stop at the exact pattern you want.
Consider named ranges for frequently reused inputs. A name such as TaxRate can make formulas easier to read, especially in a larger workbook. Create and use names only if they make the sheet clearer, and check the Name Manager if a name points to the wrong cell. A named reference can be copied without the same row-and-column adjustments as an ordinary relative address.
Keep important inputs easy to find. Put shared rates or assumptions in a clearly labeled area and consider applying a distinctive fill color. This does not change how absolute references work, but it makes it easier to verify that formulas point to the intended input.

Troubleshooting

F4 does not insert dollar signs: First make sure you are editing a formula and that the cursor is inside the reference. On a laptop, try Fn+F4 or adjust the device’s function-key behavior. If the keyboard still does not pass the shortcut to Excel, add the dollar signs by typing them directly. On Mac, try Command+T or enter the symbols manually.

The copied formula still changes its reference: Inspect the formula in one of the destination cells. If it shows a reference such as B1 without dollar signs, it is still relative. Edit the formula, lock the correct part, confirm it, and fill the range again. If only the row or column is drifting, use the appropriate mixed reference rather than locking both parts by default.

Every copied row shows the same result: Check whether the value that should vary, such as the amount in column A, was accidentally made absolute. In the example, the amount reference should remain relative so it adjusts from A2 to A3 and onward. Also check whether the cells in the amount column contain different numeric values and whether the formula was filled into the intended rows.

The result looks wrong even though the reference is locked: Verify the input value, the operation in the formula, and the cell’s number format. For example, a displayed percentage or rounded number can look different from the underlying value. Select the result cell and inspect the formula bar; then select the fixed input cell and confirm that its value is correct. Remember that locking an address does not freeze the value stored there.

The formula appears as text or Excel reports an error: Confirm that the formula starts with an equal sign, the reference is spelled correctly, and the cell is not set to Text format. If it is Text, change the cell format to General, edit the formula, and press Enter again. Also check for a missing operator or an accidental space within the cell address.

Frequently Asked Questions

What does an absolute cell reference do?
An absolute reference keeps the selected row and column fixed when a formula is copied or filled. The dollar signs in $B$1 lock both parts of the address; they do not lock the cell’s value against later edits.

What is the shortcut to make a cell reference absolute in Excel?
In desktop Excel for Windows, edit the formula, place the cursor within the reference, and press F4. On Mac, use Command+T if available. If a shortcut is unavailable or intercepted, type the dollar signs directly.

How do I lock only a row or only a column?
Put the dollar sign before the part you want to lock. B$1 locks row 1 but allows the column to change; $B1 locks column B but allows the row to change. These mixed references are useful when copying formulas across a grid.

Can I make an entire range absolute?
Yes. Add dollar signs to both endpoints, as in $B$2:$B$10. This keeps the beginning and end of the range fixed when a formula containing that range is copied.

Does an absolute reference work on another worksheet?
Yes. Keep the dollar signs on the cell address and include the sheet name. If the sheet name contains spaces, Excel uses single quotation marks around the sheet name. The dollar signs still lock the referenced row and column, not the worksheet’s contents.

Why does F4 change my computer’s settings instead?
Some keyboards assign a hardware action to F4 unless you hold Fn. Try Fn+F4, check the keyboard’s function-key setting, or edit the reference manually. The dollar-sign notation behaves the same regardless of which keyboard shortcut you use.

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.