Three behaviours, one address
A spreadsheet stores a formula's reference as a position relative to the cell holding it unless told otherwise. Enter =C7*D7 in E7, copy it down one row, and the copy reads =C8*D8, because the software kept the distance, not the address. That default is what makes filling a column of line totals take one drag. It becomes a fault the moment a formula must keep reading one fixed cell, such as a tax rate or an exchange rate sitting at the top of the sheet, while everything else moves.
- Relative (B4) shifts in both directions when copied. Right for row-by-row arithmetic.
- Absolute ($B$4) stays exactly where it was. Right for a single input every formula shares.
- Mixed ($B4 or B$4) pins only the column or only the row. Right for a grid, such as a price table where each result multiplies a row heading by a column heading.
Named ranges for single inputs
For one shared input, a name is often better than dollar signs. A formula reading =Net*VAT_Rate explains itself to the next person who opens the file, and the name stays pinned wherever the formula is copied.
Excel cycles through the four forms when the reference is selected in the formula bar and F4 is pressed, which is quicker and less error-prone than typing dollar signs by hand. Google Sheets reads the same notation. The idea reaches beyond formulas: when recording a macro in Google Sheets, the recorder asks up front whether to use absolute or relative references, and the answer decides whether the replay acts on the exact recorded cells or on whatever cell is selected. A pivot table avoids the question entirely by aggregating without formulas, and data validation rules often point at a fixed list with an absolute reference. The spreadsheets topic and advanced Excel both build on getting this right first.