Excel FormulasRelative and absolute cell references: the dollar sign
When you copy a formula, Excel changes the cell addresses in it. Usually that is exactly what you want. Sometimes one address must stay where it is. The dollar sign is how you tell Excel which is which, and misunderstanding it is behind a large share of wrong answers in spreadsheets.
Relative references move
A plain reference such as C2 is relative. Excel does not really remember "cell C2". It remembers "the cell two columns to the left on this row", or whatever the position is. Copy the formula one row down and it looks one row further down. That is how a single formula, copied down a column, works for every row.
Suppose D2 holds =B2*C2, quantity times price. Copied to D3 it becomes =B3*C3. Nothing needs to be retyped.
When moving goes wrong
Now put a tax rate in one cell, say F1, and write =B2*C2*F1 in D2. Copy it down and D3 becomes =B3*C3*F2. The tax reference slid down too, to an empty cell. The results from row 3 onwards are zero.
Absolute references stay
A dollar sign in front of the column letter and another in front of the row number locks the reference: $F$1. However the formula is copied, it keeps pointing at F1. The correct formula is =B2*C2*$F$1.
Read $ as "always". $F$1 is "always column F, always row 1".
The F4 key
You do not have to type the dollar signs. While editing a formula, click on the reference and press F4. Each press moves to the next style:
| Press | Reference | Meaning |
|---|---|---|
| once | $F$1 | column and row both locked |
| twice | F$1 | row locked |
| three times | $F1 | column locked |
| four times | F1 | nothing locked |
On laptops where the F keys do other jobs, hold Fn as well.
Mixed references
A dollar sign on only one part locks only that part. This matters when a formula is copied both across and down, as in a grid.
$A2keeps the column and lets the row change: "always column A, on whatever row I am".B$1keeps the row and lets the column change: "always row 1, in whatever column I am".
A multiplication table shows it best. With the numbers 1 to 10 down column A from A2 and across row 1 from B1, the formula in B2 is =$A2*B$1. Copy it across and down, and every cell multiplies the number at the left of its row by the number at the top of its column. With no dollar signs, or with both parts locked, the table would be wrong.
The same pattern builds summary tables with SUMIFS and COUNTIFS: labels down the side are referred to as $H2, and labels across the top as I$1.
What to lock: a checklist
- A single settings cell (tax rate, exchange rate, pass mark): lock both,
$F$1. - A lookup table in VLOOKUP, INDEX or MATCH: lock both ends,
$A$2:$E$8. Forgetting this is the commonest cause of lookups that work in the first row and fail further down. - The list in a RANK or COUNTIF copied down a column: lock it.
- A running total: lock only the start,
=SUM($B$2:B2). The range grows as it is copied down. - A grid formula: lock the column of the side labels and the row of the top labels.
The dollar sign has nothing to do with money
It does not format anything as currency and does not change the value. It only affects what happens when the formula is copied. A formula that stays in one cell gives the same answer with or without dollar signs.
Moving is not copying
Copying a formula adjusts its relative references. Cutting and pasting, or dragging a cell to a new place, moves the formula without changing it. The references still point to the original cells.
Names instead of dollar signs
A cell or range can be given a name. Select F1, click in the Name Box at the left of the formula bar, type TaxRate and press Enter. Now =B2*C2*TaxRate works anywhere, is always absolute, and can be read by someone who has never seen the sheet. Names cannot contain spaces. The Name Manager on the Formulas tab lists and edits them.
References to other sheets and workbooks
A reference to another sheet has the sheet name and an exclamation mark in front: =Prices!B2. If the sheet name has a space, it goes in single quotation marks: ='Price List'!B2. Dollar signs work in the same way: =Prices!$B$2.
A reference to another workbook adds the file name in square brackets. Such links break when files are moved or renamed, so keep related data in one workbook where you can.
Whole columns and tables
A:A means all of column A, and 2:2 all of row 2. They grow with the data and need no dollar signs for the row, but they make some functions slow.
A better way to have ranges that grow is to turn the data into a table with Ctrl and T. Formulas then refer to columns by name, such as =SUM(Sales[Amount]). These references always cover the whole column, however many rows are added.
Finding a reference mistake
If a copied formula gives a wrong answer, click one of the wrong cells and press F2. The coloured outlines show which cells it is using. A reference that has drifted to an empty or wrong cell is easy to see. Then lock it in the first formula and copy again.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.