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 behavior — 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 memorizing 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 guide for the graph, and for the places you might 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.
A workbook holds worksheets; a worksheet is a grid addressed by column letter and row number. That much is obvious. What follows from it is not.
The practical discipline is one fact per cell. A cell holding 12 units is a string; you cannot sum, compare or chart it without taking it apart first. Split the number from its unit, the first name from the last, the date from the note attached to it.
The matching trap is information stored in appearance. If you color a row to mean "disputed", you have recorded that fact nowhere a formula can read; the meaning lives only in the head of whoever chose the color. Merged cells break selection, sorting and references. Both feel like presentation choices and are in fact data-modeling choices.
The grid is also bounded: there are hard limits on rows, columns, nesting depth and formula length. You will rarely reach them, and when you do it is almost always a signal that the data belongs in a database rather than a sheet.
Rule: if a fact matters to a calculation, give it its own cell — never a color, a merge, or the tail of a text string.
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.
Every formula begins with = and states what it depends on. The engine builds a dependency graph from those statements and recalculates everything downstream of a change.
Reference style is the highest-value idea in the subject.A1 is relative: copy it down a row and it becomes A2. $A$1 is absolute and never moves. $A1 and A$1 are mixed, pinning the column or the row alone.
The mixed form is what lets a single formula fill a whole table. Write a multiplication grid as =$A2*B$1 and one cell, copied across and down, is correct everywhere. The row labels stay in column A because the column is pinned, and the column labels stay in row 1 because the row is pinned. Write it as =A2*B1 and copying breaks it immediately; write it as =$A$2*$B$1 and every cell returns the same number.
Names solve the readability half. A cell named tax_rate reads as itself, so you can review =price*(1+tax_rate) without cross-referencing anything. Names are absolute by default, which removes a whole class of copy bug.
Rule: before copying a formula, ask which parts must stay put and pin exactly those — the dollar signs are a statement about meaning, not formatting.
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.
Spreadsheet error values are a diagnostic vocabulary, and learning them is faster than debugging blindly.
#REF! means a reference no longer exists, usually because a row, column or sheet was deleted. It is the only common error indicating destroyed information rather than a bad input, which is why correcting a value never fixes it.
#VALUE! is a type mismatch: arithmetic attempted on something that is not a number. The usual cause is a number stored as text, which imports produce constantly.
#N/A means a lookup found nothing. In a lookup this is information, not a fault: it says the key is genuinely absent.
#DIV/0! is division by zero or by an empty cell, and #NAME? is an unrecognized name, typically a typo or text used without quotation marks.
The tempting fix is to wrap every formula in an error-suppressing function. That converts a diagnosis into silence. A suppressed missing value and a genuine blank become indistinguishable, and a reconciliation that should have failed now balances. Suppress only when you know which error you expect and why it is acceptable.
Rule: read the error value before touching the formula — it has already named the category of mistake.
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.
The basic aggregates are unremarkable until you notice how their variants differ, and the differences are where errors live. Counting is the clearest case: one function counts numbers, another counts non-empty cells of any type. Point the numeric one at a column of text-formatted numbers and it returns zero, correctly and unhelpfully.
Conditional aggregation is the real lever. Instead of filtering a range by hand and summing what remains, you state the condition in one formula: total this column, where that column meets a criterion. Multi-criteria forms extend it to several conditions at once.
This matters for auditability, not convenience. A manual filter-then-sum leaves no record of which rows were included; a conditional formula states the rule and re-evaluates when the data changes. The manual version is right once, on the day you did it.
One durable trap: a range that grows. A formula summing B2:B100 is silently wrong the moment row 101 appears. That is the argument for tables, later in this guide.
Rule: express the filter inside the formula so the rule stays visible and survives new data, rather than filtering by hand and summing what is left.
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.
The conditional function evaluates a test and returns one value if true, another if false. Everything else here is composition.
Combining tests with and / or keeps a formula flat; nesting one conditional inside another builds a decision tree. Nesting is correct and works. The difficulty is that nobody can read or safely modify a five-level nest, including you a month after writing it.
The signal to watch for: when the conditions form a table, use a table. A tax-band calculation written as four nested conditionals is a lookup pretending to be logic. Put the bands in cells and look them up, and the rates become data anyone can check and update, rather than literals buried inside a formula where no reviewer will find them.
Comparison brings one persistent trap: text and numbers do not compare as you expect when a number has arrived as text. The string "10" and the number 10 are not equal, and sorting the first gives alphabetical order where you wanted numeric.
Rule: keep decision rules in cells and decision logic in formulas — a formula that contains the business rules themselves has taken on data's job.
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.
Lookup is where most spreadsheet damage happens, and the reasons are structural rather than a matter of care.
Match type is the first. Older lookup functions default to an approximate match, returning the nearest value below rather than nothing when the key is absent. On unsorted data that yields a plausible wrong answer with no error at all. It is the worst possible failure, because nothing signals it.
Column position is the second. A lookup that returns "the third column" breaks silently when someone inserts a column, because the third column is now a different one. Anything identifying its target by position is fragile against ordinary editing.
Direction is the third. Classic lookup functions search rightward from the key, so to return a value to the left of it you need either restructuring or an index-and-match pair.
Newer lookup functions address all three: exact match by default, a returned range rather than a column number, and search in either direction. Availability varies by version, so check what the file must open in before you rely on them.
Rule: state the match type explicitly, never identify a return column by number, and treat a lookup that has never returned an error as one you have not yet tested.
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.
This chapter is one idea applied three times: the stored value and the displayed value are different things.
Number formats change appearance only. A cell displaying 3 may hold 3.4999, and a column of such cells will appear not to add up. The total is right and the visible addends are rounded. Rounding for display and rounding the value are different operations, and only one changes what later formulas see.
Dates are numbers. A date is a serial number counting days, formatted to display as a date. That is why subtracting two dates gives a count of days, why adding 30 moves a month, and why a date suddenly shows as a five-digit number when you clear its formatting. It is also why a date that looks correct may be text: imported dates often arrive as strings, sort alphabetically, and refuse to subtract.
Text functions exist largely to undo that. Splitting, trimming and converting are your repair kit for data that arrived as one string where it should have been several values — the 12 units problem from the first chapter, at scale.
Trailing spaces are the invisible version of this: two cells that look identical fail to match, and no amount of staring reveals why.
Rule: when a value misbehaves, check what it is before checking the formula — format hides type, and type is what arithmetic acts on.
One formula, many results
~2 min
A dynamic array formula returns a range and spills it into neighboring cells. The spill is one formula with one owning cell, which changes how a sheet is edited — you cannot overwrite part of a result.
Traditionally one formula filled one cell, and producing a column of results meant copying it down. A dynamic array formula instead returns a whole range, which spills into the cells beside and below it.
The consequential part is ownership. The spilled cells are not independent formulas; they are output. The formula lives in the top-left cell and the rest is display, which will surprise you the first time you try to edit a value in the middle of a result.
A spill needs room. If anything blocks the target area, the formula returns a spill error rather than overwriting. That is a deliberate refusal, and the right behavior, since silently destroying a neighbor's data would be far worse.
It also changes referencing. You can refer to a spilled range as a unit, so a downstream formula can consume "whatever that formula produced" instead of a fixed rectangle that may be the wrong size tomorrow.
Availability varies by version, and a workbook using spills can behave differently when opened in older software. Make that compatibility decision deliberately rather than discover it.
Rule: treat a spilled result as one object with one owning cell — to change part of it, change the formula rather than the output.
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.
A table is a named range that knows its own extent, and nearly every benefit follows from that.
Structured references replace coordinates with meaning: a formula referring to a column by name says what it is doing and survives an inserted column. Growth is automatic, too. Add a row and every formula, chart and pivot referencing the table includes it, which is the direct fix for the B2:B100 trap.
The surrounding tools improve for the same reason. Sorting and filtering are safer because the boundaries are known; the classic disaster of sorting one column and leaving the others behind is a range problem, and a table removes it. Data validation constrains what may be entered, which is far cheaper than cleaning it afterwards. Conditional formatting makes patterns visible, and is most robust when its rule refers to a column by name.
Validation and conditional formatting look like different features and are the same idea at two moments: one states a rule before entry, the other reveals its violation after.
Rule: convert any range you will add rows to into a table before writing formulas against it — most "my totals are wrong" problems are a range that stopped matching the data.
Summarizing, and choosing an encoding that does not mislead
~2 min
A PivotTable summarizes 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.
A PivotTable answers "totals by category" by assignment rather than formula: you place fields in rows, columns, values and filters, and the aggregation follows.
Two things are worth knowing before you use one. It works on normalized data, one row per observation and one column per field, so data laid out for reading, such as a column per month, must be reshaped first. And a pivot is a snapshot refreshed on demand, not a live formula, so a stale pivot beside changed data is a genuine and common error.
Charts turn numbers into a visual encoding, and the encoding is the decision. Position along a common axis is read most accurately; length is next; angle and area are read poorly. That is the honest case against pie charts with more than a few slices.
The most consequential choice is the axis baseline. A bar chart whose value axis starts above zero exaggerates differences, because bars encode value by length and truncating the axis makes length meaningless. A line chart, encoding by position, can legitimately start elsewhere.
Rule: pick the encoding for the comparison you want the reader to make, and truncate an axis only when the mark encodes position rather than length.
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.
Import problems are predictable enough to plan for. Numbers arrive as text; dates arrive in an ambiguous order; whitespace and non-printing characters ride along invisibly; separators inside quoted fields split columns unexpectedly.
The important distinction is not how you clean but whether the cleaning is repeatable.
Cleaning by hand (find and replace, split a column, retype a few cells) works and leaves no record. When the same file arrives next month the work is identical, and so is your opportunity to do it slightly differently.
A query-based transform records the steps as a sequence that re-runs against a new file. Its value is not speed the first time, since it is usually slower. Its value is that it is the same every time, and that anyone can read what was done. That is the auditability argument again, the one that favored conditional aggregation over manual filtering.
Ambiguous dates deserve specific caution: 03/04 is two different days depending on locale, and a file that imports correctly on one machine can import wrongly on another with no error at all.
Rule: if a file will arrive more than once, record the cleaning as steps rather than performing it — the second arrival is what makes that pay.
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 labeled input cells that can be changed and tested, or buried as literals where nobody can find them.
The functions are the easy part of modeling. The discipline is structural.
Separate inputs, calculations and outputs. Put assumptions in clearly labeled cells, ideally grouped, so a reviewer can see every number the model depends on without reading a single formula. A growth rate typed directly into a projection is invisible, cannot be changed in one place, and cannot be sensitivity-tested.
That structure is what makes the standard checks possible at all. Sensitivity analysis varies one assumption and observes the outcome, which is impossible when the assumption is a literal repeated in forty formulas. Scenario comparison swaps a whole set of assumptions at once.
On the functions themselves, one caution generalizes: rate and period must agree. A monthly payment needs a monthly rate and a count of months; if you mix an annual rate with a month count, you get a confident wrong number and no error. Time-value functions also use a sign convention where money out and money in take opposite signs, so getting it wrong flips the answer rather than failing.
Rule: every number a model depends on should be findable in an input cell — if a reviewer must read formulas to discover an assumption, the model cannot be checked.
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.
Most spreadsheets outlive their author's memory of them, and everything here follows from that.
Protection guards structure rather than secrecy. If you lock formula cells while leaving inputs editable, you prevent the commonest accident: someone typing a value over a formula, silently converting a calculation into a constant that will never update again.
Documentation in a spreadsheet means labeled inputs, a notes area recording where the data came from and what was assumed, and a consistent layout. It is cheap, and it is what makes a file auditable a year later.
Performance degrades from a small number of causes: whole-column references spanning a million rows, chains of volatile functions that force everything to recalculate, and formulas left covering ranges far larger than the data. A slow workbook is usually one doing far more work than it needs to.
Two collaboration hazards are worth naming. Hidden rows or sheets can carry data a reader will not know is there, and multiple copies of a file can diverge, a version-control problem that no spreadsheet feature solves.
Rule: build for the person who opens the file without you in the room — lock what should not be typed over, label what must be changed, and write down where the data came from.
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.