What-IF Analysis – Chapter III – Data Tables

June 2, 2019
Analysis

Data Tables

Instead of entering formulas and variables individually, to compare results, you can set up a Data Table, with one or two variables. Data Tables are one of Excel’s “What If Analysis” features

With a Data Table in Excel, you can easily vary one or two inputs and perform What-if analysis. A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem.

There are two types of Data Tables

  1. One-variable Data Tables
  2. Two-variable Data Tables

One-variable Data Tables

A one-variable Data Table can be used if you want to see how different values of one variable in one or more formulas will change the results of those formulas. In other words, with a one-variable Data Table, you can determine how changing one input changes any number of outputs. You will understand this with the help of an example.

Create a Data Table With 1 Variable

In this example, you will build a data table that shows the monthly payments for loan terms ranging from 1 to 6 years. The number of payments will range from 12 to 72.

NOTE: The Input cells have to be on the same sheet as the data table.

The loan information is in cells C2:C4, with the number of payments in cell C3.

To set up the data table:

  1. In cells B8:B13, type the number of payments for loans terms of 1 to 6 years
  2. In cell C7, enter a PMT function, referring to the loan information cells: =PMT(C2/12,C3,C4)
  3. Select cells B7:C13 – the heading cells and the cells for the results
  4. On the Ribbon’s Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.

5.       Click in the Column Input cell box, and then click on cell C3, which contains the variable for the number of payments.

  1. Click OK, to close the dialog box.
  2. Select the cells with the monthly payments, and format as Currency. In the screen shot below, the format is Currency, with negative numbers bracketed and in red.

  1. Click on one of the calculated monthly payment cells, and the formula bar shows that the cell contains a TABLE function, with cell C3 as the second argument. The curly brackets at the start and end of the formula indicate that this is an array formula

Two-variable Data Tables

A two-variable Data Table can be used if you want to see how different values of two variables in a formula will change the results of that formula. In other words, with a twovariable Data Table, you can determine how changing two inputs changes a single output. You will understand this with the help of an example.

Create a Data Table With 2 Variables

In this example, you will build a data table with 2 variables. It will show the monthly payments for loan terms ranging from 1 to 6 years, and interest rates from 2% to 6%.

NOTE: The Input cells have to be on the same sheet as the data table.

The loan information is in cells C2:C4, with the interest rate in C2, and the number of payments in cell C3.

To set up the data table:

  1. In cells B8:B13, type the number of payments for loans terms of 1 to 6 years
  2. In cells C7:G7, enter the interest rates between 2% and 6%
  3. In cell B7, enter a PMT function, referring to the loan information cells: =PMT(C2/12,C3,C4)
  4. Select cells B7:G13 – the heading cells and the cells for the results
  5. On the Ribbon’s Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
  6. Click in the Row Input cell box, and then click on cell C2, which contains the variable for the interest rate.
  7. Click in the Column Input cell box, and then click on cell C3, which contains the variable for the number of payments.

  1. Click OK, to close the dialog box.
  2. Select the cells with the monthly payments, and format as Currency. In the screen shot below, the format is Currency, with negative numbers bracketed and in red.

10.   Click on one of the calculated monthly payment cells, and the formula bar shows that the cell contains a TABLE function, with cell C2 as the first argument, and C3 as the second argument. The curly brackets at the start and end of the formula indicate that this is an array function.