You've got a formula in cell F2. Maybe it's a loan payment calculation. Maybe it's a profit projection. Doesn't matter. 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.
That's a one-variable data table. Now, six rows, two columns. Small footprint. Big payoff.
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. Even so, 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.
The "one variable" part means exactly that: one input cell changes. On top of that, the formula itself doesn't move. Which means the input values don't move. Everything else stays fixed. Only the variable you designate gets swapped in, one row at a time, and the output populates beside each value.
In your case, the table lives in E3:F8. 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? Still, or empty. Cell F1? Doesn't participate in the calculation. Usually a label. Also irrelevant. 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. It works — until you need to test twenty values. Or fifty. So 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.
Data tables solve this instantly. No manual refresh. Change the test values in column E? Change the formula in F2? In real terms, recalculates. The whole table recalculates. No Power Query. No VBA. They're also dynamic. Just Excel doing what it was built for.
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. One formula. One variable. Six (or sixty) test values. Instant comparison.
How to Complete a One-Variable Data Table in Cells E3:F8
Here's the exact sequence. No shortcuts. No guesswork That's the whole idea..
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. Plus, say your input is interest rate in cell B1. Your formula (monthly payment, NPV, whatever) sits in B5 and references B1 Which is the point..
That's your model. Practically speaking, keep it clean. One input cell. One formula cell.
Step 2: Place the Formula Reference in F2
Click cell F2. Worth adding: type = then click your formula cell (B5 in this example). Press Enter Nothing fancy..
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. One per cell. No formulas here — just static numbers. For interest rates: 0.04, 0.Which means 045, 0. So naturally, 05, 0. 055, 0.Think about it: 06, 0. 065. In practice, or formatted as percentages: 4%, 4. 5%, 5%, etc. Excel handles either.
Order doesn't matter for calculation, but ascending order reads better.
Step 4: Select the Full Table Range
Highlight E2:F8. On the flip side, that's seven rows (including the header row) by two columns. Include F2. Include the test values. Include the empty results column. The whole rectangle.
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 The details matter here..
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 Simple, but easy to overlook..
Click OK.
Step 7: Done
Excel instantly fills F3:F8 with the formula results for each test value in E3:E8. The formula in F2 stays untouched. The test values in E3:E8 stay untouched. Your original model (B1, B5) stays untouched.
You now have a live sensitivity table. Which means change a test value in column E — the corresponding F cell updates. Change the formula in your model — the whole column F recalculates Worth keeping that in mind..
What If Your Test Values Run Across a Row Instead?
Then you'd use a row-oriented data table. Formula reference in E2. This leads to test values in F2:K2. Here's the thing — select E2:K3. Plus, row input cell = your model's input cell. Practically speaking, column input cell = blank. Same logic, rotated 90 degrees.
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." — 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. 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. Here's one way to look at it: selecting E3:F8 instead of E2:F8 will cause Excel to misinterpret the input ranges, often resulting in errors or blank cells It's one of those things that adds up..
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.
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.
