Relative, Absolute, and Mixed Cell References
Relative Cell References
Note that in the example above, when we copied the formula from one cell to others, the formula automatically changed to add the cells in the corresponding rows:
- The formula in cell E4 added B4, C4 and D4.
- The formula in E5 added B5, C5 and D5.
This happened because the formula we entered contained Relative cell references.
Relative cell references are the default in Excel. They are called "relative" because the actual cell row and column used in the formula are not significant. Instead, the significant thing is the location of the cell used in the formula relative to the location of the cell in which the formula is entered.
The way to think of the formula E4 = B4 + C4 + D4 is Result = (cell 3 places to the left) + (cell 2 places to the left) + (cell 1 place to the left) of the cell in which the Result is entered.
Absolute Cell References
Sometimes you want a formula to refer to a specific cell regardless of where in your worksheet the formula is executed. In the following example, the formulas in column F all refer to cell B1:
The way to tell Excel that a formula should always refer to a specific cell is to use dollar signs ($). In the above example, the formula we used in cell F4 is =E4 / $B$1. No matter where we put this formula in our worksheet, it will divide the cell one to the left of the cell in which we enter the formula by cell B1.
Finally, notice that we entered the dollar sign twice in the above formula, before the "B" and before the "1". The first $ made the column an absolute reference and the second made the row an absolute reference. In this example, it was not actually necessary to make the column an absolute reference. The takeaway here is that when you refer to a cell in a formula, you can make either or both of the column and row references Absolute or Relative.
Mixed Cell References
Mixed cell references are a combination of relative and absolute references.
Just like in absolute cell references, in mixed cell references the dollar sign ($) is used to refer to a specific cell that is fixed.
An example of a mixed cell reference would be: $F4. This would mean that the the column letter, F, is fixed, while the row number is allowed to be copied from cell to cell.