2.1 Relative, absolute and mixed references
You can write one formula and fill it across a whole grid without it breaking.
Before:01. Excel — First StepsUnlocks:03. Excel — Analysis
Dollar signs in a reference decide what moves when a formula is copied, and this topic works through relative, absolute and mixed references until filling a formula across a grid becomes predictable. It opens the formula module because every function that follows depends on references behaving as intended. The F4 key cycles a reference through its four forms and saves a lot of typing. The classic confusion is that the dollar sign has anything to do with currency; it is purely an anchor, and mixed references, anchoring only the row or only the column, are where most people need the practice.
Work through these
A1 vs $A$1 vs $A1 vs A$1, and F4
A reference that moves when copied, one that never moves, and two that move in one direction only. Which dollars you place decides which of the four you get, and this is the single most useful thing in this module.
Filling right and down and predicting what happens
Filling a formula across a grid works when the references were written with copying in mind. Predicting what a filled formula will say, before filling it, is the skill this item builds.
Named ranges and when they beat dollars
Giving a range a name makes formulas readable and removes the need to freeze references by hand. It is the better answer whenever a formula would otherwise be full of dollar signs.
Referencing another sheet and another workbook
Pulling a value from another sheet or another file works and creates a dependency that can break when files move. Knowing the risk is what lets you use it deliberately.
Practice file
- xl-03-formula-drills.xlsx
Thirty short formula tasks with a self-checking answer column that turns green when you are right.
Opens in Excel. Every name, number and address in these files is made up. No account needed to download.
Sign in to keep your progress.
Free resources
We haven't checked most of these for screen reader use yet.
Links last checked 29 Aug 2026.
Stuck here?
Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.
Checking your session…
Topics shown in module order.