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

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.

Download the Practice Workbook

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


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 is useful if we could see the results of the formula based on changing values of these input variables.

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

What If Analysis in Excel with Data Table

Note: There are three types of What If Analysis in Excel. Let’s get familiar with them:

  • Scenario Manager
  • Goal Seek
  • Data Table

Learn More


2 Ways to Perform the What If Analysis with Data Table

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.

What If Analysis in Excel with Data Table

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 two-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.

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

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.

What If Analysis in Excel with Data Table One Variable

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

What If Analysis in Excel with Data Table


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.

what-if-analysis-with-data-table

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.

What If Analysis in Excel with 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.

What If Analysis in Excel with Data Table

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

Read More: How to Make a Data Table in Excel (Easiest 5 Methods)


Similar Readings


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.

What If Analysis in Excel with Data Table Two Variable

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.

What If Analysis in Excel with Data Table Two Variable

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.

What If Analysis in Excel with Data Table

Read More: How to Create a Two Variable Data Table in Excel


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, 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.

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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo