A spreadsheet is a dependency graph wearing a grid. Every formula declares what it depends on, the engine works out an order, and recalculation flows downhill from whatever changed. Almost every confusing behaviour — why a copied formula breaks, why a total silently excludes rows, why a file takes ten seconds to open — is that graph behaving exactly as declared. So the skill is not memorising functions; it is learning to say precisely what a cell depends on, and to notice when you have said something other than what you meant. Read this for the graph, and for the places people accidentally lie to it.
Each chapter opens with the short version. Tap one to read the detail.
The grid, and why one fact per cell is the whole discipline
~2 min
A worksheet is a bounded grid, but the limits matter less than the habit they enforce: one fact per cell, one table per sheet, and nothing important stored in formatting.
References, and the difference a dollar sign makes
~2 min
A formula declares dependencies. Relative references shift when copied, absolute ones never move, and mixed ones pin exactly one axis — which is the whole mechanism behind one formula filling a two-dimensional table correctly.
Error values are diagnoses, not failures
~2 min
Each error names a specific cause: a deleted reference, a type mismatch, a lookup that found nothing, a division by zero. Reading the value tells you where to look — and suppressing it throws that information away.
Aggregation, and stating the filter inside the formula
~2 min
Summing is easy; summing the right rows is the skill. Conditional aggregation answers "total these, where that" in one formula, which removes the manual filtering step where most spreadsheet errors enter.
Decisions, and when a formula has taken on data's job
~2 min
Conditional functions return one of two values from a test, and nesting them models multi-way decisions. The point at which nesting becomes unreadable is the point at which a lookup table was the better answer.
Lookup: where most spreadsheet damage happens
~2 min
A lookup finds a value in one place and returns a related one from another. Its three failure modes are structural — an approximate-match default, a return column identified by position, and a search direction that only goes one way.
What a value IS versus how it looks
~2 min
A cell has a value and a display format, and they are independent. Dates are serial numbers formatted to look like dates — which is why date arithmetic works, and why a date that looks correct may be text that cannot be calculated with.
One formula, many results
~2 min
A dynamic array formula returns a range and spills it into neighbouring cells. The spill is one formula with one owning cell, which changes how a sheet is edited — you cannot overwrite part of a result.
Tables, and the tooling that keys off them
~2 min
Converting a range to a table gives it a name, structured references, and boundaries that grow with the data. Sorting, filtering, validation and conditional formatting all become more reliable because the range is no longer a guess.
Summarising, and choosing an encoding that does not mislead
~2 min
A PivotTable summarises without formulas by assigning fields to rows, columns and values. A chart then encodes those numbers as position or length, and that encoding choice is what makes a chart honest.
Getting data in, and cleaning it repeatably
~2 min
Imported data arrives dirty — numbers as text, ambiguous dates, stray whitespace. The real question is not how to clean it but whether the cleaning is recorded, because the same file usually arrives again.
Assumptions belong in cells, never in formulas
~2 min
A model computes outcomes from assumptions, and its quality is mostly a question of where those assumptions live — in labelled input cells that can be changed and tested, or buried as literals where nobody can find them.
Building a workbook someone else can survive
~2 min
A workbook others must use has different requirements from one you use alone: protection against accidental edits, assumptions written down, and a structure that does not depend on remembering how it works.
Written by Keentune. We are not affiliated with or endorsed by the organizations whose documentation informs this guide, and any linked sources belong to their respective owners.
All exam, test, and product names and trademarks are the property of their respective owners and are used here for identification and reference only. Keentune is independent study practice — not affiliated with, authorized, or endorsed by any of these organizations.