To make a cell reference absolute in Excel, put a dollar sign before both its column letter and row number, such as $E$1. In a formula, you can also select the reference while editing and press F4 on Windows; Excel changes it to an absolute reference so it stays fixed when you copy the formula. This is useful when many calculations need to refer to the same input, such as a conversion factor or percentage.
An absolute reference locks both parts of a cell address. For instance, $E$1 remains E1 when a formula is filled down or across. By contrast, a relative reference such as E1 adjusts to the formula’s new location. The steps below apply to Excel for Microsoft 365 on Windows, with notes for Mac and Excel for the web.
What You’ll Need
- An Excel workbook with a formula you want to enter or copy.
- The address of the cell that should stay fixed, such as E1.
- For the shortcut method, a keyboard with an available F4 key. You can type the dollar signs yourself if your keyboard or Excel version makes the shortcut inconvenient.
Step-by-Step
- Identify the value that should remain fixed. Decide which cell your formulas should keep referring to as they are copied. A shared input might be a conversion factor in E1, a fixed target in B1, or a constant value in another cell. Put that value in one cell and note its address. Locking a reference does not protect the cell from being edited; it only prevents the address from shifting as a formula is copied.
- Start the formula in the first result cell. Click the cell where you want the first answer, type an equals sign, and build the formula using the relevant cells and operators. For a concrete example, suppose B2 contains a quantity of 12 and E1 contains a factor of 1.25. In C2, enter
=B2*E1. Because this is ordinary multiplication, C2 returns 15. At this point both references are relative, so copying the formula would adjust both addresses. - Make the shared reference absolute by typing dollar signs. Edit the formula and change E1 to $E$1, placing one dollar sign before the column letter and another before the row number. With quantity values in B2:B5 and the fixed factor 1.25 in E1, enter
=B2*$E$1in C2. The result is 15 when B2 is 12. B2 remains relative, while $E$1 is fixed. Press Enter to finish the formula. - Use F4 to add the dollar signs while editing. You can let Excel change the reference instead of typing the symbols. Double-click the formula cell or select it and press F2 to edit. Click the E1 reference inside the formula, or use the arrow keys to position the insertion point within that reference. Press F4 once. Excel changes E1 to $E$1. Press Enter to accept the edit. On many laptops, you may need Fn+F4 if the function keys control brightness or other hardware features.
- Check that the formula locks only what you intend. Look at the completed formula in the formula bar or in the cell while editing. For the quantity example, it should read
=B2*$E$1. The two dollar signs belong on E1 because the factor must not move. B2 has no dollar signs because each row should use its own quantity. If you make B2 absolute too, every copied formula will keep multiplying the original quantity instead of using the quantity on that row. - Copy or fill the formula to other cells. Select C2 and drag its fill handle—the small square at the lower-right corner of the selection—down through C5. You can also copy C2, select C3:C5, and paste. The references should adjust to
=B3*$E$1,=B4*$E$1, and=B5*$E$1. The B row changes to match each result row, while E1 stays fixed. If you fill across columns, the fixed column and row in $E$1 remain unchanged as well. - Use a mixed reference when only one part should stay fixed. Absolute references lock both the column and row, but a mixed reference locks only one. Suppose A2:A4 contains base values 2, 3, and 4, and B1:D1 contains factors 10, 20, and 30. In B2, enter
=$A2*B$1. The result is 20 because 2 is multiplied by 10. Copy the formula across and down to fill B2:D4: $A locks the source column while allowing its row to change, and B$1 locks the header row while allowing its column to change. This is useful for building a calculation table.
When editing a reference, F4 cycles through four forms: A1, $A$1, A$1, and $A1. Keep pressing it until the form you need appears. In the mixed-reference example, pressing F4 repeatedly on A2 lets you select the version that locks just the column; do the same for B1 to lock just the row. The exact starting point in the cycle depends on the reference’s current form.
Common Mistakes
- Locking the wrong cell. If you add dollar signs to the changing input instead of the shared value, copied formulas may repeat the same calculation. Check which cell should change from one row or column to the next before editing.
- Locking only one part by accident.
$E1fixes the column but not the row, whileE$1fixes the row but not the column. Use$E$1when both must stay fixed. - Expecting an absolute reference to stop all changes. The dollar signs control how a reference adjusts when a formula is copied or filled. They do not prevent someone from changing the referenced cell’s value, deleting it, or moving data around.
- Pressing F4 when the formula is not being edited. In that situation, F4 may repeat the last action rather than change a reference. Enter edit mode and select or position the cursor on the cell address first.
- Removing the dollar signs while rewriting the formula. A formula can look correct before you edit it and become relative again if you retype the address without its dollar signs. Review the formula after making changes, especially before filling a large range.
Tips
- Use the F4 cycle to work faster. While the cursor is on a cell reference in a formula, press F4 until the reference has the desired pattern. One press usually changes a plain reference to fully absolute; additional presses switch through the mixed forms and back to relative.
- Think in terms of how a formula will be copied. If it will move down but should keep the same column, lock the column. If it will move across but should keep the same row, lock the row. If neither part should move, lock both. This simple check helps distinguish absolute from mixed references.
- Test with a small fill before copying widely. Copy the formula to one cell below or beside the original and inspect the result. Confirm that the reference intended to move did move, and the fixed reference did not.
- Keep shared inputs easy to identify. Add a clear label near the fixed value, such as Conversion factor, and consider using a distinct fill color for input cells. Formatting does not change the formula, but it can make the workbook easier to review.
- Remember that cell references can include a worksheet name. When a formula points to a cell on another sheet, the sheet reference and cell address are separate parts. For example, locking the cell address does not necessarily lock the sheet name. Review the whole reference when copying a formula between sheets.
Troubleshooting
F4 inserts a symbol or changes a computer setting: First make sure Excel is in formula-edit mode and the cursor is on the reference. On a laptop, try Fn+F4. Keyboard settings and hardware shortcuts differ, so typing the dollar signs directly is a dependable alternative: edit E1 as $E$1, for example.
The formula changes the supposedly fixed cell when copied: Inspect the formula in the copied cell. If it shows E2 or F1 instead of $E$1, the reference was not fully absolute. Edit the formula, add a dollar sign before both E and 1, and fill the range again. If you used a mixed reference intentionally, check that the locked part matches the direction in which you copied.
Every copied row uses the same input value: The changing reference may have been locked by mistake. In the quantity example, the formula in C3 should point to B3 but still point to $E$1. Remove dollar signs from the quantity reference so its row can adjust; keep both dollar signs on the shared factor.
F4 does nothing in Excel for the web: Shortcut behavior can depend on your browser, operating system, and keyboard. Edit the formula and type the dollar signs directly. Excel for the web supports formulas with absolute references, so a reference such as $E$1 will remain fixed when the formula is copied even if you do not use the shortcut.
The result is unexpected even though the reference looks right: Check the values in both cells, the operation in the formula, and whether the fixed cell contains a number or text. Select the result cell to inspect the formula bar, then click the referenced input cell to verify its address and value. Absolute referencing controls movement of the address; it does not validate the data or correct a different formula error.
Frequently Asked Questions
What does an absolute reference mean in Excel?
An absolute reference keeps both the column and row fixed when you copy or fill a formula. Dollar signs mark the locked parts: $E$1 keeps pointing to column E, row 1.
How do I make a cell reference absolute without F4?
Edit the formula and type a dollar sign before the column letter and row number. Change E1 to $E$1, then press Enter. The formula can be copied normally afterward.
What is the difference between $A$1, $A1, and A$1?
$A$1 fixes both the column and row. $A1 fixes only column A, so the row can change. A$1 fixes only row 1, so the column can change.
Does an absolute reference stay fixed when I copy a formula to another sheet?
The dollar signs keep the referenced row and column fixed when the formula is copied. A reference to a different sheet also contains a sheet name; check that part separately if you want the formula to keep referring to the same worksheet.
Does F4 work in Excel for Mac?
Excel for Mac supports changing reference types while editing a formula. Depending on your keyboard and settings, use F4 or Command+T. If a key combination does not cycle the reference, type the dollar signs manually.
Can I use an absolute reference in Excel for the web?
Yes. Formulas such as one containing $E$1 work in Excel for the web. If the shortcut is unavailable or intercepted by the browser, enter the dollar signs directly in the formula.
Related
- Changing Date Format in Excel: Steps, Options, and Fixes
- How to Add the Same Header in Excel on Every Sheet
- Add a Header in Excel for the Web
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel CONCAT Formula: Join Text and CellsExcel’s CONCAT function joins text from cells or text you type into a formula; it does not add…
- Excel SUMPRODUCT Sample Data: How to Use It in FormulasExcel’s SUMPRODUCT multiplies corresponding values in ranges and adds the results, so it can calculate totals such as…
- LEFT Excel String Formula: Extract Text from the LeftIn Excel, use the LEFT function to return a chosen number of characters from the beginning of a…
- INDIRECT Excel Formula for a Different Sheet: ExamplesIf cell A1 contains a worksheet name and you want the value in B2 on that sheet, use…
- OFFSET in Excel: Syntax, Examples, and UsesIn Excel, OFFSET returns a reference to a cell or range located a specified number of rows and…
- Switch Excel Formulas to Values: Methods and TipsTo switch formulas to fixed values in Excel for Microsoft 365 on Windows, select the formula cells, press…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.