Level 6 · In practice

6.2Recurring problems (playbook)

Goal: have a « symptom → causes → actions » playbook for common Excel errors.

Estimated time: 12 min · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

Diagnose: a symptom = likely causes = a sequence of actions — you don't fix at random.
Roots: types (text vs number), spaces, deleted references, frozen source, volatile functions — 90% of blockers.
Check: numbers aligned to the right, pivot Refresh, proof on edge cases, not just on an isolated cell.

Wassim thread — the table taped next to the screen

Wassim keeps this table next to his screen in Algiers. Verdict after a quarter: three rows served him most — the #N/A of XLOOKUPs, the SUM that returns 0, and the pivot not up to date. For each, he eventually applied the sequence (symptom, causes, actions) instead of retyping at random.

Observed symptom, checked causes, actions in order: the reflex holds in three beats.

One reflex per symptom

Most Excel blockers are classics. Linking each symptom to its likely causes and a sequence of actions saves precious time.

Symptom	Likely causes	Actions
#N/A (XLOOKUP/VLOOKUP)	key not found, spaces, text vs number	TRIM; homogenize the type; 4th « if not found » argument
#REF!	deleted reference (row/column removed)	undo; rebuild the reference; prefer structured tables
#DIV/0!	division by zero or empty cell	=IF(divisor=0,"",num/divisor) or IFERROR(...)
#VALUE!	text where a number is expected	check the types; VALUE; clean the input
=SUM returns 0	numbers stored as text (left-aligned)	retype as numbers / convert (VALUE)
Formula shown as-is	cell formatted as Text, or = missing	set to General; retype starting with =
Pivot not up to date	not refreshed / frozen source	right-click ▸ Refresh; source as a structured table
Slow workbook	volatile functions, excessive CF, large volumes	limit OFFSET/INDIRECT; lighten the CF; Power Query/model
#SPILL!	spill blocked by an occupied cell	free the space below/right of the dynamic result

Common misinterpretation: « the table replaces the diagnosis ». No. The table gives likely causes, not a verdict: observe the symptom, check the cause (types, spaces, ranges, source), then apply the targeted action.

Text version of the diagram (SEO / screen readers / translation): Method of the table: 1) Name the observed symptom (#N/A, #REF!, #DIV/0!, #VALUE!, SUM at 0, formula shown, frozen pivot, slowness, #SPILL!). 2) Retain the likely causes from the table without concluding alone (spaces, text vs number, deleted reference, frozen source, volatiles, CF). 3) Apply the actions in order (TRIM, homogenize the type, IF/IFERROR, VALUE, General + =, Refresh, lighten). 4) Fix at the root (the same error coming back = a design to fix: types, ranges, structure). Lesson: an error that comes back calls for a setting, not random retyping.

Ready-to-say script — diagnose without retyping at random (30 s)

« My symptom: [#N/A / SUM at 0 / frozen pivot]. I check the likely cause from the table (spaces, text vs number, source), I apply the targeted action (TRIM, VALUE, Refresh) and I prove it on an edge case. »

Copy-paste template — Excel band-aids

=IFERROR(XLOOKUP(A2,Customers[Code],Customers[Name],"Not found"),"Not found")
=IF(B2=0,"",A2/B2)
=TRIM(A2)
=VALUE(A2)

SME / small budget

No formal table: a symptom, the likely cause, the action and who proves it, noted down.

SME Algiers

Integrate the real exports into the diagnosis: local separator, French dates, known owner.

Remote / hybrid

Add a screenshot to the diagnosis: at a distance, a written symptom and proof avoid duplicates.

Intercultural

Make the findings explicit in writing: what seems obvious (which format, which source) isn't for everyone.

Caution: Generic reminder: the same error coming back isn't ten incidents — fix the design at the root (types, named ranges, structure) and keep a copy before any mass repair.

48h challenge — apply tomorrow

Spot 1 current error (#N/A, SUM at 0, frozen pivot), apply the table's row (named symptom + checked cause + targeted action) and prove it.

Section exit checklist

I know how to link a symptom to its likely causes.
I check types and source before correcting.
I prove each correction (edge case, Refresh, total that adds up).

Self-assessment 1-5: diagnosis before correction?systematic proof after correction?

← Previous : Case studies (V3)Next : Best practices & pitfalls (V3) →

Check your understanding

Wassim's SUM returns 0 although the amounts are visible. Which reflex does the table apply?

💡

The same error coming back over and over isn't ten incidents: it's a design problem to fix at the root (types, ranges, structure).