Creating a two-variable data table in Excel is straightforward and useful for analyzing how changes in two input variables affect a formula’s result. This guide will walk you through setting up a two-variable data table, so you can make better data-driven decisions.
Step-by-Step Tutorial: How to Create a Two-Variable Data Table in Excel
In the following steps, you’ll learn how to create a two-variable data table to explore how different combinations of two variables affect your results. This is particularly helpful for financial modeling, budgeting, and various other analytics.
Step 1: Set Up Your Data
Ensure you have your main formula and variables ready and arranged properly.
Create your formula using the two variables you want to analyze. Place the formula in a cell that will remain static while you vary the inputs.
Step 2: Create a Layout for the Data Table
Design a grid where one variable is listed across the top row and the other variable is listed down the first column.
Place the first variable’s potential values in a row and the second variable’s values in a column. The cell where the row and column intersect should contain your formula.
Step 3: Select the Data Table Range
Highlight the range that includes the formula, the row of variable values, and the column of variable values.
This step is crucial. If you don’t select the entire range, Excel won’t know where to apply the table.
Step 4: Open the Data Table Dialog Box
Go to the "Data" tab, click on "What-If Analysis," and select "Data Table."
This is where you’ll tell Excel which cells to use for your variables.
Step 5: Fill in the Data Table Inputs
In the Data Table dialog box, enter the cell references for your row and column input cells.
The Row Input Cell should correspond to the variable in the top row, and the Column Input Cell should be for the variable in the left column. Click "OK."
Step 6: Review the Results
Excel will automatically fill in the table with the results based on your formula and variables.
Check the filled data table to ensure the numbers make sense. This visual representation helps you easily compare the impacts of different combinations.
Once you complete these steps, your two-variable data table will be fully functional. You’ll see how different values for the two variables affect your formula, providing valuable insights for decision-making.
Tips for Creating a Two-Variable Data Table in Excel
- Always double-check your formula and input values before creating the data table.
- Use cell references for variables instead of hardcoding numbers to make updates easier.
- Make sure your Excel calculations are set to automatic; otherwise, the data table won’t update.
- Label your rows and columns clearly to avoid confusion when interpreting results.
- If your table doesn’t work, ensure that the input cells are correctly referenced in the Data Table dialog box.
Worked Example: Loan Payment by Interest Rate and Term
A two-variable data table is easiest to understand with a real formula. Suppose you want to see the monthly payment on a $250,000 loan at several interest rates and loan terms.
- Enter the inputs: B1 = 250000 (loan amount), B2 = 6% (annual rate), B3 = 30 (years).
- In D5, enter the formula
=PMT(B2/12,B3*12,-B1). This cell is the top-left corner of the data table. - Type the interest rates down the column below the formula: D6:D10 = 5%, 5.5%, 6%, 6.5%, 7%.
- Type the loan terms across the row to the right of the formula: E5:G5 = 15, 20, 30.
- Select D5:G10, then go to Data > What-If Analysis > Data Table.
- In Row input cell, enter B3 (the terms run across the row). In Column input cell, enter B2 (the rates run down the column). Click OK.
Excel fills E6:G10 with the monthly payment for every rate and term combination. The result cells contain the array formula {=TABLE(B3,B2)}, which is how Excel stores a data table. To hide the formula value in D5 so the table reads cleanly, apply a custom number format of ;;; to that cell or change its font color to match the background.
Troubleshooting a Two-Variable Data Table
Every result cell shows the same number
The row and column input cells are swapped or point to cells the formula does not use. The Row input cell must be the cell that the values in the top row replace, and the Column input cell must be the cell that the values in the left column replace. Both must be cells that your formula refers to, directly or through other formulas.
“Input cell reference is not valid”
The input cells must be on the same worksheet as the data table. If your inputs are on another sheet, link them to cells on the data table’s sheet and point the formula at those, or move the data table to the input sheet.
“Cannot change part of a data table”
The results are a single array, so you cannot edit or delete one result cell. To change or remove the table, select all the result cells (E6:G10 in the example) and press Delete, or select the whole table and clear it.
The table does not update when inputs change
Check Formulas > Calculation Options. Large workbooks are often set to Automatic Except for Data Tables to save time. Press F9 to recalculate, or switch back to Automatic.
The workbook becomes slow
Each result cell recalculates the formula, so large tables built on heavy models slow Excel down. Keep the table small, use Automatic Except for Data Tables while you work, or copy the results and paste them as values once you are done.
Related What-If Analysis Guides
- How to do a data table in Excel (one-variable tables)
- How to use What-If Analysis in Excel (Goal Seek and Scenario Manager)
- How to create a sensitivity table in Excel
Frequently Asked Questions: How to Create a Two-Variable Data Table in Excel
What is a two-variable data table in Excel?
A two-variable data table allows you to see how changing two different variables at the same time affects a given formula.
Can I use more than two variables in a data table?
No, a standard data table in Excel can only handle two variables. For more variables, consider using scenarios or other advanced techniques.
Does a two-variable data table update automatically?
Yes, but only if your Excel settings are set to automatic calculation. If Formulas > Calculation Options is set to Automatic Except for Data Tables or Manual, press F9 to recalculate.
Why are my data table results incorrect?
Ensure that your row and column input cells are correctly referenced. Incorrect references can lead to wrong results.
Can I create a two-variable data table in Excel Online?
Excel for the web can open and recalculate data tables, but the What-If Analysis commands are not always available there. If you do not see What-If Analysis on the Data tab, select Editing > Open in Desktop App, build the table in desktop Excel, and save the file back to OneDrive or SharePoint.
How is a two-variable data table different from a one-variable data table?
A one-variable table changes a single input and can show the results of several formulas. A two-variable table changes two inputs at once but shows the result of only one formula, placed in the top-left corner of the table.
What does {=TABLE()} mean in my result cells?
It is the array formula Excel creates for a data table. You cannot type it yourself or edit it directly; recreate the table through What-If Analysis instead.
Related: How to Calculate Bond Price in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.