A spreadsheet is not a tiny database, and pretending it is causes most spreadsheet disasters. Use it as a visible calculation tool: keep raw data boring, make transformations explicit, and let summaries sit on top. Here is the working set I trust for budgets, operations, and analysis.
🎙️ Published & recorded:
Relative references move; absolute references stay put. That sentence is easy. The expensive part is noticing which input is a rule shared by every row. If tax lives in B1, lock it as $B$1. If price changes row by row in C, leave C2 relative. Press F4 while editing a reference to cycle the dollar signs.
Quantity in B2 · unit price in C2 · tax rate in F1
=B2*C2*(1+$F$1) ✓ copy down: rows move, tax does not
=B2*C2*(1+F1) ✗ copy down: F1 becomes F2, F3...
mixed references are for grids
=$A2*B$1 lock column A; lock row 1F1 to blank F2. Fix: click the first wrong cell, inspect the formula bar, change the rule cell to $F$1, then fill down and reconcile the grand total against quantity × price before tax.Use XLOOKUP instead of VLOOKUP when it exists. You name the key column and the return column directly, it can look left, and exact match is the default. Better still, decide what a missing key means instead of hiding it with a blank.
=XLOOKUP(A2, Products[SKU], Products[Price], "MISSING SKU")
#N/A when no [if_not_found] argument is supplied
Fix: 1. copy the failing SKU into the Products filter
2. compare exact characters and data types
3. add the product or correct the input
4. use "MISSING SKU", not "", so bad rows stay visible00127 as number 127. The product table stored text, so the formula returned the real error #N/A. Converting both key columns to text and restoring leading zeros fixed it. Wrapping everything in IFERROR(...,0) would have produced a plausible but false zero-dollar line.Stop copying rows into a second tab called “Final v7.” A live view should be a formula, not a monthly ritual. FILTER chooses rows; SORT orders the result. Keep the source as a proper table so new rows join automatically.
=SORT(FILTER(Orders, Orders[Status]="Late", "No late orders"), 5, -1)
returns late orders, newest/highest column 5 first
#VALUE!
FILTER ranges have different heights:
=FILTER(A2:F500, G2:G499="Late")
Fix: make both ranges end on row 500, or use table columns.#VALUE!; another hand-built workaround shifted every status by one customer. Fix the dimensions first, test one known order ID, then count the output against COUNTIF(Orders[Status],"Late").A pivot table answers “how much, grouped by what?” Put a category in Rows, a measure in Values, and an optional field in Columns or Filters. My rule: build the pivot before the chart. If the summary is nonsense, a polished chart only makes the nonsense persuasive.
Question: revenue by region and month
Rows: Region
Columns: Order date → group by Month
Values: Revenue → Sum not Count
Filter: Status ≠ Cancelled
Check: pivot grand total = SUM source Revenue after same filter$1,200 USD as text. The pivot silently chose Count and reported 184 instead of $213,400. Fix: remove currency words, convert the column to numbers, refresh the pivot, change “Summarize values by” to Sum, then reconcile its grand total to the source.A real spreadsheet date is a serial number displayed as a calendar date. Text that looks like a date is just text. That difference explains broken sorting, blank monthly pivots, and arithmetic that refuses to work. Store one date per cell, use an unambiguous import format such as ISO 2026-07-24, and apply display formatting afterward.
=A2+30 30 calendar days later
=EDATE(A2,1) same day next month
=EOMONTH(A2,0) last day of this month
=NETWORKDAYS(A2,B2) working days inclusive
#VALUE! from ="July 24, 2026"-A2
Fix: convert imported text with DATEVALUE after confirming locale.03/04/2026 as March 4; the UK office read April 3. Delivery metrics shifted by a month without any formula error. Fix: preserve the original import, parse day/month/year explicitly, display 24 Jul 2026, and spot-check dates where both first numbers are 12 or less.Most “lookup bugs” are dirty text. TRIM removes ordinary extra spaces, CLEAN removes many nonprinting characters, and SUBSTITUTE handles known intruders. Preserve the raw column. Create a separate cleaned key so the transformation can be audited.
=UPPER(TRIM(CLEAN(A2)))
non-breaking spaces copied from a web page survive TRIM:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
#N/A although "ACME-42" looks identical
Diagnose: =LEN(A2) and =UNICODE(RIGHT(A2,1))
Fix: remove the discovered character in a helper column.Northwind failed every lookup because the imported value ended with a non-breaking space. Clicking the cell showed nothing unusual. LEN reported 10 instead of 9. Fix: replace character 160, trim, verify the length, then point lookups at the cleaned key.Do not paint errors white and do not start with IFERROR. Read the message. #N/A means no match. #VALUE! means the wrong kind of input. #REF! means the formula points somewhere that no longer exists. Each has a different repair.
#N/A → inspect key, type, spaces, and source membership
#VALUE! → inspect text where a number/date is expected
#REF! → deleted/moved row, column, sheet, or closed dependency
Real broken formula after deleting column D:
=SUM(B2:C2)+#REF!
Fix: Undo if possible; otherwise identify the intended source
from a prior version, restore the reference, and test known rows.#REF! across the forecast. They replaced errors with zero to meet a deadline, understating costs. Correct recovery: stop editing, restore the previous file version, compare formulas, reinstate the rate column or named range, then add a control total in both currencies.Modern formulas return many cells from one formula. That is a spilled array. The top-left cell owns the result; the surrounding cells must stay empty. Do not type into the middle of it. Refer to the whole result with the spill operator, such as J2#.
=UNIQUE(Orders[Region]) one formula, many rows
=SORT(UNIQUE(Orders[Region]))
#SPILL! "There's already data in E7."
Fix: select the warning → Select Obstructing Cells → move or
clear those cells; unmerge cells; place formula outside a table.
Never clear blindly: inspect the obstruction first.#SPILL! because cell E37 contained a single apostrophe left from a manual note. Delete appeared to do nothing from the top. Fix: use “Select Obstructing Cells,” inspect E37, clear its contents, and protect the spill output area from manual entry.I am firmly against twelve-layer nested IF formulas. They are code with no names, no tests, and brackets as camouflage. If business rules form a mapping, put them in a table and use XLOOKUP. If rules are ranges, store thresholds in ascending order and use an approximate match. Rules belong where another person can read them.
=IF(A2<10,"Tiny",IF(A2<25,"Small",IF(A2<50,"Medium",
IF(A2<100,"Large",IF(... twelve levels ...)))))
Threshold table:
Minimum | Band
0 | Tiny
10 | Small
25 | Medium
50 | Large
=XLOOKUP(A2, Bands[Minimum], Bands[Band],, -1)
Now changing a rule means editing a row, not surgery.A dependable workbook is boring to inherit. Raw inputs are untouched. Rules live in named cells or tables. Errors remain visible until resolved. A summary reconciles to the source. Before sending, use this checklist.
□ one rectangular raw-data table; one field per column
□ stable IDs, not names, used as lookup keys
□ shared rule cells locked with $ or named
□ dates are real dates; amounts are real numbers
□ XLOOKUP missing cases say "MISSING", never silent zero
□ pivots refreshed and grand totals reconciled
□ #N/A, #VALUE!, #REF!, #SPILL! investigated
□ boundary cases tested; 12-level IF replaced by a table
□ a clean copy opens correctly on another machine