Faragiri

Learn Formulas Excel

A solar installer in Quimper debugs a quoting sheet: building a formula in stages, what each error value means, and the key that evaluates a fragment.

6Sections
4Min read
4Depth

Corentin quotes solar installations in Quimper. His pricing formula was one line, 214 characters long, and when it started returning #VALUE! nobody could tell which part of it had failed.

Rebuilt as five short formulas in five cells, the failure was visible in eleven seconds: the roof area was arriving from another sheet as text.

Build it in stages, then combine if you must

One operation per cell, each with a heading, each checkable by eye. Panel count in one cell. Panel cost in the next. Labour hours in the next. Then a total.

Modular formula design

Four intermediate stages that make complex pricing formulas checkable

Splitting a 214-character pricing formula into separate labeled cells reveals calculation faults in seconds.

  1. 01Isolate panel quantityPlace panel count calculation in a dedicated cell with a descriptive header
  2. 02Compute panel costCalculate hardware expenditure separately so unit pricing is independently verifiable
  3. 03Calculate labour hoursCompute installation time in an isolated cell before merging with equipment costs
  4. 04Sum total pricingCombine verified sub-totals into the final quote, checkable at every stage

Intermediate cells allow instant visual verification; if a single formula is required, assemble it only after verifying each component.

This is not a beginner's compromise. A formula you cannot check is a formula you are trusting, and the intermediate cells are what make a wrong number findable rather than merely visible.

If it must end up as one formula, write it in stages first and combine it afterwards, when you already know each part works.

The key that answers a fragment

Inside a formula, select part of it in the formula bar and press F9. Excel replaces the selection with what it evaluates to, right there.

Formula debugging

Rereading formula syntax versus live fragment evaluation with F9

Rereading a formula tests your mental model of the calculation; pressing F9 tests what Excel actually sees.

Rereading formula textTesting mental assumptions
Evaluating with F9Testing live calculation
Diagnostic mechanismAssumes underlying cells contain expected numeric typesReplaces selected expression with its actual calculated result
Type error detectionFails to distinguish between number 48 and text string "48"Displays quotation marks around text numbers immediately
Exit safetyPassive reading carries no modification riskPress Escape to restore formula; Enter commits the evaluated value

Selecting a reference in the formula bar and pressing F9 provides immediate diagnosis of type mismatches without modifying the sheet.

Corentin selected the roof area reference and saw "48" in quotation marks, which is the whole diagnosis: quotation marks around a number mean text. Escape leaves the formula untouched; Enter would commit the substitution, which is the one thing to be careful about.

This single key does more for debugging than any amount of rereading, because rereading tests your model of the formula and F9 tests the formula.

What each error means

#VALUE! is a type mismatch: arithmetic attempted on something that is not a number, which is nearly always text arriving from an import.

One quoting sheet

Seven things Excel says, and what each of them is actually reporting

Six of these are errors and one is a column width. The last branch is the dangerous one, because it returns a number instead of a complaint.

  • #VALUE!Arithmetic on something that is not a number
    • text from an import
    • a date stored as text
  • #NAME?A word Excel does not recognise
    • a misspelled function
    • text without quotation marks
  • #N/AA lookup found nothing, which is sometimes correct
    • a stray space in the key
    • a number matched against text
  • No error, wrong answerThe class that costs money
    • a range that stopped growing
    • IFERROR hiding a real fault
why it broke
  • #REF!A row or column was deleted and the address is gone
    • cannot be recalculated back
    • find it before saving
  • #DIV/0!Division by zero, and an empty cell counts as zero
    • appears in sheets with no zeros in them
  • #NUM! and #####An impossible number, and a column too narrow
    • the second is not an error

Six branches announce themselves and the seventh does not, which is why a sheet that shows no errors is not evidence of a sheet that is right.

#REF! is a reference to a cell that no longer exists, and it means somebody deleted a row or a column. It cannot be repaired by recalculating because the address is gone.

#NAME? is a name Excel does not recognise: a misspelled function, or text typed without quotation marks.

#DIV/0! is division by zero or by an empty cell, and an empty cell counts, which is why it appears in a sheet with no zeros in it.

#N/A means a lookup found nothing. It is the only error that is frequently correct, because sometimes there genuinely is no match.

#NUM! is a number the operation cannot produce, such as a root of a negative. And ##### is not an error at all: the column is too narrow.

The three failures that are not errors

Worse than errors, because they return a number.

Plausible wrong numbers

The visible calculation total and the three silent defects beneath

Failures that return plausible numbers are far more dangerous than explicit errors because they escape notice.

  • Plausible numeric totalFormulas compute without producing error codes or warnings
Sheet surface output
  • Stagnant range coordinates=SUM(B2:B41) ignores appended row 41 when data expands
  • Stray whitespace in keys"Nantes " fails to match clean text, dropping expected values from totals
  • Blanket IFERROR suppressionWrapping calculations in IFERROR turns legitimate faults into silent zeros

Silent errors cost money because they masquerade as correct results rather than alerting the spreadsheet author.

A range that stopped growing. =SUM(B2:B41) written when there were 40 rows still says B2:B41 after the 41st arrives, and it is off by exactly one row that nobody sees.

A stray space. "Nantes " and "Nantes" are different values, so a lookup fails and a total is short by whatever that row held.

And IFERROR wrapped around everything. It replaces an error with a blank or a zero, which is a decision to stop being told about problems, and Corentin's sheet had eleven of them hiding two real faults.

Where the wrongness comes from

Almost never the arithmetic. Almost always the inputs: text where a number was expected, a range that no longer matches the data, or a lookup key that matches nothing because of a space or a different type.

Diagnostic focus

Formula arithmetic versus corrupted input data

Calculation breakdowns almost never stem from mathematical logic; they stem from bad input types and shifting ranges.

  • Formula arithmeticRarely the failure point
    • Mathematical operators execute faithfully
    • Parenthesis hierarchy follows exact rules
    • Algebraic errors are readily spotted
  • Input corruptionAlmost always the culprit
    • Numbers formatted or imported as text strings
    • Data ranges that no longer cover all rows
    • Lookup keys mismatched by invisible trailing spaces

Root cause diagnosis

Running =ISNUMBER() on inputs and pressing Ctrl+Down to verify range boundaries detects most spreadsheet bugs in under a minute.

Which is why the first move on a broken sheet is not to reread the formula. It is =ISNUMBER() on the inputs and Ctrl and down-arrow on the ranges, and the two of them together find most faults in under a minute.

What to build them with, as of 2026

F9 inside the formula bar, in every version of Excel, which replaced adding temporary cells to see what a fragment returns.

Formula inspection stack

Four tools for building, evaluating, and maintaining formulas

Modern functions like LET document calculation stages internally, eliminating unreadable monolithic formulas.

Formula inspection
  • F9 fragment evaluationTests isolated expressions directly inside the formula bar in all versions
  • Evaluate Formula dialogSteps through nested calculations sequentially to audit inherited formulas
  • LET scoped variablesNames intermediate results inside a single formula since Excel 2021
  • AI formula diagnosisIdentifies syntax errors and missing arguments quickly from error descriptions

Modern workbook standards

AI assistants resolve formula syntax and error codes rapidly, but auditing whether cell ranges match underlying sheet data remains a human responsibility.

Formulas, then Evaluate Formula, which steps through a nested formula one operation at a time and shows the intermediate results. Slower than F9 and better for a formula somebody else wrote.

LET, available in Microsoft 365 and Excel 2021, which names intermediate results inside a single formula, so a long formula can carry its own stages instead of hiding them. It replaced the choice between one unreadable formula and five helper cells.

A model given a formula and its error will usually name the cause immediately, free on every major assistant, and it is genuinely fast on syntax and type problems. It cannot see the sheet, so it cannot tell whether a range still matches the data, and that is the fault class that returns a plausible wrong number rather than an error.

The reference forms every one of these formulas is built from are cells, references and the interface, and the #N/A that is often correct comes from VLOOKUP, XLOOKUP and INDEX MATCH. Fixing the imported text that caused the #VALUE! is text, dates and the imported mess, under the most used analysis tool in the world.

Alongside this

Above this