Three kinds of reference
A1 is relative. Copy it one column right and it becomes B1; copy it one row down and it becomes A2. This is the default and it is usually what you want.
$A$1 is absolute. It never changes, no matter where you copy it.
$A1 and A$1 are mixed. The dollar locks whatever follows it — $A1 locks the column but lets the row move; A$1 locks the row but lets the column move.
Press F4 while editing a reference to cycle through all four states.
Why mixed references matter
Mixed references are what let you write one formula and fill it across a whole grid.
Build a multiplication table with headers in row 1 and column A: the formula =$A2*B$1 in cell B2 fills correctly across every cell. The column lock keeps it reading from column A; the row lock keeps it reading from row 1.
If you find yourself writing slightly different formulas in each cell, you probably need a mixed reference instead.
Named ranges
Select a range and type a name in the Name Box, or use Formulas → Define Name. Then write =SUM(Revenue) instead of =SUM($B$2:$B$400).
Names are absolute by default and make formulas self-documenting. They also survive row insertion better than hard-coded ranges. Use the Name Manager to audit them — orphaned names pointing at #REF! are a common cause of mysterious errors in inherited workbooks.
Structured references
Convert a range to a Table with Ctrl+T and references change entirely: =SUM(Sales[Amount]) refers to the Amount column of the Sales table.
These grow automatically as rows are added, which is the single best reason to use Tables. A pivot table or chart built on a Table expands with the data instead of silently ignoring new rows.