A cell reference is the address a formula uses to find a value, and the dollar sign decides what happens to that address when the formula is copied: A1 adjusts, $A$1 stays put, and $A1 and A$1 lock one half each. That one character is the entire difference between relative, absolute, and mixed references, and it decides whether a filled formula reads the right cells or the wrong ones.
The address itself is column letter plus row number. A1 means the cell where column A meets row 1; columns run A through XFD (16,384 of them) and rows run 1 through 1,048,576. A reference can name a single cell, a range like B2:B5, or a cell on another sheet.
What is a relative reference in Excel?
A relative reference, such as A1, is based on the relative position of the cell holding the formula and the cell it points at. Copy or fill the formula and the reference adjusts to keep that relative position. Microsoft’s example is a formula in B2 referring to A1: copied to B3, it adjusts from =A1 to =A2. And per the same page, “By default, new formulas use relative references.”
Relative is the right default, because most filled formulas mean “the cells to my left,” not one fixed address:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | Total |
| 2 | Widgets | 12 | 4.00 | 48.00 |
| 3 | Bolts | 50 | 0.30 | 15.00 |
| 4 | Panels | 8 | 25.00 | 200.00 |
How do you lock a cell in an Excel formula?
Sometimes a formula must keep pointing at one cell no matter where it is copied. People reach for different words for this (lock a cell, anchor a cell, keep it constant), and they all mean the same thing: an absolute reference. Written $A$1, it always refers to a cell in a specific location, and copying or filling the formula does not adjust it. Microsoft’s docs use =$B$4*$C$4: copied from D4 to D5, the formula stays exactly the same. Type the dollar signs by hand, or press F4 with the reference selected; the F4 section covers the shortcut.
The classic case is one rate cell feeding a whole column:
| A | B | C | |
|---|---|---|---|
| 1 | Rate | 5% | |
| 2 | Rep | Sales | Commission |
| 3 | Ana | 2,000 | 100 |
| 4 | Ben | 3,400 | 170 |
| 5 | Cho | 1,250 | 62.50 |
The dollar signs change nothing about the result in the cell where you first type the formula; they matter the moment the formula is copied or filled. And when one fixed cell matters to the whole model, a named range is the readable alternative: =B3*Rate says what =B3*$B$1 only implies, and a name never needs its dollar signs checked.
Lookup formulas are the other heavy user of locking. An INDEX and MATCH built on $A$2:$C$500 survives filling down; the same formula on A2:C500 walks its table down one row per fill and starts missing entries at the bottom.
What does the dollar sign mean?
In a cell reference, the dollar sign has nothing to do with currency. It fixes whatever it stands in front of: a $ before the column letter fixes the column, a $ before the row number fixes the row. Two positions to lock means four possible forms:
B1locks nothing; both halves adjust when the formula is copied.$B$1locks both; the reference never moves.$B1locks the column; the row still adjusts.B$1locks the row; the column still adjusts.
The first two are the everyday pair. The two half-locked forms are mixed references, and they have their own use.
What is a mixed reference in Excel?
A mixed reference has either an absolute column and a relative row ($A1), or an absolute row and a relative column (A$1). Copying adjusts only the relative half. Microsoft’s example: a mixed reference copied or filled from A2 to B3 adjusts from =A$1 to =B$1. The column followed the copy; the locked row did not.
Mixed references earn their keep when one formula has to fill in two directions at once:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 1 | 2 | 3 | |
| 2 | 1 | 1 | 2 | 3 |
| 3 | 2 | 2 | 4 | 6 |
| 4 | 3 | 3 | 6 | 9 |
What does F4 do in Excel?
Typing dollar signs by hand is fiddly, so Excel has a shortcut. Select the cell that contains the formula, select the reference you want to change in the formula bar, and press F4 to switch between the reference types. Microsoft’s shortcut reference describes the behavior: with a cell reference or range selected in a formula, F4 cycles through all the combinations of absolute and relative references, so repeated presses walk B1 through all four forms. In Excel for Mac, Command+T moves through the same combinations.
One gotcha comes from F4’s day job. When no reference is selected, F4 repeats your last command or action, when it can. If pressing it does something unrelated to dollar signs, Excel was not editing a formula; double-click into the cell or click into the formula bar first.
Do references change when you move a formula?
Copying and moving look similar and behave differently, and Microsoft documents them as different operations. When you copy a formula, relative cell references change, which is everything the sections above described. When you move a formula, by cutting and pasting it or by dragging the cell border, the rule flips: “the cell references within the formula do not change no matter what type of cell reference that you use.”
The practical reading: dollar signs protect a formula from copying, and a move needs no protection at all. A moved formula keeps calculating from the exact cells it always did. If you actually wanted the references to re-point after relocating a formula, moving will never do it; copy the formula to the new cell instead and delete the original.
The R1C1 reference style, briefly
Excel has a second reference style in which both the rows and the columns are numbered. R1C1 style indicates a cell’s location with an “R” followed by the row number and a “C” followed by the column number: R2C2 is an absolute reference to the cell in row 2, column 2, the cell A1 style calls $B$2, and R[-2]C is a relative reference to the cell two rows up in the same column. Microsoft positions the style as useful for computing row and column positions in macros.
In Excel for Windows, the toggle lives in Excel’s options: select the File tab, then Options, and in the Formulas category, under Working with formulas, set or clear the R1C1 reference style check box. Most visits to that check box go in the off direction: if column headings show numbers instead of letters and formulas look like =RC[-1], the style got switched on, and clearing the box brings the letters back.
How a missing dollar sign slips through
The absolute-reference failure mode is quiet. Type =B3*B1 where you meant =B3*$B$1, fill it down, and every copy multiplies by a different cell:
No error value appears. The result is a number computed from the wrong cell, which is harder to spot than the loud failures cataloged in Excel’s error values, explained, because every cell in the column still shows something plausible. What gives it away is the pattern: one formula in a range that disagrees with its neighbors.
The short version
A cell reference is a formula’s pointer, and the dollar sign controls what happens to it on copy. A1 is relative, the default, and adjusts to keep its relative position. $A$1 is absolute and always refers to the same cell, which is how you lock a rate cell into a fill-down. $A1 and A$1 are mixed and lock one half each, the trick behind two-direction fills. F4 (Command+T on Mac) cycles a selected reference through the forms while you edit. Moving a formula never adjusts its references; copying adjusts the relative ones. And R1C1 style numbers the columns, with its check box under File > Options > Formulas in Excel for Windows. This kind of locking only controls how a reference copies; keeping a cell from being edited at all is sheet protection, a separate feature.
For the other symbols that turn up in a formula, from the arithmetic operators to the reference colon, the formula symbols glossary is the companion to this page. The dollar sign is one keystroke, and its absence is invisible after a fill-down. When a workbook you did not build lands on your desk, scan it for formulas that break their range’s pattern before trusting the totals, and when two versions of a model disagree, compare them to see exactly which references moved.
