What an absolute reference does and why you need it
An absolute reference is a cell address in Excel that stays the same when you copy a formula to other cells. When you copy a normal formula down a column, Excel automatically shifts the cell addresses — so a formula that refers to B2 becomes B3, then B4, and so on. An absolute reference prevents that shift, keeping the formula pointed at the exact same cell no matter where you paste it.
You need absolute references when one cell contains a value that should explore to many calculations. The most common example is a tax rate, discount percentage, or conversion factor that you want to use in dozens of rows without having to type the formula over and over.
Without absolute references, you would either have to manually edit each formula or copy the value into every row. With them, you type the formula once, copy it down, and every row uses the same reference cell automatically.
Key Takeaways
- Add a dollar sign before the column letter and row number to lock a cell reference: $B$2 instead of B2.
- You can lock only the column ($B2), only the row (B$2), or both ($B$2) depending on how you plan to copy the formula.
- Type the dollar signs by hand, or select the cell reference in the formula bar and press F4 to cycle through the locking options.
- Absolute references work the same way in Google Sheets, LibreOffice Calc, and most other spreadsheet programs.
The dollar sign syntax: how to write an absolute reference
An absolute reference uses dollar signs before the column letter and the row number. Instead of writing B2, you write $B$2. The dollar signs tell Excel "do not change this part of the address when the formula is copied."
You can type the dollar signs directly into the formula. Click the cell where you want to enter the formula, type the equals sign, then type the cell address with dollar signs: =A1*$B$2. When you press Enter and copy that formula down, the A1 will shift to A2, A3, and so on, but the $B$2 will stay locked on cell B2 in every row.
The dollar sign goes before both the column and the row. $B$2 locks both. $B2 locks only the column. B$2 locks only the row. Most of the time you will use $B$2 (both locked), but the partial locks are useful when you are copying a formula both across columns and down rows at the same time.
Using F4 to add dollar signs without typing them
If you are building a formula and want to lock a reference without typing dollar signs, you can use the F4 key. Click in the formula bar where your formula is displayed at the top of the screen, then select the cell address you want to lock by double-clicking it or clicking and dragging over it.
With the cell address selected, press F4. Excel will cycle through the four locking options: B2 (no lock), $B$2 (both locked), B$2 (row locked), and $B2 (column locked). Keep pressing F4 until you see the version you want, then press Enter to confirm the formula.
This method is faster than typing dollar signs if you are building a complex formula with many references. It also reduces the chance of mistyping a dollar sign in the wrong place.
A real example: using a tax rate across many rows
Suppose you have a spreadsheet with product prices in column A and you want to calculate the total cost including tax in column B. The tax rate is 8.5% and sits in cell D1. You would write the formula in B1 as =A1*1.085, but that hardcodes the tax rate into the formula. If the tax rate changes, you have to edit every formula.
Instead, put the tax rate (1.085) in cell D1 and write the formula as =A1*$D$1. Now when you copy the formula down to B2, B3, and beyond, each row multiplies its own price (A2, A3, etc.) by the tax rate in D1. If the tax rate changes, you update D1 once and all the formulas automatically use the new rate.
The formula in B2 becomes =A2*$D$1, in B3 it becomes =A3*$D$1, and so on. The A column shifts because it is a relative reference, but the D1 stays locked because it is absolute.
Partial locks: when to lock only the column or only the row
Most of the time you will lock both the column and row with $B$2. But if you are copying a formula both down and across — for example, building a multiplication table or a matrix of calculations — you might lock only one dimension.
Use $B2 (column locked, row free) when you want the column to stay the same as you copy across, but the row to shift as you copy down. Use B$2 (row locked, column free) when you want the row to stay the same as you copy across, but the column to shift as you copy down.
For most everyday spreadsheets — calculating totals, explore a single rate or percentage to many rows — you will not need partial locks. But they exist for situations where you are reusing a value in multiple directions at once.
Copying and pasting formulas with absolute references
Once you have written a formula with an absolute reference, copy it the same way you copy any formula. Click the cell with the formula, press Ctrl+C (or Cmd+C on Mac), then select the range where you want to paste it and press Ctrl+V (or Cmd+V).
Excel will copy the formula to every cell in the range. The relative references (like A1) will shift to match each row, but the absolute references (like $D$1) will stay locked on the original cell. You can also click and drag the fill handle — the small square at the bottom right corner of the cell — down or across to copy the formula.
If you paste the formula into a cell and it does not look right, click the cell and check the formula bar at the top. You will see exactly which cells the formula is referencing. If a reference shifted when it should not have, add dollar signs. If a reference did not shift when it should have, remove them.
Absolute references in Google Sheets and other spreadsheet programs
The dollar sign syntax works the same way in Google Sheets, LibreOffice Calc, Microsoft 365 online, and most other spreadsheet programs. If you learn absolute references in Excel, you can use them in any spreadsheet tool.
Google Sheets also supports F4 to cycle through locking options, though the keyboard shortcut may vary depending on your operating system. The formula bar works the same way — click in it, select the cell address, and press the locking shortcut to add dollar signs.
If you are moving a spreadsheet from Excel to Google Sheets or vice versa, absolute references will transfer without any changes. The formulas will work exactly the same in both programs.
Frequently Asked Questions
What happens if I copy a formula with an absolute reference to a different sheet?
The absolute reference stays locked to the same cell on the same sheet. If you want to reference a cell on a different sheet, you need to include the sheet name in the formula, like =$Sheet2.$B$2. The dollar signs still lock that reference even when the formula is on a different sheet.
Can I lock a reference to a named range instead of a cell address?
Yes. If you have created a named range (like "TaxRate" for cell D1), you can use the name directly in a formula: =A1*TaxRate. Named ranges are always absolute — they do not shift when you copy the formula — so you do not need dollar signs.
Why did my formula show a different result after I copied it?
Check the formula bar to see which cells the formula is referencing in its new location. If a cell reference shifted when it should not have, you forgot to add dollar signs. If a reference did not shift when it should have, you added dollar signs by mistake. Edit the formula and copy it again.
Do I need absolute references if I am only copying a formula to one other cell?
No. Absolute references matter when you are copying a formula to many cells and want some references to stay the same. If you are copying to just one cell, relative references work fine — Excel will shift them correctly, and you probably want it to.
Can I use absolute references in a formula that references another file?
Yes, but the syntax is longer. You write the file path, sheet name, and cell address with dollar signs: =[FilePath]Sheet1.$B$2. The exact format depends on your operating system and spreadsheet program, so check the formula bar after you type it to make sure Excel recognized the reference.