Relative & Absolute Cell References
Learn how relative, absolute, and mixed references behave when formulas are copied, and how to reference cells across worksheets.
Video
Watch the lesson video, then complete the reading and challenge.
Lesson Notes
Read through the key concepts before you try the challenge.
Understanding Relative References
Relative references are Excel’s default behavior. When a formula is copied, the row and column references adjust automatically.
Click cell D4 first, then type an equals sign (=) to begin entering your formula.
Enter the formula =B4*C4 to multiply Unit Price by Quantity.
Use the fill handle to copy the formula downward.
Notice how each row now references the corresponding unit price and quantity automatically.
Because the references were relative, Excel adjusted the row numbers automatically as the formula was copied.
Locking a Cell with Absolute References
If a formula uses a value that should not change — such as a tax rate — we must lock the reference.
Without locking the tax rate in E2, copying the formula causes incorrect results.
Because the reference was not locked, Excel shifted the tax rate cell as the formula was copied.
If you ever see your tax rate changing as you copy a formula, it usually means the reference was not locked.
Add dollar signs to lock the reference. $E$2 locks both the column and row. You can also click the cell reference and press F4 to insert the dollar signs automatically.
Now copy the formula down using the fill handle.
Final Result with Absolute Reference Applied
After locking the tax rate and copying the formula down, every row calculates correctly.
The tax rate remains fixed while unit prices and quantities adjust normally.
Mixed References
Excel allows partial locking of either rows or columns. This is called a mixed reference.
Mixed references are commonly used when building structured financial models or multiplication tables.
- $A$2 → Fully absolute (row and column locked)
- A$2 → Row locked, column relative
- $A2 → Column locked, row relative
- A2 → Fully relative
Referencing Cells Across Worksheets
You can reference cells from another worksheet using the format SheetName!CellAddress.
When referencing another worksheet, Excel automatically inserts the sheet name followed by an exclamation point.
If the sheet name contains spaces, Excel will wrap it in single quotation marks. Example: ='Paper Goods'!E13
If the sheet name or cell reference is incorrect, Excel will return a #REF! error.
Knowledge Check
Which symbol makes a cell reference absolute in Excel?
Practice File
Download this file and follow along with the lesson.
Challenge
Apply what you've learned in this lesson.
- Download the Cell References practice workbook.
- Click the Paper Goods tab at the bottom-left of the workbook.
Click cell D4 and begin your formula with =.
In cell D4, enter a formula that multiplies the unit price in B4, the quantity in C4, and the tax rate in E2. Make sure to use an absolute reference for the tax rate ($E$2) because it must remain constant.
Use the fill handle to copy the formula to cells D5:D12.
Change the tax rate in cell E2 to 6.5%. If your totals did not update correctly, check whether E2 was locked properly using $E$2.
When you're finished, your workbook should look like this:
- Click the Catering Invoice tab.
- Delete the value in cell C5 and replace it with a reference to the total cost of the paper goods (cell E13 on the Paper Goods worksheet).
- Use the same steps to calculate the sales tax for each item on the Menu Order worksheet. The total in cell E14 should update.
- In cell C4 of the Catering Invoice worksheet, create a reference to the updated total.
When you're finished, the Catering Invoice worksheet should look like this:
