skip to content

How do you decide which parts of a team's Excel reporting to replace with a BI tool?

level: principalimportance: should knowfreq 30%

answer

  1. one file is doing two different jobs
  2. cadence and audience decide most cases
  3. some numbers are typed by a human on purpose
  4. the formulas may be the only definition
  5. taking away export backfires

basics

~20 s

Replace what repeats on a schedule, is shared across teams, or carries a definition others depend on. Leave ad-hoc exploration, what-if modelling and human judgment inputs in Excel, give those inputs a governed home, and never remove the export path.

solid answer

~50 s

One workbook usually does two different jobs, and only one of them should move. The **reporting** job — the same numbers, the same cut, every month, read by people beyond the author — belongs in a governed tool: scheduled refresh, one definition, access control, an audit trail. The **thinking** job — ad-hoc exploration, a scenario model built for one decision, a layout nobody else will ever read — is what a spreadsheet is genuinely good at, and rebuilding it in a BI tool makes it worse. Two things decide the hard cases. First, **definitional ownership**: if the workbook's formulas are the only place a KPI is defined, extract and publish the rule before retiring the file, or you delete the definition along with it. Second, **judgment inputs**: manual accruals and forecast overrides are real business data, not sloppiness — they need a home, an owner and an audit trail, not deletion. Keep export to Excel available; removing it is the fastest way to grow shadow copies.

code

text · 6 lines
text
-- per output in the workbook, score and route
                     recurring?  shared?  only definition?  human input?
monthly revenue pack     yes       yes         yes              no    -> platform
Q3 pricing scenario      no        no          no               yes   -> stays in Excel
accrual entry template   yes       yes         no               yes   -> governed input + platform
ad-hoc churn dig         no        no          no               no    -> stays in Excel

go deeper

for a junior

Know that not every spreadsheet should become a dashboard, and that recurring shared reports are the candidates while one-off exploration is not. Recognising that Excel remains legitimate for ad-hoc work is the point.

for a middle

Explain the axes that decide a case — cadence, audience, whether the workbook holds the only definition of a rule — and why manual inputs need a designed home rather than deletion.

for a senior

Show you would migrate output by output, extract undocumented rules first, reconcile before cutover, and keep a sanctioned live connection from Excel to the governed model so analysts keep their last mile.

for a principal

Own the tradeoff explicitly: governance bought with a slower time-to-answer is a business decision, not a technical one. Be ready to say what you would leave ungoverned on purpose and how you would know the balance had tipped.

## One file, two jobs The mistake in these migrations is treating the workbook as a single artefact. Nearly every long-lived spreadsheet is doing two unrelated things: - **Reporting** — producing the same figures, on the same cadence, for an audience that is not the author. - **Thinking** — exploring, modelling a one-off scenario, recording a judgment, laying something out for a specific conversation. The first belongs in a platform. The second belongs in a spreadsheet, and moving it makes people slower without making anything more correct. A migration plan that does not distinguish them will either fail (too ambitious) or leave the real problem untouched (too timid). ## What the platform is genuinely better at Scheduled unattended refresh with failure alerting. One definition of a measure serving every consumer. Access control, including per-user row restrictions. An audit trail of who changed the logic and when. Scale beyond a laptop. Distribution that does not involve emailing a file. Those are precisely the properties a monthly pack read by four departments needs, and precisely the ones a spreadsheet cannot provide no matter how carefully it is built. ## What Excel is genuinely better at Also a real list, and a candidate who cannot produce it will propose a rollout that fails: freeform layout; arbitrary what-if without asking anyone; manual overrides in the middle of a calculation; annotation next to a number; working offline; being installed everywhere and known by everyone. Finance in particular does work that is *supposed* to include human judgment, and a tool with no cell to type into cannot host it. ## The decision axes For each output in the workbook, ask: 1. **Cadence** — recurring on a schedule, or one-off? Recurring pulls toward the platform. 2. **Audience** — does anyone but the author read it? Shared pulls toward the platform. 3. **Definitional ownership** — is this the only place the rule is written down? If yes, the rule must be extracted and published *before* the file is retired, whichever tool ends up hosting it. 4. **Judgment inputs** — does a human type numbers into the middle of it? Those inputs need a designed home: an input table with an owner, a timestamp and an audit trail, not an assumption that they will disappear. 5. **Volatility of the ask** — if the question changes weekly, a governed report will always lag the need, and the spreadsheet is honestly the right tool for now. ## Do not take away export The most reliable way to fail is to remove the Excel export so that people "have to use the dashboard". Analysts have a last mile — combining with something else, an unanticipated cut, a formatting requirement for a committee paper — and if the sanctioned tool cannot serve it they will rebuild the whole report from raw extracts, which is strictly worse than a governed export. The better pattern is a **sanctioned connection**: let Excel connect live to the governed model rather than to pasted CSVs. Power BI's Analyze in Excel is the obvious example — PivotTables over the published model, so the definitions stay central while the surface stays familiar. You get governance and they keep their tool. ## Shadow workbooks are bug reports When a replaced spreadsheet reappears a month later, resist reading it as disobedience. It is telling you what the platform could not do: a missing filter, an override the model refused to allow, a granularity nobody exposed, an export that was blocked. Fix the gap and the copy stops being necessary. Ban the copy without fixing the gap and it moves somewhere you cannot see it. ## Measuring success Not "number of workbooks retired". Better: the sanctioned report is the one people cite in meetings; refresh failures are visible and owned; the count of independent definitions of each headline metric goes down; and the time to answer a new question has not got worse. If the last one degrades, you have traded agility for control at a rate the business did not agree to — which is the actual judgment call this question is asking you to make.

  • A replaced workbook reappears a month later — how do you respond?
    Treat it as a bug report, not disobedience. It names something the sanctioned tool could not do: a missing filter, a granularity nobody exposed, an override the model refused, a blocked export. Close that gap and the copy becomes unnecessary. Ban it without closing the gap and it simply moves somewhere you cannot see it.
  • The workbook's formulas are the only written definition of a headline KPI — what now?
    Extract the rule before retiring the file. Audit the formulas, write the definition down in plain language, get the business owner to confirm it, and reconcile the new implementation against the workbook's published numbers for a full period. Only then retire the file — read-only rather than deleted, so any later challenge has evidence to check against.
  • What is a reasonable success measure for this kind of migration?
    Not workbooks retired. Better signals: the sanctioned report is the one cited in meetings, refresh failures are visible and owned, the number of independent definitions of each headline metric falls, and time-to-answer for a new question has not got worse. If that last one degrades, you traded agility for control at a rate nobody agreed to.

saying these in an interview costs you the question

  • Bans spreadsheets outright and forces everyone onto the BI tool
  • Calls Excel legacy with no place in a modern stack
  • Plans to rebuild every workbook one-for-one in the new tool
  • Removes export to Excel so people are pushed to the dashboard
  • Treats manual forecast overrides as sloppiness to be deleted

context

resolving links…