Write reliable formulas
Build calculations that remain correct when data grows or copies across cells.
Pivot Table Excel Tutorial
From Kevin Stratvert. Watch here or open it on YouTube .
As the video builds calculated fields or source columns, pause to trace which cells change when the formula is copied.What this lesson is really solving.
Translate the business rule into words, examples, and edge cases before writing the formula. Use explicit ranges or table references, lock references intentionally, and surface errors rather than hiding every failure behind blank text.
Understand the idea before touching the steps.
A formula is a tiny program: inputs, logic, output, and failure cases.
Follow these steps in order.
Take the action in each step; then use the deliverable below to prove the lesson is finished.
- 1
Start with simple arithmetic and relative versus absolute references before nesting functions.
- 2
Use SUMIFS or COUNTIFS for conditional aggregation and lookup functions with explicit match behavior.
- 3
Handle blanks and errors intentionally; do not hide every error with a blanket fallback.
- 4
Test formulas against hand-calculated examples, zeros, missing values, duplicates, and boundary dates.
See the standard in context.
Revenue is Units × Unit Price only for non-cancelled rows. A helper column expresses that rule, a small test block covers zero units, missing price, and cancelled status, and the control total compares calculated revenue with the finance extract.
Inspect before you move on.
- The formula is tested on normal, boundary, blank, and invalid inputs.
- Copied formulas reference the intended rows and fixed assumptions.
- Error handling distinguishes truly optional blanks from broken logic.
Your deliverable
A calculation sheet with tested formulas and a visible assumptions block.
Watch for this
Hard-coding business rules or cell addresses inside dozens of formulas with no central assumption.
Prove it—don’t just recognize it.
You can explain each formula in plain language and show a test case that would catch a wrong result.
Quick knowledge check
Answer from the lesson—not from confidence alone. Score at least 2 of 3 to unlock completion.
This curriculum-aligned check is scored automatically and stored with your account when signed in. It is an objective learning signal, but it has not yet been independently validated as a standardized assessment.
Tools, templates, and references
Pass the knowledge check above first.
Completion unlocks after a score of 2 out of 3. Then confirm that you produced the lesson deliverable.
Go to the knowledge check