Cell Referencing
Cell referencing is a critical concept in spreadsheet software like Excel.
- It allows you to refer to the contents of a cell in a formula rather than typing in a specific value
- This can make your spreadsheets more flexible and powerful
There are two types of cell referencing: absolute and relative.
Relative cell referencing is the default type.
- When you copy a formula that includes a relative cell reference, Excel adjusts the reference relative to the new location
- For example, if you copy the formula "=A1+B1" from cell C1 to C2, the formula will adjust to "=A2+B2"
Absolute cell referencing is indicated with dollar signs before the column and/or row reference (like $A$1).
- When you copy a formula with an absolute cell reference, that reference does not change
- For example, if you copy the formula "=$A$1+B1" from cell C1 to C2, the formula will stay as "=$A$1+B2"
Consider the following example spreadsheet:
|
A |
B |
C |
|
|
1 |
10 |
20 |
|
|
2 |
5 |
15 |
- If you input the formula "=A1+B1" in cell C1 and drag the fill handle down to copy the formula to cell C2, the formula in C2 will change to "=A2+B2"
- But if you input the formula "=$A$1+B1" in cell C1 and drag the fill handle down, the formula in C2 will still refer to cell A1: "=$A$1+B2"
Exam Tip
- Be careful when copying formulas! Make sure you're using the right type of cell reference for what you want to do
- Remember the dollar signs ($) for absolute cell referencing. It can save you a lot of time and hassle!
- Use cell references rather than the value of the cell
Worked example
An auction company sells toys. It uses a spreadsheet to show each person’s items and the amount of money the buyer and seller owe to the company.
Part of the spreadsheet is shown below.
a. The person selling the item pays a Seller’s commission on any item sold. This is calculated using the Selling price and finding a match or the next value below in the table, in cells I6 to J11.
Write a formula to display in cell F6, the Seller’s commission on the Double Decker bus toy.
This formula will be replicated down to cell F13.
[5]
VLOOKUP(D6, I$6:J$11,2)
VLOOKUP() [1]
(D6, [1]
I6:J11, [1]
correct use of $ [1]
2) [1]
or
IF(D6<$I$7, J$6, [1]
IF(D6<$I$8, J$7, [1]
IF(D6<$I$9, J$8, [1]
IF(D6<$I$10, J$9, [1]
IF(D6<$I$11, J$10, ,J$11))))) [1]
b. Describe how you could replicate the formula in cell F6 for each item.
[2]
2 of:
Click on the cell / F6 [1]
Move to the bottom RHS cell [1]
Select drag handle/cross / black box / double click on drag handle [1]
Drag handle/cross to F13 [1]
or
Click on the cell / F6 [1]
Click fill [1]
... down [1]
or
Click on the cell / F6 [1]
Click copy [1]
Select F7 to F13 [1]
Click paste [1]
or
Hover over the cell / F6 [1]
Move to the bottom RHS cell [1]
Select drag handle/cross / black box / double click on drag handle [1]
Drag handle/cross to F13 [1]
