Excel Absolute References on Windows: How to Use Them

To make an Excel reference stay on the same cell when you copy a formula, put a dollar sign before its column and row, as in $B$1. In Excel for Microsoft 365 on Windows, you can select the reference while editing a formula and press F4 to add the dollar signs or cycle through the reference types. Absolute references are useful when a formula should keep using a fixed rate, target, or input as you fill it across a worksheet.

What You’ll Need

Excel for Microsoft 365 on Windows, with a workbook containing a formula or sample data. The same reference notation is also used in other recent desktop versions of Excel.
A cell you want formulas to keep referring to, such as a tax rate, and a basic understanding of which direction you plan to copy the formula.
A keyboard with an F4 key. On some laptops, you may need to hold Fn while pressing F4, depending on the keyboard settings.

You do not need to turn on a special setting or convert the worksheet. The dollar signs are part of the reference in the formula; they do not change the value stored in the referenced cell.

Step-by-Step

Identify the cell that must stay fixed. Look at the formula you want to copy and decide which input should remain the same in every copied version. A tax rate, commission percentage, exchange rate, or fixed lookup value often belongs in an absolute reference. A row-by-row amount or quantity usually needs to change as the formula moves. For example, if every order uses one rate stored in B1, B1 is the cell to lock; each order amount should remain relative so it can adjust for its row.
Enter the formula using ordinary references first. Suppose B1 contains the rate 8%, A2 contains an amount of 50, and you want the result in C2. With that layout, enter =A2*B1 in C2; the result is 4. The calculation is 50 multiplied by 0.08. Starting with the regular references makes it easier to see which part needs to remain fixed before you copy the formula.
Edit the formula and select the reference to lock. Click C2, then click in the formula bar or press F2 to edit the formula in the cell. Put the insertion point on the B1 reference, or select the full reference in the formula. Press F4 once. Excel changes that reference to $B$1, locking both its column and its row. The finished formula is =A2*$B$1. In this layout, the result in C2 remains 4, but when you copy the formula down, the reference to the rate stays at B1.
Understand the F4 cycle before using it repeatedly. When a cell reference is selected while you edit a formula, each press of F4 cycles through four forms: A1, $A$1, A$1, and $A1, then back to A1. The first form is fully relative. The second locks both the column and row. The third locks only the row, and the fourth locks only the column. Stop when the form you need appears. If you press F4 when the reference is not selected or the insertion point is elsewhere, Excel may not change the reference as intended.
Use a mixed reference when only one direction should stay fixed. A mixed reference locks either the column or the row, not both. For example, suppose B2 contains 3, the headers in row 1 contain values, and C1 contains 10. In C2, the formula =$B2*C$1 returns 30. The column B stays fixed while the row number can change; row 1 stays fixed while the column letter can change. If you copy this formula one column right and one row down, its references adjust to =$B3*D$1. This is useful for a multiplication table or other grid where each result combines a value from a fixed column with a heading from a fixed row.
Copy the formula to the cells that need it. After checking the dollar signs, select the formula cell and drag its fill handle, the small square at the lower-right corner of the selection, over the destination cells. You can also copy and paste, or use Home > Fill > Down after selecting the formula cell and cells below it. In the tax example, copying C2 to C3 changes the amount reference to A3 but keeps the rate reference at $B$1. That is the intended behavior: each row uses its own amount and the same rate.
Check a copied formula rather than relying only on the displayed result. Select a destination cell and inspect the formula bar. Confirm that fixed cells still have dollar signs in both places, and that row-specific or column-specific references changed as expected. For the example above, if A3 contains 100 and B1 still contains 8%, copying the formula down should make C3 calculate 8. A correct-looking result in one cell does not guarantee every copied reference is right, especially if nearby values happen to produce similar results.
Lock a range when the whole range must remain in place. Absolute references can mark both ends of a range. For example, if B2:B4 contains 2, 3, and 5, enter =SUM($B$2:$B$4) in D2. The result is 10. Copying that formula to another cell leaves the summed range at B2:B4. To lock a range with F4, select each endpoint reference in turn and use the shortcut, or type the dollar signs yourself. Check both ends: locking only one endpoint can allow the other end to shift when the formula is copied.
Use the same dollar-sign method for a reference on another worksheet. If a sheet named Rates has 8% in B1, and A2 on the current sheet contains 50, enter =A2*Rates!$B$1 in C2. The result is 4. The exclamation mark separates the sheet name from the cell reference; the dollar signs lock the cell when you copy the formula. If the sheet name contains spaces, Excel surrounds it with apostrophes, as in 'Rate Card'!$B$1. You can also type an equals sign, select the other sheet and cell, and then finish the formula; add or cycle the dollar signs before filling the formula.

Common Mistakes

Locking too much or too little: An absolute reference such as $B$1 will not move in either direction. That is correct for one fixed rate, but not for a value that should follow each row. Conversely, a reference such as B1 changes in both directions when copied. Decide whether to lock the column, the row, or both based on how the formula will be filled.

Pressing F4 at the wrong time: F4 changes a reference when you are editing a formula and the relevant cell reference is selected or the insertion point is within it. It does not reliably add dollar signs if you have simply selected a cell without entering formula-edit mode. Click in the formula bar or press F2, select the reference, and try again.

Changing the formula’s result by accident: Dollar signs do not freeze a cell’s value. They keep a reference pointed at the same address. If the value in the locked cell changes, formulas using it recalculate. To preserve a one-time result rather than a live calculation, you would need a different approach, such as copying and pasting values.

Locking only one end of a range: In a range such as B2:B4, each endpoint has its own column and row. If a formula must always use precisely that range, make sure both endpoints are absolute. Otherwise, copying the formula may shift part of the range and produce an unintended total.

Confusing worksheet protection with an absolute reference: A dollar sign does not prevent someone from editing a cell. It only controls how a reference adjusts when a formula is copied. Protecting cells or a sheet is a separate task.

Tips

Use F4 for speed, but type the dollar signs directly if you only need to adjust one part of a reference or if the shortcut is unavailable.
Plan the copy direction. A fixed column is often useful when filling right, and a fixed row is often useful when filling down, though the correct choice depends on the worksheet layout.
Give important input cells clear labels, such as Tax rate or Target, and consider a distinct fill color. This makes it easier to recognize which reference should remain absolute.
Test a formula in one copied cell before filling a large range. Inspect the formula bar and verify the result using a simple hand calculation.
To change several existing formulas, edit one correct formula and then fill it across or down where appropriate. Avoid assuming that selecting a large range and pressing F4 will convert every reference inside every formula the way you intend.
When you do not want to alter the original worksheet, test the formula in a spare cell or make a copy of the sheet first.

Troubleshooting

F4 does not add dollar signs: Make sure you are editing a formula, not just selecting a worksheet cell. Click the formula bar or press F2, then place the cursor in the cell reference. On some Windows laptops, the top-row keys control brightness or media functions by default; try Fn+F4 or use the keyboard’s function-lock setting. You can also type the dollar signs in the formula directly.

The shortcut changes a different part of the formula: Undo the edit if needed, return to formula-edit mode, and select the exact reference you want to change. When a formula contains multiple references, F4 cycles the reference associated with the current cursor position. Select each reference separately if you need to lock more than one.

The formula shifts after copying: Check whether the reference has dollar signs before both the column and row, or only the part you intended to lock. For an unchanging cell address, use $B$1. For a fixed column with a changing row, use $B2; for a changing column with a fixed row, use B$1. Correct the original formula and fill it again.

The formula shows an error or an unexpected total: Verify that the referenced cell or range is correct, the worksheet name is spelled correctly, and the formula includes the required operators and parentheses. For a sheet name with spaces, use apostrophes around the sheet name. Also check that values intended for arithmetic are numbers rather than text. Absolute referencing will not fix an incorrect range or invalid data.

You are using Excel for Mac or Excel for the web: The dollar-sign notation works in these versions too, but keyboard behavior can differ. In Excel for Mac, Command+T is commonly used to cycle reference types while editing a formula; some keyboards may also require Fn with a function key. Excel for the web may handle function keys differently depending on the browser and keyboard, so typing the dollar signs directly is a reliable alternative. Menu names and shortcut behavior can vary by platform, but the reference forms remain the same.

Frequently Asked Questions

What does an absolute reference mean in Excel?
An absolute reference points to a specific cell address that does not change when a formula is copied. Dollar signs before both the column and row, such as $B$1, lock both parts.

What is the Windows shortcut for an absolute reference?
While editing a formula and working on a cell reference, press F4 to cycle through relative, absolute, and mixed reference forms. On some laptop keyboards, use Fn+F4.

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

Can I make only the column or row absolute?
Yes. Put a dollar sign before only the part you want to hold fixed. Use $A1 to lock the column, or A$1 to lock the row.

Does an absolute reference stop the referenced value from changing?
No. It keeps the formula pointing to the same cell address, but Excel still recalculates the formula if that cell’s value changes.

Do absolute references work across worksheets?
Yes. Include the worksheet name and an absolute cell address, such as Rates!$B$1. If the worksheet name contains spaces, put apostrophes around it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.