excel tutorialCell References in Excel
Cell references in Excel are very important. Once you understand cell references, your Excel life will change for good. This chapter explains the three different type of cell references. Relative, absolute and mixed reference.
Relative Reference
By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative. You can download the accompanied Excel file if you want.
1. Select cell D2 and drag it down to cell D5.
Result:
Do you see what happens? Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4, and so on. The reference in the formula is relative to the position of the cell containing the formula. In other words: each cell references its two neighbors on the left. Sometimes we don't want this type of reference. Read on.
Absolute Reference
See the formula in cell B2 below.
Cell B2 references cell A2 and cell E2. Again, both references are relative.
1. Select cell B2 and drag it down one cell.
Result:
Do you see what happens? Cell B3 references cell E3 which is not what we want. Solution: we need to fix the reference to cell E2 in the formula of cell B2. In other words: we need to make an absolute reference to cell E2.
2. To achieve this, place a $ symbol in front of the row number and column letter of cell E2 (or place your cursor on E2 in the formula bar and press F4). This way the reference to cell E2 will not change when you drag the formula down (or across).
3. Drag cell B2 down again.
Check: Cell B5 references cell A5 and cell E2!
Mixed Reference
Sometimes we need a combination of relative and absolute reference (mixed reference). See the following example with two products and three reductions. You may find yourself in a similar situation every now and then.
1. See the formula in cell E3. We want to copy this formula to the other cells quickly. Drag cell E3 across one cell.
Result:
Do you see what happens? The reference to the price should be a reference to column B! Solution: place a $ symbol in front of the column letter ($B2) in the formula of cell E3.
In a similar way, when dragging cell E3 down, the reference to the reduction should be a reference to row 7. Solution: place a $ symbol in front of the row number (A$7) in the formula of cell E3.
Result:
2. Now we can safely (and quickly!) drag the formula to the other cells.
Result.
Check: cell G4 points to the correct cells.
Did you like this free Excel tutorial? Show your appreciation, vote for us.