What-if analysis in Excel, on live warehouse data

Excel is where analysts go to ask “what if?” — what if we cut the price, what if that region grows 10%, what price gets us to target margin. The tools for it are excellent and have been for years. The catch has always been the data underneath: run a scenario and you’re testing it against whatever snapshot you last pasted in. This guide covers Excel’s what-if toolbox — Data Tables, Goal Seek, Scenario Manager — and how to run all of it on live, governed data instead of a stale export.

TL;DR

• Excel has three built-in what-if tools: Data Tables (see how one or two inputs change a result across many values), Goal Seek (find the input that hits a target), and Scenario Manager (save and compare named sets of inputs). Solver adds constrained optimisation.
• They all share one weakness: they run on whatever data is in the sheet — usually an export that’s already stale. Every scenario on fresh data means another export and paste.
• The fix is to feed the same tools live, governed data. With the Astrato Excel Add-in, the base your scenarios build on refreshes on demand, and you can wire an assumption to a cell so changing it re-queries the warehouse — no new export.
• You keep Excel’s entire what-if toolbox; you just point it at current data that ties to your dashboards.
• Personal, ad-hoc what-if belongs in Excel. Shared, collaborative planning with saved-and-committed inputs belongs in a governed data app.

Excel’s built-in what-if tools, quickly

If you searched “how to run what-if analysis in Excel,” here’s the toolbox, all under Data tab → What-If Analysis:

Data Table — the workhorse for sensitivity analysis. You give it a range of possible input values and a formula, and it shows the result for every value at once. A one-variable data table varies a single input (say, growth rate) down a column and reads results back; a two-variable data table varies two inputs — one down the column input cell, one across the row input cell — and fills a grid with every combination. It’s how you see, in one view, how the answer moves across a whole range of assumptions.

Goal Seek — the reverse question. Instead of “what result does this input give?”, it asks “what input gives this result?” Set a target value for a formula cell, tell Goal Seek which changing cell to adjust, and it finds the input that hits the target — the classic “what price gets us to a 40% margin?” or “what volume breaks even?”.

Scenario Manager — for comparing named sets of inputs. Save several scenarios (best case, base case, worst case), each a different combination of changing-cell values, switch between them, and generate a scenario summary report that lays the outcomes side by side.

Solver — an add-in for optimisation: find the input values that maximise or minimise a target cell subject to constraints, changing several variables at once. Overkill for a quick sensitivity check, invaluable for advanced business models.

These are genuinely good tools. The problem was never the analysis — it’s the data the analysis runs on.

The catch: your scenarios are only as fresh as your last paste

Every one of those tools operates on the values already sitting in the worksheet. And in most models, those values arrived as an export — a query run against the warehouse or the ERP, dropped into the sheet, and frozen in place. 

So the moment you want to run a scenario against current actuals, you’re back to the same loop: export again, clean it, paste it, and rebuild whatever the new row count broke.

Picture the pattern concretely. Last quarter you built a pricing model with a two-variable data table sweeping price against volume — a genuinely useful piece of analysis. 

This quarter, leadership asks the same question on current numbers. The model is still there, but the actuals behind it are three months old, so before you can answer you export again, reconcile the columns, and hope nothing in the data table’s input cells broke when the ranges shifted. 

The analysis you already built is held hostage by the data feeding it.

That has two costs. The obvious one is time — every fresh-data scenario starts with data logistics instead of analysis. 

The subtler one is trust: a scenario built on last month’s snapshot can quietly disagree with the live dashboard everyone else is looking at, so the conclusion is suspect before anyone acts on it. 

The what-if is only ever as good as the base it’s built on, and the base has always been stale.

Two kinds of what-if — and why the base matters for both

It helps to separate two things people mean by “what-if”, because live data helps each in a different way.

The first is substitution. You hold the data fixed and vary an input inside a formula — a growth rate, a discount, a price. This is what Data Tables and Goal Seek do, and the scenario logic is sound. The risk is only that the fixed data underneath is a stale export, so the whole sensitivity grid comes out precise and out of date at the same time.

The second is query-driven. You change an input that pulls a different slice of the data itself — a region, a product line, a period. Native Excel can’t do this alone: changing that kind of input means going back to the source and exporting again. It’s the flavour a live connection unlocks, because the input can drive the query rather than a re-export.

Most real analysis mixes the two — you switch to a region (query-driven) and then sweep a price across a range (substitution). Both are only as trustworthy as the data beneath them, which is why the base being live and governed matters no matter which kind of what-if you’re running.

Feed the same tools live, governed data

The fix isn’t a new modelling tool — you already have the right ones. It’s to change what they run on. The Astrato Excel Add-in brings governed data into Excel as native formulas, so the base your what-if analysis sits on is live and refreshable, and defined the same way as your dashboards.

Two things change as a result.

Your scenarios run on current data. Build your one- or two-variable Data Table, your Goal Seek, your Scenario Manager cases exactly as you always have — but on a dataset that refreshes on demand. Hit Refresh and the base updates; your what-if recomputes against today’s numbers instead of a three-week-old paste.

You can drive the query itself from a cell. This is the piece Excel’s native tools can’t do on their own. Wire an assumption — a region, a segment, a period — to a cell, and changing that cell re-queries the warehouse underneath. The data itself moves, not just a formula input. So “what does this look like for EMEA instead of the Americas?” is a cell edit, not a new export.

Together, that covers both flavours of what-if: substitution (Data Table, Goal Seek — vary an input inside a formula) and query-driven (change an input that pulls a different, live slice of the data). One runs on a governed base; the other changes the base live.

How to build a live what-if model

The workflow, end to end, assuming your data team has connected Astrato to your warehouse once:

1. Pull your base data in as a dataset. From the Astrato panel, build a dataset from governed measures and dimensions and pull it into the sheet as a native table. This is the live base your model sits on.
2. Wire your assumptions to cells. For inputs that should change the data — region, product line, period — link a filter to a worksheet cell (a variable). For inputs that should change a calculation — a growth rate, a discount — just reference a cell as you always would.
3. Build the what-if on top. Add your Data Table for the sensitivity grid, Goal Seek for the target-finding, or Scenario Manager for named cases. They work normally, because the data underneath is ordinary Excel formulas.
4. Change an assumption and see the result. Type a new value in the input cell. If it drives a filter, the data re-queries live; if it drives a calculation, the model recalculates. Either way, no export.
5. Refresh when the actuals move. Press Refresh to pull current data, and every scenario re-evaluates against it.

A worked example: a pricing scenario

Say you’re pricing a subscription and want to know how a change plays out. Your base — current revenue, active accounts, margin by region — comes in as a governed dataset. You wire two inputs to cells: a monthly price and a region.

Change the price from €429 to €379 in the input cell, and your margin and rank formulas recompute instantly against the live base. Switch the region cell from Americas to EMEA, and the underlying data re-queries — now you’re looking at the EMEA slice, live, without touching an export. 

Drop a two-variable Data Table underneath to see margin across a grid of prices and volumes at once, or point Goal Seek at the margin cell to find the exact price that hits your target. 

Save the best, base and worst cases in Scenario Manager and generate a summary report that lays them side by side. The whole exploration runs on current, governed data — and because the base ties to your dashboards, the conclusion holds up when you present it. 

Next month, when the actuals have moved, you don’t rebuild the model: you press Refresh and every scenario re-evaluates against the new numbers.

You keep Excel’s whole what-if toolbox

Nothing here replaces the tools you know. Because the data lands as native Excel formulas, Data Tables, Goal Seek, Scenario Manager and Solver all behave exactly as they do in Microsoft’s documentation — the same dialogs, the same column and row input cells, the same scenario summary report. 

You’re not learning a new way to model; you’re removing the manual export from the one you already use, and giving the model a base it can trust. And because a refresh updates that base in place, the model you build once keeps earning its keep every cycle, instead of being rebuilt from a fresh export each time the question comes back.

Which tool for which question

A quick decision guide:

• “What input gives me this exact result?” → Goal Seek. One target, one changing cell.
• “How does the result move across a range of inputs?” → a one- or two-variable Data Table. Sensitivity analysis at a glance.
• “How do a few named cases compare?” → Scenario Manager, with a summary report.
• “What’s the optimal mix under constraints?” → Solver.
• “What if the underlying data itself were different — this region, this period?” → a live query driven from a cell. This is the one Excel can’t do alone, and where a governed connection earns its place.

The boundary: where what-if lives

Personal, ad-hoc what-if — an analyst exploring a question in their own model — is exactly what Excel is for, and this makes it faster and more trustworthy. When scenario planning becomes a shared process, though — many people entering assumptions that get saved, compared, and committed as the plan of record — that’s a different job. 

Collaborative, governed planning with write-back belongs in an Astrato Data App, where the inputs are audited and shared. Use Excel for the exploration; use a data app for the process. 

The two hand off cleanly — an analyst can explore freely in Excel, then take the assumptions that survived scrutiny into a governed app when they need to become the plan of record.

Common mistakes

• Don’t run scenarios on a stale base. If your model’s inputs came from an export, refresh them from live data first — a beautiful sensitivity table on old actuals is precise and wrong.
• Separate assumptions from data. Keep your changing cells in a clearly labelled inputs block, referenced by your formulas and Data Tables, so a scenario is one edit rather than ten.
• Use the right tool. Reaching for a Data Table when you want target-finding (Goal Seek) — or vice versa — is the most common what-if mis-step.
• Keep metric definitions in the model. Let the governed measure define “margin” or “net revenue” so your scenario output ties to the dashboards instead of drifting.
• Know when to graduate to a data app. If several people need to enter and save assumptions, that’s a workflow, not a spreadsheet.

Frequently asked questions

How do I run what-if analysis in Excel on live data? 

Bring your base data in as a refreshable, governed dataset with the Astrato Excel Add-in, then build your Data Table, Goal Seek or Scenario Manager on top as normal. Refresh pulls current data, and you can wire an assumption to a cell so changing it re-queries the warehouse — no export.

What’s the difference between a Data Table, Goal Seek and Scenario Manager? 

A Data Table shows how a result changes across a range of one or two input values. Goal Seek works backwards to find the input that produces a target result. Scenario Manager saves named sets of inputs so you can compare cases and generate a summary report. They solve different questions; all three work on a governed, live base.

Can I change an assumption without exporting new data each time? 

Yes. That’s the core of it. Reference a cell for calculation inputs, or wire a cell to a filter for data inputs — changing the cell recalculates or re-queries live, so a new scenario is a keystroke rather than a fresh export.

Do one-variable and two-variable data tables still work? 

Yes. The data arrives as ordinary Excel formulas, so one-variable (single column or row input cell) and two-variable data tables behave exactly as in Excel’s built-in feature — just on a live, governed base.

Will my scenario numbers match the dashboards? 

Yes, when your base is a governed dataset. The measures come from the same semantic layer as your dashboards, so the base your scenario builds on is the same figure everyone else sees.

Does Solver and sensitivity analysis still work? 

Yes. Solver runs as normal for constrained optimisation, and any sensitivity analysis you’d build with a Data Table works the same way — the only change is that the numbers it optimises or sweeps across come from a live, governed base rather than a static paste.

Is this for collaborative budgeting too? 

Not from Excel. Individual, ad-hoc what-if is Excel’s job. When assumptions need to be entered, saved and shared by a team as the committed plan, that’s a governed data app — writeback from Excel is on the way.

Next steps

If every fresh-data scenario in your model starts with another export, the fix is to give the model a live base.

• See how governed data lands in Excel on the Excel Add-in product page.
• For the wider picture, read Live, governed data in Excel.
• Book a walkthrough and we’ll wire up a live what-if model on your own data.