To keep a cell reference constant in an Excel formula when you copy it to other cells, you use absolute referencing by adding dollar signs ($) before the column letter and/or the row number.
Understanding Absolute, Relative, and Mixed Cell References
Excel uses different types of cell references that determine how a cell address changes when you copy a formula:
- Relative Reference (e.g., A1): The cell reference adjusts based on the relative position of rows and columns. If you copy a formula with A1 from cell B2 to C2, the formula in C2 will now reference B1.
- Absolute Reference (e.g., $A$1): The cell reference never changes, regardless of where you copy the formula. The column and row are both fixed.
- Mixed Reference (e.g., $A1 or A$1): Either the column or the row is fixed, but not both. $A1 keeps the column constant, while A$1 keeps the row constant.
How to "Lock" a Cell in Excel
You "lock" or make a cell reference absolute by using dollar signs ($). Here's how:
- Absolute Reference: If you want cell A1 to always be referenced, no matter where you copy the formula, use
$A$1. This locks both the column (A) and the row (1). - Mixed Reference (Column Fixed): If you want the column to remain the same but the row to change, use
$A1. The column (A) is locked, but the row will adjust when copied. - Mixed Reference (Row Fixed): If you want the row to remain the same but the column to change, use
A$1. The row (1) is locked, but the column will adjust when copied.
Examples
| Scenario | Formula in Cell A1 | Copied to Cell B2 | Explanation |
|---|---|---|---|
| Sum of B1 and C1, B1 always referenced | =$B$1+C1 |
=$B$1+D2 |
B1 ($B$1) remains the same because it's absolutely referenced. C1 becomes D2 as it's a relative reference. |
| Sum of A1 and B1, row 1 always referenced | =A$1+B1 |
=B$1+C2 |
Row 1 (A$1) remains the same, but the column changes (A to B). B1 becomes C2 as it's a relative reference. |
| Sum of A1 and B1, column A always referenced | =$A1+B1 |
=$A2+C2 |
Column A ($A1) remains the same, but the row changes (1 to 2). B1 becomes C2 as it's a relative reference. |
| Multiplying A1 by a constant in D1, D1 locked | =A1*$D$1 |
=B2*$D$1 |
The value in A1 changes relative to the cell it's copied to, but D1 always stays the same because the column and row are locked with $ symbols, making it an absolute reference. |
How to Add Dollar Signs Quickly
While you can manually type the dollar signs, you can also use the F4 key (or Fn + F4 on some keyboards). Select the cell reference in the formula bar and press F4 repeatedly to cycle through the relative, absolute, and mixed referencing options.
Common Use Cases
- Calculating percentages based on a fixed total: If your total is in cell A1, use
=(B1/$A$1)to calculate the percentage of each item in column B relative to the total in A1. - Creating lookup tables: When using
VLOOKUPorHLOOKUP, you often want to fix the lookup table range. - Applying a consistent rate or factor: If you have a tax rate in cell D1, use
$D$1in your formulas to always use that specific rate.
