Course outline / Basic formulas
Lesson 6 of 75 · Beginner · 4 exercises
Absolute and mixed references
Lock a cell with $ so formulas copy correctly.
What you'll practise
- Lock a cell with $
- See the copy in action
- Divide by a fixed total
- Lock just the column or the row
Lock a cell with $
When you copy a formula down a column, Excel shifts its references to match. =B2*F2 in row 2 becomes =B3*F3 in row 3. That's usually what you want, but not when every row should use the same cell, such as one tax rate.
A dollar sign locks the part of the reference it sits in front of. $F$2 is an absolute reference: it stays on F2 wherever the formula is copied. In Excel, press F4 while the cursor is on a reference to add the dollar signs.
In cell C2, multiply the price in B2 by the VAT rate in F2, locking the rate as $F$2.
See the copy in action
Here's what the formula from the last step looks like after it's copied down to row 4. The price reference has moved to B4, but the locked rate is still $F$2.
In cell C4, write that copied formula: the price in B4 multiplied by $F$2.
Divide by a fixed total
Percent-of-total columns are the most common place you'll need an absolute reference. Each row divides its own value by the one total cell.
B6 already adds up all the prices. Because column D is formatted as a percentage, the result shows as a percent.
In cell D2, divide B2 by the total in B6, locking the total as $B$6.
Lock just the column or the row
You can lock only half of a reference. This is a mixed reference:
$A3 locks the column, so it always reads column A, but the row can still change.
C$1 locks the row, so it always reads row 1, but the column can still change.
This sheet has quantities down column A and prices across row 1. One mixed formula, copied across the whole grid, fills in every quantity times price. Pressing F4 repeatedly cycles through $A$1, A$1, $A1 and A1.
In cell C3, multiply the quantity in column A by the price in row 1, written as $A3 and C$1.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.