Contextures

Excel 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

Compare Results in a Data Table

With the PMT function, you are able to calculate the monthly payment for a loan, based on an interest rate, number of periods and the amount of the loan. If you adjust the rate, periods or amount, the formula result changes, so you can see the effect of these different variables.

Instead of testing the results with a single formula, you can set up a data table, and compare the results, side-by-side. In this tutorial, you'll set up two data tables - one with 1 variable, and another with 2 variables.

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.

    ribbon data table command

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

    data table input cell

  6. Click OK, to close the dialog box.
  7. 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.

    data table results

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

Clear a Data Table

Because the data table values are in an array, you cannot edit or clear individual cells. If you try to change one cell, you will see an error message - "Cannot change part of a data table." If you want to remove the entire table, or the resulting values, follow the steps below.

To remove the data table from the worksheet:

  1. Select all the cells in the data table, including the heading
  2. On the keyboard, press the Delete key

To clear out the resulting values only:

  1. Select all resulting values in the data table.
  2. On the keyboard, press the Delete key

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.

    data table 2 input cells

  8. Click OK, to close the dialog box.
  9. 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.

    data table 2 variables

  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.

Download the Sample File

Click here to download the data tables sample file. The file is zipped, and in xlsx format, with no macros.

More Function Tutorials

SUM  

VLOOKUP  

INDEX and MATCH

Count Functions

INDIRECT   

Function Videos 

Search Contextures Sites

 

pivot power premium

 

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

 

 

 

Last updated: December 4, 2016 3:36 PM