How to Enter an Absolute Cell Reference in Excel

How-to basics · Updated November 18, 2026

To enter an absolute reference in Excel, put the cursor on a cell reference inside a formula and press F4 on Windows; Excel adds dollar signs so the reference stays fixed when you copy the formula. For example, A1 becomes $A$1. You can also type the dollar signs yourself. This guide covers both methods in Excel for Microsoft 365 on Windows, plus the Mac and web differences.

An absolute reference locks both the column and the row. It is useful when many formulas need to refer to the same input, such as a tax rate, discount, exchange rate, or fixed lookup cell. The formula can then be filled elsewhere without that reference shifting.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or access to Excel for Mac or Excel for the web. The core reference notation works across these versions.
  • A worksheet with a formula to edit, or a plan for the formula you want to enter. You can practise in a blank workbook.
  • A cell address to keep fixed, such as E1. Remember that an absolute reference is written with a dollar sign before both the column letter and row number: $E$1.

Step-by-Step

  1. Identify which cell must stay fixed. Decide which value your formula should continue using when you copy it. For example, if E1 contains a rate that applies to every row of a sales table, E1 is the reference you may need to lock. Make sure the cell contains the intended value before writing the formula.

  2. Select the cell where the formula should go. Click the destination cell, such as C2, and type an equals sign to begin a formula. You can enter a formula from the formula bar as well as directly in the cell. If you are editing an existing formula, double-click the cell or select it and press F2 on Windows to enter edit mode.

  3. Build the formula and include the cell address. Enter the operators and references the calculation needs. As a concrete example, suppose column B contains a row-by-row amount and E1 contains a multiplier. With amounts in B2:B6 and the fixed multiplier in E1, enter the following formula in C2: =B2*E1. At this point both references are relative, so Excel would normally adjust both when the formula is copied.

  4. Place the insertion point inside the reference you want to lock. While the formula is being edited, click between the characters in E1, or use the arrow keys to move the caret into that address. It is usually easiest to place the caret immediately before or after the reference. You do not need to select the entire formula or the worksheet cell E1.

  5. Toggle the reference type. With the caret in E1, press F4. Excel changes the address to $E$1, adding a dollar sign before the column and another before the row. The formula should now read =B2*$E$1. If you are using a Mac, press Command+T while editing the reference; depending on your keyboard settings, you may also need to use the function-key controls provided by your Mac.

  6. Confirm the formula. Press Enter to finish the formula. In the example, assume B2 contains 12 and E1 contains 1.5. The formula returns 18 because 12 multiplied by 1.5 equals 18. The dollar signs are part of the address notation; they do not change the arithmetic result.

  7. Copy the formula to other rows. Select C2 and drag the fill handle, the small square at the lower-right corner of the selected cell, down through C6. You can also copy C2, select C3:C6, and paste. The relative reference B2 changes to B3, B4, and so on, while the absolute reference remains $E$1. For example, the copied formula in C3 becomes =B3*$E$1.

  8. Check the copied results and references. Click one of the filled cells and look at the formula bar. Confirm that the row-specific reference has changed but the fixed reference still says $E$1. If the formula is not behaving as expected, check that E1 contains a number and that the original formula was entered in the intended starting row.

You can enter an absolute reference manually at any point by typing the dollar signs before the column and row, such as $E$1. This is often the simplest option when typing a new formula, using Excel for the web, or working on a keyboard where the shortcut is inconvenient.

Common Mistakes

  • Locking the wrong part of the address. A dollar sign before the column but not the row, as in $E1, locks only the column. A dollar sign before the row but not the column, as in E$1, locks only the row. If you need both to remain fixed, use $E$1.

  • Pressing F4 when the caret is not in a reference. The shortcut acts on a cell reference while you are editing a formula. If the cell is merely selected and you are not editing, F4 may repeat the last action instead. Click inside the formula or press F2 first, then position the caret in the reference.

  • Making every reference absolute. A formula often needs a mixture of reference types. In =B2*$E$1, B2 should move with each row, but E1 should not. If you change B2 to $B$2 as well, every copied formula will keep using the value in B2.

  • Using the wrong address because of an inserted or moved cell. Absolute references keep the same cell address when a formula is copied, but they are not a guarantee that the cell will always contain the same kind of information. If you move or replace worksheet data, inspect formulas that rely on it.

  • Expecting dollar signs to format a number as currency. In $E$1, the dollar signs mark a fixed reference; they do not apply currency formatting. To display a calculated result as money, format the result cell separately using the Number Format controls on the Home tab.

Tips

  • Use F4 to cycle through all four reference styles. When the caret is in a reference, repeated presses cycle through A1, $A$1, A$1, $A1, and back to A1. This lets you choose a fully relative, fully absolute, or mixed reference without typing dollar signs.

  • Think about what should move when copied. A relative reference changes its row and column based on where the formula goes. An absolute reference stays fixed in both directions. A mixed reference locks only one direction. This simple question—what should move?—helps you pick the correct form before filling a formula.

  • Use mixed references for tables. For a formula copied across columns but always using row 1, a reference such as B$1 keeps the row fixed while allowing the column to change. For a formula copied down rows while always using column A, $A2 keeps the column fixed while the row changes.

  • Type the address when keyboard shortcuts are awkward. You can add dollar signs directly while entering a formula. This works in Windows, Mac, and Excel for the web and makes the desired reference type explicit. Check that you have placed each dollar sign before the correct part of the address.

  • Test the formula in one more cell before filling a large range. Copy it down or across once, then inspect the adjusted formula. A quick check can catch a reference that was locked too much or too little before it affects many results.

Troubleshooting

  • F4 does not add dollar signs. First check that the cell is in edit mode and that the caret is inside a cell address in the formula. On some Windows laptops, the function-key row controls hardware features by default, so try Fn+F4. If that does not work, type the dollar signs into the reference manually.

  • The formula changes when you fill it. Click a copied formula and inspect its address in the formula bar. If the reference that should stay fixed has no dollar signs, edit the original formula and make it absolute, then refill the range. If only the row or column should stay fixed, use the appropriate mixed reference rather than locking both.

  • The copied formula shows an error or an unexpected value. Check that the locked cell contains the expected value and is the correct type for the calculation. For multiplication, for example, a text entry that looks like a number may not behave as intended. Also check the relative reference: it should point to the correct row after copying.

  • The keyboard shortcut behaves differently in Excel for the web. Browser and operating-system shortcuts can affect which keystrokes reach Excel. Edit the formula and type the dollar signs directly, or try the shortcut while the insertion point is inside the reference. In Excel for the web, the formula bar can be useful when editing a longer formula.

  • You see the formula instead of its result. Make sure the entry begins with an equals sign and that the cell is not formatted as Text. Change the number format to General, then re-enter the formula. Also check whether Show Formulas is enabled; on Windows, Ctrl+` toggles formula display. On a Mac, the shortcut can differ with keyboard layout, so check the Formulas tab or Excel keyboard shortcuts for your setup.

Frequently Asked Questions

What does an absolute reference mean in Excel?
An absolute reference keeps both the column and row fixed when a formula is copied. Excel shows this with a dollar sign before each part, as in $E$1.

What is the shortcut to make a reference absolute?
In Excel for Microsoft 365 on Windows, edit the formula, place the caret in the reference, and press F4. On Mac, use Command+T. You can always type the dollar signs manually.

Why does F4 create a mixed reference instead?
F4 cycles through several reference styles with each press. Continue pressing it until both the column and row have dollar signs, or type the reference in full, such as $E$1.

Can I make only the row or column absolute?
Yes. Use E$1 to lock row 1 while letting the column change, or $E1 to lock column E while letting the row change. These are mixed references.

Do absolute references stay fixed if I copy a formula to another worksheet?
The dollar signs keep the referenced row and column fixed within that reference, but copying between worksheets can change which sheet the formula points to unless the formula includes a sheet name. Check the resulting formula after copying across sheets.

Does an absolute reference prevent a cell from being edited?
No. It controls how a formula reference adjusts when copied or filled. It does not protect the referenced cell from being changed; worksheet protection is a separate feature.

Related

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.