The relative cell reference changes when copied or filled to other cells; the absolute reference does not. In A1-style notation, the dollar signs control this behavior: A1 is fully relative and shifts both its column letter and row number, $A$1 is fully absolute and stays fixed, and the two mixed forms ($A1 and A$1) lock one coordinate while letting the other drift. Excel and Google Sheets both follow this convention, and the change happens because the spreadsheet stores relative references as offsets from the formula's own cell, recalculating them at the destination.
How the spreadsheet stores and adjusts references
A formula like =A1+1 in cell B2 is not stored as the literal text "A1." The engine records "one column left, one row up" as a relative offset. When you copy that formula to C3, the offset is reapplied from the new location, landing on B2, so the formula becomes =B2+1. This offset model is why relative references feel like they "follow" the formula through the grid.
The dollar sign is a lock flag on a single coordinate. In $A$1, both the column lock and row lock are set. In $A1, only the column is locked; the row is still a relative offset. In A$1, only the row is locked. The engine evaluates each coordinate independently, which is why mixed references behave asymmetrically when you fill down versus across.
Filling is not identical to copying in every case. Dragging the fill handle down a column increments the row offset by one per row. Dragging across increments the column offset. Copying and pasting to a nonadjacent cell computes the offset from source to destination in one jump. In all cases the lock flags are preserved and the unlocked coordinates are recalculated, so the underlying mechanism is the same.
A subtlety: the reference type is a property of how the formula is written, not of the cell it points to. You can convert between types without retyping by placing the cursor on the reference and pressing F4 (Windows) or Cmd+T (Mac), which cycles A1 → $A$1 → A$1 → $A1 → A1. Each press toggles the lock state, and the cycle order matters when you are editing a mixed reference mid-formula.
Quantified context: offsets, ranges, and fill distances
The magnitude of the shift equals the distance you move the formula. Copy a formula from B2 to B10 and every relative row reference moves down 8 rows. Copy from B2 to E2 and every relative column reference moves right 3 columns. The arithmetic is exact and predictable, which is what makes relative references reliable for building grids of calculations.
A single formula filled across 12 months and down 500 rows can generate 6,000 calculated cells from one entry. If that formula mixes an absolute anchor to a tax rate in $B$1 with relative references to monthly revenue in row 3, the anchor stays at $B$1 in all 6,000 cells while the revenue reference walks across columns and down rows. Get the lock wrong and 6,000 cells compute against the wrong input, often without an error message.
Ranges behave the same way. The range A1:A10 in a formula contains two relative endpoints. Copy it down one row and it becomes A2:A11. Anchor it as $A$1:$A$10 and it stays put. A partially anchored range like $A1:$A10 locks the columns but lets both row endpoints slide, which produces a window that moves down the sheet as you fill.
The F4 cycle itself has four states and returns to the start after four presses. That is a small but useful fact when you are editing quickly: an even number of presses returns the reference to fully relative, an odd number leaves it locked in some way.
Comparison of the four reference types
| Reference form | Column locked | Row locked | Behavior when copied right | Behavior when filled down | Typical use |
|---|---|---|---|---|---|
| A1 (relative) | No | No | Column shifts right | Row shifts down | Grids of parallel calculations |
| $A$1 (absolute) | Yes | Yes | Stays A1 | Stays A1 | Fixed rates, constants, lookup anchors |
| $A1 (mixed) | Yes | No | Stays in column A | Row shifts down | Column headers pinned, rows walking |
| A$1 (mixed) | No | Yes | Column shifts right | Stays in row 1 | Row labels pinned, columns walking |
The two mixed forms are mirror images, and choosing the wrong one is the most common source of off-by-one errors in lookup tables. If your lookup values run down a column and your formulas fill across a row, you typically want the column locked and the row free, which is $A1. Reverse the layout and you want A$1. Match the lock to the direction you will fill.
Edge cases that break the simple rule
Structured references in Excel Tables do not use dollar signs at all. A formula referencing Table1[Revenue] adjusts automatically as the table grows or shrinks, and copying it behaves differently from copying an A1-style reference. The relative-versus-absolute framing does not map cleanly onto structured references, so treat them as a separate system with their own rules.
Copying a formula between workbooks or between sheets can trigger reference rewriting if the source and destination differ in sheet names or external links. The lock flags survive, but the sheet qualifier may be added or changed. A reference like $A$1 on Sheet1 becomes Sheet1!$A$1 when copied to Sheet2, and the absolute lock applies only within that qualified address.
Cutting and pasting is not the same as copying. When you cut a formula and paste it elsewhere, Excel does not adjust relative references at all; the formula moves intact. This is a deliberate design choice, and it surprises people who expect cut to behave like copy. The distinction matters when you are reorganizing a model.
Deleting or inserting rows above a formula shifts relative references but leaves absolute references pointing at the same address, which may now contain different data. An absolute reference to $A$1 after inserting a row above row 1 still points at $A$1, but the contents of $A$1 may have changed. The lock protects the address, not the value.
Common misconceptions
The most persistent myth is that absolute references are "safer." They are not safer; they are different. An absolute reference where you needed a relative one produces a column of identical results, which looks plausible and is wrong. Relative references where you needed an absolute anchor produce shifting references that may silently point at empty cells or unrelated data.
A second misconception is that the $ sign is formatting. It is functional syntax, and removing it changes computation, not appearance. This is why copying formulas with the wrong locks can produce correct-looking numbers in the first cell and wrong numbers everywhere else.
A third myth is that mixed references are advanced or rarely needed. They are the workhorses of two-dimensional models, such as a price table where rows are products and columns are regions, and a single formula must pull the right price by locking the product column while letting the region column walk.
The counterintuitive professional caveat: a formula that works when copied right may fail when copied down, and vice versa, because the two mixed forms are not interchangeable. Professionals audit models by copying a formula in both directions and checking both the first and last cells of the filled range, not just the first. The last cell is where a wrong lock tends to reveal itself.
Practical actions and checkpoints
Before filling a formula across a range, decide which coordinates must move and which must stay. Write the reference with explicit locks, then test by copying to a distant cell and inspecting the result. Concrete steps:
Identify the anchor inputs (rates, constants, lookup keys) and lock the coordinate that must not move; use $A$1 for fully fixed, $A1 or A$1 for one-axis locking. Fill one cell in each direction first, then compare the first and last cells of the intended range before filling the whole block. Use F4 (Windows) or Cmd+T (Mac) to cycle lock states rather than retyping the reference, and count the presses so you land on the intended state. When copying to a nonadjacent cell, verify the offset arithmetic manually for one reference before trusting the rest. Remember that cut-and-paste does not adjust relative references; use copy-and-paste when you want the offset behavior. Audit filled ranges by checking the last row and last column, where a wrong mixed reference usually surfaces.
A simple threshold for when to slow down: any formula that will be filled more than about 10 cells in either direction is worth a two-cell test first. Below that, the cost of a wrong lock is small and easy to spot; above it, errors propagate quietly.
Limitations and what the rule does not cover
The relative-versus-absolute rule applies to A1-style references in Excel, Google Sheets, LibreOffice Calc, and most spreadsheet engines that use dollar-sign locking. It does not apply to R1C1 notation, where references are inherently relative unless bracketed, and where the entire mental model changes. It also does not apply to structured references in tables, named ranges, or references generated by functions like INDIRECT, which resolve at calculation time and can behave unpredictably when copied.
The rule also says nothing about whether the copied formula is correct. Reference adjustment is mechanical; correctness depends on whether the movement matches your intent. A formula can adjust exactly as designed and still produce wrong results because the underlying data layout does not match the reference pattern.
Finally, the lock protects an address, not a value or a sheet. If rows are inserted, sheets are renamed, or workbooks are linked, absolute references can point at different content than you intended. The $ sign is a coordinate lock, and treating it as a guarantee of stability overestimates what it does.
For spreadsheet work involving financial models, regulatory reporting, or any calculation where errors carry consequences, this article is general educational information about how reference syntax behaves, not professional advice on model design or financial reporting. Consult a qualified professional for guidance on your specific workbook and reporting requirements.