While working with **Microsoft Excel**, create a complicated formula that depends on a number of different inputs, and we wish to understand how altering these inputs and output affects the output. Build a **What-If Analysis Data Table** rather than evaluating per variable separately so we can quickly see all possible results. In this article, we will demonstrate two different examples to create a **Two Variable Data Table with What If Analysis in Excel**.

**Download Practice Workbook**

You can download the workbook and practice with them.

**What Is Two Variable Data Table in Excel?**

**Data Table** variables are one sort of constant that could function as a dataset or a straightforward worksheet with columns and rows and hold large amounts of data. A **two-variable data table** demonstrates how different variations of the quantities of the two sets of variables affect the formula outcome. In other terms, it demonstrates how altering two information values can alter the result of a given calculation.

**2 Ideal Examples of Creating Two Variable Data Table with What If Analysis in Excel**

Users can evaluate two variables or values concurrently or at once in a data table for computation by using the **Excel What-If Analysis** data table feature. Let’s look at the examples for two-variable data tables.

**1. Create Two Variable Data Table for Monthly Payment Using Excel What If Analysis**

In this example, we have a dataset containing **loan amount**, **interest rate**, **number of payments**, and **monthly payments**. We will calculate the **monthly payments **using **the PMT function**. It is a component of the financial functions and determines the loan amount based on fixed repayments and a fixed mortgage rate. Let’s see the change of two of the input variables simultaneously.

**STEPS:**

- Firstly, select the cell where you want to put the
**PMT**function to compute monthly payments. - Secondly, put the formula into that selected cell.

`=PMT(C5/12,C6,C4)`

- Thirdly, press the
**Enter**button on your keyboard to see the result.

- Now, we need to set up a two-variable data table, we take
**months**(**starting with months of 60 to 156**) on the**x-axis**and**loan amount**(**starting with $80,000 to $120,000**) on the**y-axis**.

- Choose cell
**F3**and allocate the value of cell**C7**which is monthly. - Then, press
**Enter**.

- At this point, select the whole data table.
- After that, go to the
**Data**tab from the ribbon. - Consequently, click on the
**What-If Analysis**drop-down menu, under the**Forecast**group. - Subsequently, select the
**Data Table**from there.

- Thus, the
**Data Table**dialog box will pop up. - Select cell
**$C$4**for the**Row input cell**field and cell**$C$6**for the**Column input cell**. - Click on the
**OK**button to complete the process.

- Hence, the data table will result automatically based on the data value.

- If you click any of the cells of the data table, you will find the formula down.

`{=TABLE(C4,C6)}`

- The formula depends on the dataset that has all the information.

- We can not change the value of the data table. If you want to change the value, a Microsoft Excel pop-up window will appear which says
**Can’t change part of a data table.**

**Note:**You only can change the value of cell

**C4 to C6**. And this will automatically change the data in the data table.

**Read More: ****How to Create One Variable Data Table Using What If Analysis**

**2. Utilize Excel What If Analysis to Generate Two Variable Data Table for Future Value **

We will now generate two-variable data tables for future values. And we will get the future value using **the FV function**. The** FV **function determines the value of a particular investment using a fixed rate of return as a component of financial functions.

**STEPS:**

- In the first place, choose the cell where you want to insert the
**FV**function to calculate**future value**. - Next, enter the formula into the cell you just chose.

`=FV(C5/12,C6*C7,-C4)`

- To view the outcome, press the
**Enter**key on your keyboard.

- Now that we have a two-variable data table set up, we will use the
**x-axis**to represent the**interest rate**(**ranging from 5% to 12%**) and the**y-axis**to represent the**number of years**(**ranging from 12 to 16**). - Select cell
**F3**and enter the future value from cell**C8**there. - After that, hit
**Enter**.

- Pick all of the data in the table at this stage.
- After that, use the ribbon to navigate to the
**Data**tab. - Therefore, under the
**Forecast**group, select the**What-If Analysi**s drop-down menu. - Select the
**Data Table**from there after that.

- The
**Data Table**dialog box will appear as a result. - Choose cell
**$C$5**for the column input cell and cell**$C$7**for the row input cell field. - To end the procedure, click the
**OK**button.

- Therefore, based on the input value, a data table will automatically be generated.

**Read More: ****How to Perform What If Analysis in Excel (3 Examples)**

**Things to Keep in Mind**

- The complete array range must be selected before pressing the
**Delete**key on the keyword if a user wishes to alter or delete a portion of the output from the data table. The preceding approach must be used if the user chooses to utilize**Ctrl**+**Z**or undo, respectively. - In situations where two variables alter at once, it is useful to consider the outcome.
- The related formula for the
**Data Table**doesn’t need to be updated manually. - Data tables differ from Excel tables, which are used to manage a collection of connected data.

**Conclusion**

The above examples will assist you to create** Two Variable Data Table with What If Analysis in Excel**. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the **ExcelDemy.com** blog!

## Related Articles

**How to Delete What If Analysis in Excel (2 Simple Methods)****What-If Analysis in Excel with Example****How to Do What-If Analysis Using Scenario Manager in Excel****Do What-If Analysis Using Goal Seek in Excel****How to Use What If Analysis in Excel (with All 3 Features)****What If Analysis Data Table Not Working (Issues with Solutions)****How to Perform What-If Analysis in Excel (2 Ways)**