Established onExcel 365, Windows and MacExcel 2016 and laterGoogle Sheets, web: no R1C1 notation
A reference written without dollar signs moves when the formula is copied. A reference written with them does not. That is the whole of what the dollar sign does, and the sentence is worth reading twice, because what it does not do causes more trouble than what it does.
What a formula actually stores
Conceptually a reference is an offset from the cell containing the formula: two columns left, same row. Copying carries the offset, which is why a filled-down column works at all.
A dollar sign converts that offset into a fixed position for the axis it precedes. Locking the column alone, or the row alone, is the mixed form, and it is the whole technique behind any grid where one formula fills a rectangle: lock the column when reaching left for the row label, lock the row when reaching up for the column heading.
What it does not do
It does not protect a reference against insertion or deletion. Insert a row above row one, and a reference written as fully locked to A1 is rewritten to A2, dollars and all.
This is correct behaviour: the software is preserving the meaning, which is the cell containing that value, and the value moved. It is also routinely misunderstood as a promise that the reference will always point at A1, and it is not that promise.
The function that does make that promise
A reference constructed from text is not adjusted by anything, because at the time the sheet is edited it is not a reference at all, only a piece of text. It survives insertion, deletion and rearrangement completely.
Which sounds useful and is mostly a way of building a formula that breaks silently the moment somebody restructures a sheet. It also cannot be traced by any of the tools in the final part of this site, because there is no dependency to trace, and it is volatile, which the next entry covers.
The notation that makes inconsistency visible
Excel offers a reference style in which the offset is written out explicitly rather than resolved into a letter and a number. In that style, every cell of a correctly filled-down column shows exactly the same text.
Which means an inconsistent formula in the middle of a column stops being invisible and becomes a line that reads differently from its neighbours. Switching to it for ten seconds is the fastest audit available in the product, and almost nobody knows the setting exists.
References across sheets and files
A reference to another sheet in the same file behaves like any other and is renamed automatically when the sheet is renamed. A reference to another file is different: it stores a path, it is not updated when the other file moves, and it does not recalculate unless that file is open or the link is refreshed.
The entry on links between files in the fifth part is about the consequences. The short version is that a number arriving from another file is a number whose age you do not know.
Naming instead of locking
Where a formula reaches for a constant — a rate, a threshold, an exchange rate — the better answer than locking the reference is naming the cell. A named cell survives insertion, reads as what it means rather than as a coordinate, and cannot be silently repointed by a rearrangement.
The cost is that names are workbook-level and invisible from the sheet, which is the same complaint this part ends on.
What to check in a sheet you inherit
Look at the first and last cell of every calculated column and compare which parts are locked. A column where the lock pattern differs between its ends has been edited by hand somewhere in the middle, and that is the defect the final part calls an inconsistent formula.
What we cannot verify
The copy behaviour, the insertion behaviour and the alternative notation are documented by the vendor and demonstrable directly. Google Sheets behaves the same way for copying and insertion; it does not offer the alternative notation, which was confirmed by testing rather than found in documentation stating its absence.
The keystroke worth learning
Both products cycle a reference through the four lock states while the cursor is inside it in the formula bar. It is one key in Excel and a different one on Mac, and it removes the need to type dollar signs, which is where most of the errors in this area come from.
The four states in order are: neither locked, both locked, row only, column only. Knowing the order matters because the mixed forms are the ones people want and the ones they most often get wrong by typing.
In short
- A dollar sign fixes a reference against copying, and against nothing else.
- Inserting a row rewrites a fully locked reference, correctly, to preserve its meaning.
- A reference built from text survives everything and cannot be traced by any tool.
- The mixed form is the whole technique behind one formula filling a rectangle.
- In the alternative notation a consistent column reads identically in every cell.
- Naming a cell beats locking a reference to it, at the cost of being invisible.