The tutorial shows how to use data tables for What-If analysis in Excel. Learn how to create a one-variable and two-variable table to see the effects of one or two input values on your formula, and how to set up a data table to evaluate multiple formulas at once.
You have built a complex formula dependent on multiple variables and want to know how changing those inputs changes the results. Instead of testing each variable individually, make a What-if analysis data table and observe all possible outcomes with a quick glance!
Two variable data tables are an extremely useful tool in Excel for analyzing how different values affect complex formulas and models They allow you to see the results of substituting a range of values for multiple variables at once.
In this comprehensive guide, I’ll walk through exactly how to create two variable data tables in Excel step-by-step By the end, you’ll have mastered this powerful analytical technique to take your financial modeling and data analysis to the next level
What is a Two Variable Data Table?
A two variable data table shows the different results of two sets of variable values in your Excel model. For example, you can substitute:
- Different interest rates and periods to see effects on loan payments
- Multiple price points and demand estimates to find revenue maximizing values.
- Expense % and sales projections to find profitability break-even points.
The data table displays every combination of the variable values and the corresponding results. This enables robust scenario analysis by substituting multiple factors.
Why Use a Two Variable Data Table?
Two variable data tables are extremely useful for:
- Sensitivity analysis – test multiple value combinations in your model.
- Scenario modeling – quickly display results of different what-if scenarios.
- Optimization – determine optimal input values to achieve financial targets.
- Break-even analysis – find where revenue covers expenses across multiple variables.
- Risk assessment – model effects of various probabilities for different factors.
- Forecasting – substitute projected values for key assumptions and variables.
With just a few clicks, data tables generate an extensive analysis showing how key values drive your formulas.
When to Use a Two Variable Data Table
Use two variable data tables when:
- You have a model, formula or metric dependent on 2 variables.
- You want to substitute a range of values for those 2 variables.
- It’s too time consuming to manually try different value combinations.
- You need to conduct sensitivity, scenario, optimization, or break-even analyses.
Data tables automate testing of different variable combinations to provide deeper insights.
One vs. Two Variable Data Tables
There are two types of data tables in Excel:
One Variable tests different values for a single variable.
Two Variable tests different values for two variables.
Examples:
- One variable – sales prices to maximize revenue
- Two variable – sales prices AND demands to maximize revenue
Two variables are best when the calculation depends on multiple factors. One variable is sufficient if only testing a single changing value.
Preparing Your Data for a Two Variable Table
Follow these steps before creating your two variable data table:
-
Input Fixed Values – Enter any fixed values used in your formulas in the appropriate cells.
-
Build Main Formula – Create the formula you want to analyze based on certain variable inputs.
-
Name Your Formula Cell – Select the cell with your formula and name it (e.g. “Results”).
-
Add Labels – Label cells that will hold your variable values (e.g. “Price”, “Demand”).
This sets up your workbook to handle the data table calculations properly.
How to Create a Two Variable Data Table in Excel
Follow these steps to generate a two variable data table:
-
Input Variable 1 Values – Enter your range of values to substitute for the first variable in a column.
-
Input Variable 2 Values – Enter your range of values to substitute for the second variable in a row.
-
Anchor Cell – Select the cell where you want the data table results displayed.
-
Go to Data Tab – Click Data > What If Analysis > Data Table.
-
Input – In the Data Table dialog box, input the cell references for:
- Row input cell – variable 1
- Column input cell – variable 2
- Table output cell – your named formula cell
-
OK – Click OK to generate the two variable data table.
That’s it! Your data table will display each combination of values and the formula results.
Data Table Calculations and Formulas
The two variable data table uses these calculations:
- Loops through each variable 1 value:
- Substitutes into the original formula
- Loops through each variable 2 value:
- Substitutes into the formula with variable 1 value
- Stores formula result in data table
- Repeats for each variable 1 and 2 value combination
This automates testing of all scenarios rather than manually changing values.
Data Table Tips and Tricks
Follow these tips when working with two variable data tables:
-
Format the data table for easy readability (number formats, borders, etc).
-
Document which values are substituted for each variable.
-
Highlight key combinations like optimal values, break-even points etc.
-
Run sensitivity analyses by varying input ranges to test impacts.
-
Nest larger tables inside cells to show multi-level variations.
-
Use descriptive cell references so it’s clear which cells drive the analysis.
-
Rename worksheet tabs to reflect different data table scenarios for easy comparison.
Data Table Limitations
Keep these limitations in mind:
-
Data tables grow very large with multiple variable values.
-
Formulas cannot refer to data table results (circular reference).
-
Not ideal for complex models with many variable inputs.
-
Results are static – formula changes require rebuilding the table.
-
Only substitutes values in existing formulas – doesn’t add new formulas.
Examples of Two Variable Data Tables
A few examples of two variable data tables:
-
Sales price and demand to optimize revenue
-
Labor and materials cost to analyze project expense scenarios
-
Interest rate and years to compare loan repayment schedules
-
Production volume and fixed costs to find break-even points
Next Steps After Creating Your Data Table
Once you’ve generated your two variable data table, next steps include:
-
Identify key insights, opportunities and risks from the scenario analysis.
-
Highlight outcomes to emphasize in reports and presentations.
-
Test more extreme values if initial ranges don’t sufficiently stress test the model.
-
Mitigate risks by developing contingency plans for unfavorable scenarios.
-
Consider probabilities of different variable combinations occurring.
-
Refine formula logic to better align with likely real-world results.
Getting the most value means going beyond just creating a data table to actively analyzing and applying findings.
Two variable data tables are incredibly useful for modeling different scenarios by substituting multiple input values in Excel formulas.
The key takeaways are:
-
Data tables show results for all combinations of variable values.
-
Useful for sensitivity analysis, break-even analysis and forecast projections.
-
Variable values are input in a column and row with results populating a data table.
-
Requires naming the output formula cell and anchoring the data table.
Now get out there, substitute some variables and let the insights begin flowing! Data tables will take your Excel skills to new heights.
How to delete a data table in Excel
As mentioned above, Excel does not allow deleting values in individual cells containing the results. Whenever you try to do this, an error message “Cannot change part of a data table” will show up.
However, you can easily clear the entire array of the resulting values. Heres how:
- Depending on your needs, select all the data table cells or only the cells with the results.
- Press the Delete key.
Done! 🙂
How to create a one variable data table in Excel
One variable data table in Excel allows testing a series of values for a single input cell and shows how those values influence the result of a related formula.
To help you better understand this feature, we are going to follow a specific example rather than describing generic steps.
Suppose you are considering depositing your savings in a bank, which pays 5% interest that compounds monthly. To check different options, youve built the following compound interest calculator where:
- B8 contains the FV formula that calculates the closing balance.
- B2 is the variable you want to test (initial investment).
And now, lets do a simple What-If analysis to see what your savings will be in 5 years depending on the amount of your initial investment, ranging from $1,000 to $6,000.
Here are the steps to make a one-variable data table:
- Enter the variable values either in one column or across one row. In this example, we are going to create a column-oriented data table, so we type our variable values in a column (D3:D8) and leave at least one blank column to the right for the outcomes.
- Type your formula in the cell one row above and one cell to the right of the variable values (E2 in our case). Or, link this cell to the formula in your original dataset (if you decide to change the formula in the future, you would need to update only one cell). We choose the latter option, and enter this simple formula in E2:
=B8
Tip. If you want to examine the impact of the variable values on other formulas that refer to the same input cell, enter the additional formula(s) to the right of the first formula, as shown in this example. - Select the data table range, including your formula, variable values cells, and empty cells for the results (D2:E8).
- Go to the Data tab > Data Tools group, click the What-If Analysis button, and then click Data Table…
- In the Data Table dialog window, click in the Column Input cell box (because our Investment values are in a column), and select the variable cell referenced in your formula. In this example, we select B3 that contains the initial investment value.
- Click OK, and Excel will immediately populate the empty cells with outcomes corresponding to the variable value in the same row.
- Apply the desired number format to the results (Currency in our case), and you are good to go!
Now, you can take a quick look at your one-variable data table, examine the possible balances and choose the optimal deposit size: 
The above example shows how to set up a vertical, or column-oriented, data table in Excel. If you prefer a horizontal layout, heres what you need to do:
- Type the variable values in a row, leaving at least one empty column to the left (for the formula) and one empty row below (for the results). For this example, we enter the variable values in cells F3:J3.
- Enter the formula in the cell that is one column to the left of your first variable value and one cell below (E4 in our case).
- Make a data table as discussed above, but enter the input value (B3) in the Row input cell box:
- Click OK, and you will have the following result:
Create a Two Variable Data Table Excel 2016
How to create a two variable data table in Excel?
To create a two variable data table, execute the following steps. 1. Select cell A12 and type =D10 (refer to the total profit cell). 2. Type the different unit profits (highest price) in row 12. 3. Type the different percentages in column A. 4. Select the range A12:D17.
How to create a data table in Excel?
Select the entire data table range including the formula, the row and column of the variable values, and the cells in which the calculated values will appear. We select the range E2:H8. Create a data table in the already familiar way: Data tab > What-If Analysis button > Data Table…
How to create multiple data tables in Excel?
Create multiple Excel data tables. Simple. 1. In the Input Data, make two columns next to the variable interest rates. 2. In the first column, create a reference to the repayment calculation in the original data. 3. In the second column, create a reference to the total interest in the original data. 4.
How to create a two-variable data table in Excel?
The steps to create a two-variable data table in Excel are basically the same as in the above example, except that you enter two ranges of possible input values, one in a row and another in a column.
