To create a two-way data table in Excel, put one set of test values across the top row, a second set down the first column, and a formula linked to your model in the grid’s upper-left corner; select the full grid, then choose Data > What-If Analysis > Data Table. The ten points below explain setup, interpretation, troubleshooting, and alternatives for people comparing two changing assumptions in Microsoft Excel for Microsoft 365 on Windows.
1. Start with a model that has two input cells
A two-way data table varies two existing model inputs and records the resulting value of one formula, so first identify the two cells that should change and the result cell that depends on them. For a simple production model, place units per hour in B2, available hours in B3, and total units in B4; with those inputs in B2 and B3, enter =B2*B3 in B4. At 120 units per hour and 7.5 hours, the formula returns 900 units, giving you a straightforward result to compare as either assumption changes.
2. Lay out the test values in the right direction
Put the first input’s trial values horizontally across the top row of the table and the second input’s trial values vertically down its first column. For the production example, put a link to the output formula in D5, rates of 80, 100, 120, 140, and 160 in E5:I5, and hours of 4, 6, 7.5, 8, and 10 in D6:D10. The empty cells E6:I10 are where Excel will return the combinations; keep labels and units clear so a reader can tell which assumption belongs to each axis.
3. Link the table’s corner to the result
The cell at the intersection of the headings, D5 in this layout, must reference the model’s output cell rather than contain a copied result or a separate calculation. Enter =B4 in D5, then select the entire rectangle D5:I10, including the formula, both sets of assumptions, and the blank result area. Excel uses the corner formula as the answer to recalculate for every pair of input values, so a broken or hard-coded link can make the whole table misleading.
4. Assign row and column input cells carefully
With D5:I10 selected, choose Data > What-If Analysis > Data Table. In the dialog, set Row input cell to B2 because the rate values run across row 5, and set Column input cell to B3 because the hour values run down column D; then click OK. The row and column fields refer to the model’s original input cells, not to the heading cells in the table. Reversing the assignments can still produce numbers, but they will represent the wrong assumptions and may be difficult to spot without a simple check.
5. Read individual intersections as paired assumptions
Each interior cell represents the model output for the value at the top of its column paired with the value at the left of its row. In this example, G9 is at the intersection of 120 units per hour and 8 hours, so its result is 960 units; that is 120 multiplied by 8. Compare the table’s central values with the original model to confirm the mapping, and read across a row to see the effect of changing rate while holding hours fixed, or down a column to see the effect of changing hours while holding rate fixed.
6. Choose useful ranges and increments
A table is only as informative as the trial values it tests, so select ranges that reflect plausible operating conditions and increments fine enough to reveal important changes. Testing rates from 80 to 160 in steps of 20 gives a compact overview, while testing every single unit across that range creates a denser grid without necessarily adding useful insight. Include the current model assumptions among the test values when practical; that gives you a direct check that the corresponding intersection agrees with the original output.
7. Use it for sensitivity analysis, not only arithmetic
The same setup works when the output formula is more involved than a multiplication, provided it ultimately depends on both designated input cells. For example, a staffing model might calculate completed orders from workers scheduled and average orders completed per worker, while a classroom model could estimate attendance from enrollment and an attendance rate. A two-way table quickly shows which assumption has the larger effect, whether the relationship is linear, and where combinations produce an output that misses a chosen operational target.
8. Format the grid to make comparisons easier
Format the headings with units, such as “units per hour” and “hours,” and apply a consistent number format to the results so readers do not confuse whole units with percentages or decimals. You can use Home > Conditional Formatting > Color Scales to reveal high and low output regions, but keep the color scale subordinate to readable labels and a visible legend or explanation. Avoid excessive decimal places: if the model reports whole completed items, displaying values like 960.000 obscures rather than improves the comparison.
9. Check recalculation and performance
Excel recalculates data tables as model inputs or formulas change, and a large grid can take noticeably longer when each output triggers a complex calculation. If the workbook feels slow, reduce unnecessary trial combinations, simplify formulas feeding the result, or choose Formulas > Calculation Options > Automatic Except for Data Tables so Excel recalculates the table only when requested. That setting affects data tables in the workbook, so remember to recalculate when you need current results and restore the usual calculation mode if automatic updates are important to other users.
10. Troubleshoot blanks, repeated values, or wrong results
If the grid stays blank or produces unexpected results, verify that the selected range includes the top-left formula and both input lists, and check that the formula actually depends on the two input cells assigned in the dialog. Confirm the row input cell matches the horizontal values and the column input cell matches the vertical values, and make sure the input cells and data table are on the same worksheet. Recheck one intersection manually by entering its two assumptions into the model; if the model result differs from the table, investigate the formula links, cell references, and calculation settings before relying on the grid.
How to Choose
Choose a two-way data table when you already have a working formula model and want to see how one output responds to two inputs at once. It is especially useful for sensitivity analysis: you can compare combinations in a single matrix without building a separate formula for every pair. It is not the best choice when you need a large, reusable dataset, several output metrics, or a report that must update through a broader analysis workflow.
Before building one, define the decision the table should support and select just the two assumptions that matter most to that decision. Use realistic ranges, include baseline values, and label each axis with its units. If you need to compare many outputs, consider making separate tables or structuring the model so that each output can be evaluated clearly; a standard two-variable data table is designed around one formula result. For a very large set of combinations, Power Query, a PivotTable, or formulas that explicitly generate scenarios may be more manageable, depending on how the source data is organized.
Also consider compatibility and calculation cost. Create and edit the feature in desktop Excel; Excel for the web does not offer full authoring support for What-If Analysis data tables, so use the desktop app when setting up or changing one. A small table with a few dozen combinations is usually easy to interpret, whereas a very wide grid can slow recalculation and become hard to read. Choose the simplest tool that answers the question, rather than adding scenarios just because the worksheet has room.
Frequently Asked Questions
Is a two-way data table the same as a PivotTable? No. A two-way data table repeatedly substitutes two values into model input cells and records a formula’s output. A PivotTable summarizes and rearranges source records, such as sales rows grouped by product and month; it does not perform the same what-if substitution.
Can a two-way data table show more than one formula result? A standard two-variable data table is built around one output formula. If you need to compare several outputs, create separate tables with their own corner formulas or use another model design that presents those outputs together without making the grid ambiguous.
Why do my row and column input cells seem backward? The Row input cell is the model cell replaced by values across the table’s top row, while the Column input cell is replaced by values down the first column. Check the orientation of the assumptions in the selected range, then verify the assignments in Data > What-If Analysis > Data Table.
Can I edit one result cell in the finished table? Excel manages the calculated results as a data table, so an individual interior result is not an ordinary independent formula cell. Change the model, headings, or input-cell assignments and recalculate the table instead of typing over one output; doing so keeps the scenario grid internally consistent.
Can I create one in Excel for the web? Excel for the web does not provide the full desktop Data Table authoring workflow. Open the workbook in desktop Excel for Microsoft 365 to create or modify the table, and check the results there if the web version does not update them as expected.
Final Thoughts
A two-way data table is a compact way to test how two assumptions affect one formula in an existing Excel model. Build a reliable output formula first, place each set of test values on the correct axis, and confirm one result against a manual calculation. With sensible ranges and clear labels, the table turns a model into a practical comparison tool rather than a wall of unexplained numbers.
Related
- Excel Environmental Data Analysis: Methods and Examples
- Excel Solver Answer Report: Create and Read It
- How to Run Solver in Excel: Setup and Optimization
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis: Methods, Tools, and ExamplesExcel data analysis means organizing spreadsheet data, checking it for errors, and using formulas, PivotTables, charts, and other…
- Can You Use Goal Seek on Multiple Cells in Excel at Once?No. In Microsoft Excel for Microsoft 365 on Windows, Goal Seek changes one input cell to make the…
- Excel Sheet Shortcut Keys for Faster Worksheet WorkFor Excel for Microsoft 365 on Windows, the most useful sheet shortcuts are Shift+F11 to add a worksheet,…
- Can You Create a Dropdown List in OneNote?No—OneNote does not provide a native dropdown-list control that readers can open and use to choose one value…
- 0.3-Inch Hanging Indent in Word: Set It Up PreciselyTo create a 0.3-inch hanging indent in Word, select the paragraph or paragraphs, open the Paragraph dialog, choose…
- Gmail Rules: How to Create and Manage Filters Like OutlookYes. Gmail can automatically sort, label, forward, archive, delete, or otherwise handle messages using filters—the feature that plays…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.