You've got a formula in cell F2. Maybe it's a loan payment calculation. Maybe it's a profit projection. Day to day, doesn't matter. Day to day, what matters is you need to see how that result changes when one input — just one — shifts across a range of values. And you need it laid out clean in cells E3 through F8 Small thing, real impact. That alone is useful..
That's a one-variable data table. Six rows, two columns. Small footprint. Big payoff And that's really what it comes down to..
Most people either overcomplicate it or skip it entirely, defaulting to manual copy-paste or dragging formulas down and wondering why the references break. There's a better way. Plus, built right into Excel. Takes about thirty seconds once you know the pattern.
What Is a One-Variable Data Table
A one-variable data table is Excel's native What-If Analysis tool for testing how a single input change ripples through a formula. You feed it a column of test values. It spits out the corresponding formula results — automatically, all at once, no dragging required.
Counterintuitive, but true.
The "one variable" part means exactly that: one input cell changes. On top of that, everything else stays fixed. The formula itself doesn't move. The input values don't move. Only the variable you designate gets swapped in, one row at a time, and the output populates beside each value Worth keeping that in mind..
In your case, the table lives in E3:F8. Plus, that's six test values in column E (E3 through E8) and six calculated results in column F (F3 through F8). The formula being tested sits just above the table — typically in F2 — and references the input cell you'll vary.
The Anatomy of the Range
Let's visualize it:
- E3:E8 — your test values (the variable inputs)
- F2 — the formula that uses the variable input
- F3:F8 — where Excel writes the results
Cell E2? Usually a label. Still, cell F1? And doesn't participate in the calculation. Also irrelevant. Or empty. The engine only cares about three things: the formula cell, the column of input values, and which cell in your model represents the variable.
Why It Matters / Why People Care
You could just type six different values into your input cell, jot down each result, repeat. People do it all the time. So it works — until you need to test twenty values. Think about it: or fifty. Still, or when your boss asks "what if we change the growth assumption from 3% to 7% in half-point increments? " and you're already late for a meeting.
Real talk — this step gets skipped all the time.
Data tables solve this instantly. They're also dynamic. The whole table recalculates. No Power Query. No manual refresh. Recalculates. Change the test values in column E? Even so, change the formula in F2? No VBA. Just Excel doing what it was built for No workaround needed..
Real-World Scenarios
- Loan modeling: Test monthly payments across interest rates from 4% to 9% in 0.5% steps
- Break-even analysis: See profit at unit volumes from 500 to 5,000
- Sensitivity checks: How does NPV shift when discount rate moves 8% → 14%?
- Pricing strategy: Revenue outcomes at price points $19, $24, $29, $34, $39, $44
In every case, the structure is identical. Also, one variable. Practically speaking, one formula. That said, six (or sixty) test values. Instant comparison Surprisingly effective..
How to Complete a One-Variable Data Table in Cells E3:F8
Here's the exact sequence. No shortcuts. No guesswork.
Step 1: Set Up Your Model
Somewhere on the sheet — doesn't have to be near E3:F8 — build the formula that depends on one key input. Say your input is interest rate in cell B1. Your formula (monthly payment, NPV, whatever) sits in B5 and references B1.
That's your model. That said, one input cell. Keep it clean. One formula cell.
Step 2: Place the Formula Reference in F2
Click cell F2. Type = then click your formula cell (B5 in this example). Press Enter Worth keeping that in mind..
F2 now shows the current result of your formula — calculated with whatever value is currently in B1. This is critical: F2 must be a direct reference to the formula cell, not a copy of the formula itself. If you retype the formula in F2, the data table won't know which input to vary.
Step 3: Enter Test Values in E3:E8
In E3 through E8, type the six values you want to test. Consider this: 04, 0. Also, 065. Practically speaking, 06, 0. Or formatted as percentages: 4%, 4.In real terms, 055, 0. No formulas here — just static numbers. 5%, 5%, etc. One per cell. Because of that, 05, 0. Because of that, for interest rates: 0. 045, 0.Excel handles either.
It sounds simple, but the gap is usually here.
Order doesn't matter for calculation, but ascending order reads better And that's really what it comes down to..
Step 4: Select the Full Table Range
Highlight E2:F8. Include the test values. Here's the thing — that's seven rows (including the header row) by two columns. Include the empty results column. Include F2. The whole rectangle The details matter here..
Step 5: Open Data Table Dialog
Go to Data → What-If Analysis → Data Table (in the Forecast group on the Data tab).
A tiny dialog appears with two fields: Row input cell and Column input cell.
Step 6: Specify the Column Input Cell
Since your test values run down column E, this is a column-oriented data table. Leave "Row input cell" blank. Click in "Column input cell" and select your model's input cell — B1 in our example Took long enough..
Click OK.
Step 7: Done
Excel instantly fills F3:F8 with the formula results for each test value in E3:E8. Day to day, the test values in E3:E8 stay untouched. Plus, the formula in F2 stays untouched. Your original model (B1, B5) stays untouched Simple as that..
You now have a live sensitivity table. Change a test value in column E — the corresponding F cell updates. Change the formula in your model — the whole column F recalculates It's one of those things that adds up..
What If Your Test Values Run Across a Row Instead?
Then you'd use a row-oriented data table. Formula reference in E2. And column input cell = blank. Row input cell = your model's input cell. Select E2:K3. But test values in F2:K2. Same logic, rotated 90 degrees That alone is useful..
But for E3:F8 specifically — column orientation is what you need.
Common Mistakes / What Most People Get Wrong
Mistake 1: Putting the Formula Inside the Table
People often type the actual formula into F2 instead of referencing the model cell. `=PMT(B1/12
etc.Because of that, " — which is exactly what we want to avoid. The magic of the data table lies in its ability to reference the formula indirectly through a single cell. On the flip side, if you retype the formula in F2, the table will treat it as a static value, and changing B1 won’t update the results. Always ensure F2 contains only =B5, not the full formula.
Mistake 2: Forgetting the Header Row
The table’s first row (E2:F2) must include the formula reference (F2) and a blank header (E2). Omitting this row or misaligning the selection will break the table’s structure. Take this: selecting E3:F8 instead of E2:F8 will cause Excel to misinterpret the input ranges, often resulting in errors or blank cells.
Mistake 3: Using Absolute References in Test Values
Test values in E3:E8 must be static numbers (e.g., 4%, 0.05). If you accidentally reference cells (e.g., =E3), the table will fail because it expects fixed inputs, not dynamic ones. Double-check that E3:E8 contains no formulas Most people skip this — try not to..
A Note on Flexibility
Data tables are remarkably adaptable. To test a different input (e.g., loan term instead of interest rate), simply update your model’s input cell (B1) to reference the new variable and adjust the test values in E3:E8. The table recalculates instantly. Similarly, changing the formula in B5 (e.g., swapping PMT for FV) updates all results without altering the table’s structure.
Conclusion
Data tables are a powerful tool for exploring financial scenarios, but their utility hinges on precise setup. By anchoring the formula to a single cell (B5), using static test inputs (E3:E8), and maintaining the correct table range (E2:F8), you create a dynamic, error-resistant model. Avoid common pitfalls like embedding formulas directly into the table or misaligning ranges. Once mastered, this technique transforms static spreadsheets into interactive decision-making tools, letting you stress-test assumptions and visualize outcomes with minimal effort. Whether analyzing loans, investments, or budgets, a well-constructed data table is indispensable for financial clarity Nothing fancy..
