Absolute Cell Referencing ($): Locks a cell reference so it doesn't change when you copy or drag a formula. Think of the $ as putting glue on a cell address to hold it firmly in place.
The Problem: Look at the visual table. To get the "Total" in cell B2, you might write the formula =A2*C1. This works for the first row. But when you drag that formula down to cell B3, Excel automatically changes it to =A3*C2, which is wrong because the VAT rate is not in C2.
The Solution: To fix this, you must "lock" the reference to the VAT cell. In cell B2, you change the formula to =A2*$C$1. Now, when you drag it down to B3, the formula correctly becomes =A3*$C$1. The part without the dollar sign (A3) changes, but the part with the dollar signs ($C$1) stays locked.
Visual Example: Here's how it looks in a spreadsheet. The formula in cell B2 is dragged down to B3 and B4. Notice how the reference to
$C$1 (the VAT rate) never changes.
| Price |
Total |
20% |
| 100 |
=A2*$C$1 |
|
| 150 |
=A3*$C$1 |
|
Types of Locking:
* A1 (Fully Relative): Both column and row change when dragged. This is the default.
* $A$1 (Fully Absolute): Both column and row are locked. The formula will always point to cell A1, no matter where you copy it.
* $A1 (Mixed): The column is locked, but the row can change. Useful for dragging formulas down a column while always referring to column A.
* A$1 (Mixed): The row is locked, but the column can change. Useful for dragging formulas across a row while always referring to row 1.
Example: In our visual table, the formula is =A2*$C$1. When you drag it down one row, it becomes =A3*$C$1. The A2 reference shifted to A3 because it was relative, but the $C$1 stayed completely locked.
Common question: If a question asks you to modify a formula like =COUNTIFS(C2:C101;F2) so it can be safely dragged down without shifting the source data range, you add dollar signs to lock the range coordinates: =COUNTIFS($C$2:$C$101;F2).