skip to content

When a business-critical Excel workbook becomes a production pipeline, what breaks first?

level: seniorimportance: should knowfreq 50%

answer

  1. it is not the row limit
  2. a workbook has no meaningful diff
  3. one hand-edited cell looks like every other
  4. the refresh steps live in someone's head
  5. performance is the last thing to hurt

basics

~20 s

Correctness fails long before performance does. With no diff, no review and no tests, hardcoded plugs and drifted formulas ship wrong numbers silently, while a manual refresh ritual known to one person becomes the real single point of failure.

solid answer

~50 s

People expect the row limit to be the wall. It almost never is. What breaks first is **verifiability**: a workbook has no meaningful diff, so a hand-edited cell — a hardcoded plug inside one `SUMIFS`, a range that stops one row short — is invisible to review and produces a plausible wrong number rather than an error. Second is the **process**: refreshing means a sequence of manual steps in a known order, held in one person's head, with no log of whether it ran or succeeded. Third is **concurrency** — the file gets copied to make a change, and now two versions of the truth exist. Performance arrives last, usually as volatile functions such as `OFFSET` and `INDIRECT` recalculating over hundreds of thousands of formulas. The migration that works is a parallel run: rebuild extraction, joins and aggregation in SQL, reconcile to the cent against the workbook for a full period, explain every remaining delta, then cut over and leave only the last-mile presentation in Excel.

code

text · 5 lines
text
-- rows 2..4317, copied down consistently
=SUMIFS(Data!$D:$D, Data!$A:$A, $A2)

-- row 4318, hand-edited two quarters ago to make it tie out
=SUMIFS(Data!$D:$D, Data!$A:$A, $A4318) + 1200

go deeper

for a junior

Recall that spreadsheets have no code review, no tests and no run log, and that a single hand-edited cell is invisible. Knowing the failure is usually silent wrongness rather than size is the point.

for a middle

Explain concrete mechanisms: formula drift down a column, hardcoded plugs inside formulas, IFERROR masking real errors, and volatile functions such as OFFSET and INDIRECT driving recalculation cost.

for a senior

Demonstrate a migration plan you have actually run: audit the workbook for undocumented rules, rebuild in SQL, parallel-run and reconcile to the cent for a full period, explain every delta, then cut over with monitoring.

for a principal

Own the organisational question — who is accountable for a business-critical file with no owner of record, what controls apply to numbers that reach the board, and how much spreadsheet autonomy you trade away for auditability.

## The order in which things fail Spreadsheets do not collapse; they erode. The order matters because it tells you what to fix first. **1. Verifiability.** A workbook is a binary blob. There is no line-by-line diff of formulas, no pull request, no reviewer. A cell can be overwritten with a constant — the notorious hardcoded plug that makes this quarter tie out — and be indistinguishable from its neighbours forever after. Version history on a shared drive tells you *that* the file changed, not *which formula* changed. This is the first and most damaging loss, because it converts every subsequent error into a silent one. **2. Formula drift.** Ranges that should be identical down a column are not: someone inserted rows and one formula's range did not extend, someone pasted a fix into a single cell, someone added `+1200` to reconcile a difference nobody investigated. Sampling three cells looks fine. The wrongness is in row 4,318. **3. Process fragility.** "Refresh" turns out to mean: download two exports, paste one into a sheet, run a macro, check a figure against last month, then send. The order matters, the steps are undocumented, and only one person knows them. There is no run log, no alerting and no notion of "the job failed" — a missed step produces last month's numbers wearing this month's date. **4. Concurrency.** Two people need it at once, so one takes a copy. Now there are two files, one of which will be edited, and neither is authoritative. Every shared spreadsheet eventually has children. **5. Error masking.** `IFERROR` is applied to make the sheet look clean, so genuine `#REF!` and `#N/A` results — the only signal the workbook ever produced — become blanks and zeros. The zeros then flow into totals. **6. Performance.** Last, and least interesting. Volatile functions (`OFFSET`, `INDIRECT`, `NOW`, `TODAY`, `RAND`) recalculate on every change; whole-column array-style formulas multiply the work; the file takes minutes to open. Annoying, but visible — and visible problems are the ones that get fixed. The grid ceiling of 1,048,576 rows is what candidates usually name, and it is genuinely the last thing to bite. By the time you hit it, the workbook has been producing unverified numbers for two years. ## Finding the logic before you replace it The workbook is frequently the **only** written definition of the business rules. Before rewriting: - List the distinct formulas per column, not per cell — the exceptions are where the rules hide. - Hunt for hardcoded constants inside formulas; each is an undocumented rule or a fudge, and you must find out which. - Trace precedents on the headline figures to find every input, including the ones pasted from email. - Interview the owner about the steps they perform that are not in the file at all. ## The migration that works Move extraction, joins and aggregation into SQL against the warehouse. Keep the last mile — layout, commentary, the manual adjustments finance genuinely needs — in Excel, connected to the modelled source rather than to pasted exports. Then **run in parallel for a full reporting period** and reconcile to the cent. Every difference is either a bug in the new pipeline or a bug in the workbook; you must be able to say which, for each one, out loud, to the person whose name is on the report. That conversation is the whole migration. Skipping it is why rewrites get rejected: the business does not trust a number that appeared without an explanation of why it differs from the number they have been using. Once reconciled, freeze the workbook read-only rather than deleting it — it is your evidence — and put monitoring on the new pipeline so failures announce themselves instead of being discovered by a stakeholder. ## What the interviewer wants Evidence you have lived it: that you name silent wrongness rather than the row limit, that you know the business logic is undocumented, and that you plan a reconciliation rather than a cutover weekend.

  • How do you migrate without the business losing trust in the numbers?
    Run both in parallel for a full reporting period and reconcile line by line. Every delta gets an explanation naming which side was wrong and why. Present the reconciliation before proposing cutover, and freeze the old workbook read-only afterwards rather than deleting it, so any later challenge can be checked against the evidence.
  • How do you extract the business logic buried in a workbook nobody documented?
    Audit formulas by column rather than by cell — list the distinct formulas and treat every exception as a hidden rule. Hunt hardcoded constants inside formulas; each is either an undocumented rule or a fudge, and you must establish which. Trace precedents on headline figures to find inputs, then interview the owner about steps performed outside the file entirely.
  • What legitimately stays in Excel after the migration?
    The last mile: layout and commentary, ad-hoc slicing of a governed result, and genuine human inputs such as accruals or forecast overrides. Those inputs need an owner, a place to be recorded and an audit trail — not deletion. Connect the workbook to the modelled source instead of to pasted exports so the numbers arriving are the governed ones.

saying these in an interview costs you the question

  • Names the million-row limit as the first thing that breaks
  • Treats shared-drive version history as equivalent to code review
  • Says the numbers must be fine because they have always matched
  • Proposes a weekend rewrite and cutover with no parallel run
  • Adds IFERROR so the errors stop appearing on the sheet

context

resolving links…