In this article, weâ€™ll illustrate the use of the **What If Analysis** with **data table** and how it is useful to make financial decisions.

**Table of Contents**hide

**Introduction to the What if Analysis with Data Table**

In Excel the What If Analysis is used to see how different values of an input variable of a formula affect the outcomes of the formula. In most cases, the outcome of aÂ formula depends on multiple input variables. For making decisions, it would be useful if we could see the results of the formula based on changing values of these input variables.

For example, a data table can help decide on the monthly payment to repay a loan as itâ€™ll provide us with a range of monthly payments based on different interest rates and payment terms. Letâ€™s see an overview:

**Note: **There are three types of What If Analysis in Excel. Letâ€™s get familiar with them:

**Scenario Manager****Goal Seek****Data Table**

**How to Perform the What If Analysis with Data Table in Excel**

A data table cannot analyze data for more than two variables (one for the row input cell and another for the column input cell).Â But it can produce as many results as we want for these two variable combinations.

Letâ€™s introduce the dataset weâ€™re gonna use in this article. We used **the PMT function **to calculate the monthly payment to pay a loan of 40,000 dollars at an interest rate of 9% with 60 payment periods.

Letâ€™s put the following formula in the cell **D7**

`=PMT(D5/12,D6,-D4)`

**Formula Breakdown:**

Compare it with

**=PMT(rate, nper, pv, [fv], [type])**

**rate**=

**D5/12**;

**D5**represents the

**annual interest rate**of 9%, we

**divide**it with

**12**to adjust is for

**monthly**.

**nper**=

**60**; for 5 years 5*12=60

**pv=**

**40,000**; the present value is the total

**loan amount**

**Result**: Payment per Period (**pmt-monthly**) = **830**

Now, with this dataset, we are going to evaluate different outputs for **one-variable** change (interest** rate and term separately**) and also for t**wo-variable** (interest** rate and term together)** change.

**1. ****One-Variable Data Table**

One variable data table can be used when we want to see the results that change with different values of one input variable. Here weâ€™ll see two examples.

**1.1 One-Variable in the Row Input Cell**

As our data table is **row-oriented**, we entered the formula to calculate **pmt-payment per month** in the **first column** of the data table. Then, we put different values for **no of payment periods (nper)** in a row seen in the screenshot and, the row below that weâ€™ll calculate different **pmt values **corresponding to these changing **nper **values.

In this illustration, **cell I6 **contains the formula for calculating **pmt-payment per period.**

** **

Letâ€™s follow the steps below:

**Select**the**data table**along with the**cell**that contains the**formula**.- Go to the
**Data**tab in the**Excel Ribbon**. - Click on the
**What If Analysis**dropdown and choose**Data Table.**

** **

Following the above steps will open up a window:

- Enter the
**cell reference (C6)**for the input cell in the**Row input cell** - Hit
**OK.**

Finally, we get the **payment per period** values for the **corresponding **values of **no of payment periods**.

**1.2 One-Variable in the Column Input Cell**

This time our data table is column-oriented, we entered the formula to calculate pmt-payment per month in the first row of the data table. Then, we put different values for the **annual interest rate** in a column seen in the screenshot and, the column right to that weâ€™ll calculate different **pmt values **corresponding to these changing **interest rates**.

In this illustration, **cell G4 **contains the formula for calculating **pmt-payment per period.**

Letâ€™s follow the steps below:

**Select**the**data table**along with the**cell**that contains the**formula**.- Go to the
**Data**tab in the**Excel Ribbon**. - Click on the
**What If Analysis**dropdown and choose**Data Table.**

** **

Following the above steps will open up a window:

- Enter the
**cell reference ( C5 )**for the input cell in the**Column input cell** - Hit
**OK**.

** **

Finally, we get the **payment per period** values for the **corresponding **values of the **annual rate of interest.**

**2. ****Two-Variable Data Table**

We can use a two-variable data table to illustrate how a different value of two variables in a certain formula changes the result of that formula. Letâ€™s dig into the example:

In this example, we put the formula in cell **G4. **Â The corresponding **row **of that cell has different values of **nper** and the corresponding **column **of the **G4 **cell contains different annual interest **rate **values.

Letâ€™s follow the steps below:

**Select**the**data table**along with the**cell**that contains the**formula**.- Go to the
**Data**tab in the**Excel Ribbon**. - Click on the
**What If Analysis**dropdown and choose**Data Table.**

** **

Following the above steps will open up a window:

- Enter the
**cell reference (D6 )**for the input cell in the**Row input cell** - Enter the
**cell reference (D5 )**for the input cell in the**Column input cell** - Hit
**OK**.

Finally, we get the different **payment per period** values for the **corresponding annual rate of interest and no of payment periods.**

**Things to Remember**

- Too many data tables in a worksheet will slow down the speed of calculations of an Excel file.
- No further operation is allowed in a data table as it has a fixed structure. inserting, or deleting a row or column will show a warning message.
- The data table and the input variables for the formula must be in the same worksheet.

**Download the Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

Now, we know how to perform What If Analysis with Data Table in Excel. Hopefully, it would encourage you to use this functionality more confidently. Any questions or suggestions donâ€™t forget to put them in the comment box below.