Cell references in Excel are very important. Understand the difference between relative, absolute, and mixed links and you’re on your way to success.
Relative link
By default, Excel uses relative links. Look at the formula in the cell D2. It refers (points) to cells B2 и С2. Both links are relative.
- Highlight a cell D2, hold its lower right corner and drag it down to the cell D5.
- Note! Cell D3 refers to cells B3 и C3. Cell D4 refers to cells B4 и C4. Cell D5 refers to cells B5 и C5. In other words, each considered column cell D refers to two adjacent cells on the left.
Absolute link
Look at the formula in the cell E3.
- To create an absolute cell reference H3, place a dollar sign ($) before the column letter and before the row number. You should get a link like this – $H$3.
- Now drag this formula to other cells.
- All formulas refer to a cell N3 remains fixed. As a result, the length and width in inches are calculated correctly.
mixed link
Sometimes you need a combination of relative and absolute reference (mixed reference).
- Look at the formula in the cell F2.
- You need to copy this formula to other cells. Copy formula from cell F2 into a cell G2 and see what happens:
- See what happened? Price reference (column Price) must be absolute on the column B and relative by line. To solve this problem, place a dollar sign ($) in front of the column letter − $B2.
- When copying a formula from a cell F2 в F3 already the link to the discount (string Reduction) must be absolute on the string 6 and relative by column. To solve this problem, place a dollar sign ($) in front of the line number − B$6.
- As a result, the formula should look like this:
=$B2*(1-B$6)
Note: We deliberately don’t put a dollar sign ($) in front of the row number in a cell reference. V2. So the link can change from V2 on B3 when copying the formula down. For the same reason, we don’t put a dollar sign ($) in front of a column letter in a cell reference. V6. So the link can change from V6 on С6 и D6 when copying the formula to the right.
Now if we copy this formula to other cells, the column references B and the line 6 will remain fixed.
