In Excel for Microsoft 365 on Windows, create a two-variable data table by putting a formula link in the grid’s top-left cell, test values across the top and down the left, selecting the full range, then choosing Data > What-If Analysis > Data Table and assigning the row and column input cells. The examples below show how that feature works for common planning problems, how to make the results easier to read, and when a different kind of Excel table is the better choice.
1. Build a Two-Variable What-If Data Table
A two-variable data table calculates one formula repeatedly using every combination of two input values, then displays the results in a grid. Put a reference to the model’s result formula in the grid’s upper-left corner, put one set of candidate values across the top row, and put the other set in the first column; select the entire range, including the formula cell, before opening Data > What-If Analysis > Data Table. Set Row input cell to the model cell represented by the top-row values and Column input cell to the model cell represented by the first-column values, so Excel substitutes each pair and returns the calculated result at their intersection.
2. Compare Loan Payments by Rate and Term
A payment model is a clear example because the monthly payment changes when either the annual interest rate or loan term changes. With the principal in B2 as 250000, annual interest rate in B3 as 6%, and term in B4 as 30 years, put the monthly payment in B5; the assumed layout is those exact cells, and the formula is =-PMT(B3/12,B4*12,B2), which returns approximately $1,498.88. In the data table, put rates such as 5%, 6%, and 7% across the top, terms such as 15, 20, and 30 down the side, and assign B3 as the row input cell and B4 as the column input cell; this is an illustration, not financial advice, so check actual terms with a lender or qualified professional.
3. Find a Break-Even Combination of Price and Volume
For a small product model, make the output monthly profit, with price per unit as one model input and units sold as the other. The underlying calculation should include the costs that actually apply—for example, unit price multiplied by units sold, less per-unit costs and fixed monthly costs—so the grid can show where profit reaches zero rather than merely displaying sales revenue. Put candidate prices across the top and sales volumes down the side, then look for intersections near zero; this makes it easier to see whether the target depends more on a price change or on selling more units.
4. Test Selling Price Against Unit Cost
A gross-margin sensitivity grid helps when supplier costs or selling prices may change, but sales volume is not the question being tested. Use the model’s selling-price input for one axis and unit-cost input for the other, with gross profit per unit as the result; for example, comparing selling prices from $18 to $22 against costs from $9 to $12 shows which combinations preserve a chosen margin. Keep units and other assumptions fixed, and label the result clearly as profit per unit or margin percentage, because those are different outputs and should not be interpreted interchangeably.
5. Estimate Staffing Cost From Hours and Wage
A staffing grid can show total labor expense for combinations of scheduled hours and hourly pay rates. Put wage rates across the top and weekly hours down the left, then use a model result that multiplies the selected rate by the selected hours; if the result is intended to represent total employer cost, make sure the model also accounts for relevant payroll overhead rather than treating wages as the full cost. This is useful for comparing staffing plans—for instance, 120, 150, or 180 hours at $20, $22, or $24 per hour—without replacing the model’s input cells by hand for every scenario.
6. Calculate Reorder Points From Demand and Lead Time
Inventory planning can use daily demand and supplier lead time as the two inputs, with the model output set to the reorder point in units. If a simple model uses average daily demand multiplied by lead-time days, a grid comparing demand of 20, 25, and 30 units per day with lead times of 4, 6, and 8 days reveals the resulting replenishment thresholds. Treat that as a basic planning calculation, not a complete stock policy: it does not include safety stock, demand variability, minimum order quantities, or supplier delays unless those factors are added to the model.
7. Compare Project Value at Different Discount Rates and Benefits
A project model can show how a present-value result responds to two assumptions, such as the annual benefit and discount rate, while keeping the project duration and other cash flows fixed. Place the candidate benefit amounts across the top and discount rates down the side, then link the grid’s corner cell to the model’s present-value output; the table then makes sensitivity visible without changing the original model inputs permanently. This is an illustrative modeling technique, not investment or financial advice, and the answer is only as sound as the timing, cash-flow estimates, and discount-rate assumptions used in the workbook.
8. Forecast Campaign Orders From Visits and Conversion Rate
For a campaign forecast, use qualified visits and conversion rate as the two inputs and orders as the result. A grid with visits of 2,000, 3,000, and 4,000 down the side and conversion rates of 1%, 1.5%, and 2% across the top makes the assumptions behind an order forecast easy to inspect. If the business decision is about revenue or profit instead, link the result to a model that also includes average order value and campaign cost; an order count by itself does not establish whether a campaign is worthwhile.
9. Check Production Capacity Against Uptime
A capacity model can examine planned operating hours and machine uptime as inputs, with completed units as the output. For example, if the model’s rated production rate is 40 units per operating hour, comparing 6, 7, and 8 scheduled hours against 80%, 90%, and 95% uptime shows how downtime affects the day’s attainable output. Make sure the model defines uptime consistently and includes changeovers or other bottlenecks if they matter; a table based only on rated speed can otherwise overstate usable capacity.
10. Choose a Regular Excel Table When You Are Organizing Records
Excel’s What-If Analysis Data Table is a calculation tool, not the same thing as a formatted Excel Table used to store rows of records. If the goal is to keep a sales list with fields such as Date, Product, Region, and Revenue, select the range and choose Insert > Table; the resulting table has named columns, filter controls, and expanding structured references rather than scenario inputs on two axes. Use the What-If Analysis table when one formula needs results for combinations of two model inputs, and use a regular Excel Table when you need to manage, filter, or summarize a dataset.
How to Choose
Start by defining the result you want each cell to show: payment, profit, units, present value, or another single output. Then identify exactly two assumptions that feed the model and check that each has a dedicated input cell; the data table substitutes values into those cells, so values that do not affect the output formula will produce no meaningful change. Decide which assumption belongs across the top and which belongs down the left, and label both axes with units such as dollars, months, hours, or percent.
Choose a built-in Data Table when the model already works and you want Excel to recalculate its result for a manageable set of combinations. It can become slow when the grid is large or the workbook contains complex calculations, because Excel recalculates the model for each pair; for performance, reduce the number of test values or consider whether a formula-based grid, a PivotTable, or a separate scenario model better fits the job. If recalculation is slowing a workbook, Excel for Windows offers Formulas > Calculation Options > Automatic Except for Data Tables, though the table will not update automatically under that setting.
Excel for Microsoft 365 on Windows uses Data > What-If Analysis > Data Table. Excel for Mac also supports What-If Analysis Data Tables, though ribbon placement and labels can vary by build; Excel for the web does not provide the desktop What-If Analysis command for creating these tables, so open the workbook in the desktop app to build one. For a two-variable table, use both input-cell boxes; leaving one blank makes it a one-variable table instead.
Frequently Asked Questions
Why do all the results in my table look the same?
Check that the top-left cell contains a reference to the model’s result and that the row and column input cells are assigned to the correct model inputs. Also confirm that the output formula depends on those inputs; otherwise changing them cannot change the result.
Does a data table permanently change my model inputs?
No. Excel temporarily substitutes each pair of test values while calculating the grid, then restores the model’s original input values. The output cells show the scenario results, while the model’s input cells retain their existing values.
Can I test three variables in one data table?
No. Excel’s What-If Analysis Data Table supports one or two variable input cells. For a third assumption, hold it at a chosen value and run another table, or use a different modeling approach that can organize more dimensions.
Can I create a two-variable data table in Excel for the web?
The web app does not include the desktop What-If Analysis Data Table command for creating one. Open the workbook in Excel for Windows or Mac to create or edit the table.
Can I format the results as a heat map?
Yes. Select the result cells, then choose Home > Conditional Formatting > Color Scales to apply a color scale; keep the axis labels visible and use a sensible scale so color differences reflect meaningful changes.
Final Thoughts
A two-dimensional data table is most useful when a working Excel model has one clear result and two important assumptions worth comparing. Set up the output reference and axes carefully, map each axis to the correct input cell, and treat the grid as a view of the model’s assumptions—not a substitute for checking whether those assumptions are realistic.
Related
- How to Run Solver in Excel: Setup and Optimization
- K-Means Clustering in Excel: Solver and Easier Methods
- Excel Solver Two-Variable Example and Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Apps Script in Google Sheets: Where to Find ItIn Google Sheets on a computer, open your spreadsheet and choose Extensions > Apps Script. The Apps Script…
- How to Insert a Dynamic Checkbox in Microsoft WordTo insert a dynamic, clickable checkbox in Word, show the Developer tab, then choose Developer > Check Box…
- Unable to Share Calendar in Outlook 365? Try These FixesIf you can’t share a calendar in Outlook 365, first confirm you’re signed in to the account that…
- How to Create Inbox Folder Rules in OutlookTo create an Outlook inbox rule that sorts messages into a folder, open the Rules settings for your…
- How to Run Apps Script in Google Sheets: A Practical GuideTo run Apps Script in Google Sheets, open your spreadsheet, select Extensions > Apps Script, add and save…
- How to Insert a Line in Word on a Resume: Simple StepsTo insert a clean line in a Word resume, click in the paragraph where you want a divider,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.