Home > Formulas > Sum > Data Table

Excel Data Table Examples

Debra Dalgleish - Contextures

With an Excel Data Table, you can quickly compare formula results, based on one or more settings. For example, if you're taking out a loan, which interest rate and loan term will fit your monthly budget?

compare monthly payments in data table

1) Data Tables: Compare Formula Results

In some workbooks, you might want to test different scenarios, and compare the results. For example, if you're buying a car, which loan repayment term would fit your monthly budget?

  • 4-year loan term at 5% interest
  • 3-year loan term at 4.5% interest
  • other terms and interest options??

With an Excel Data Table, you can quickly create a summary of the monthly payment options. Data Tables are one of Microsoft Excel's What If Analysis tools, along with Scenario Manager and Goal Seek.

compare monthly payments in data table

2) Calculate Monthly Payments with PMT

For the first Data Table example, I'll use the Excel PMT function, to calculate monthly payments for a car loan.

Before setting up a data table, you need to set up a regular worksheet formula, with input cells for the formula's arguments.

In the screen shot below, I set up a PMT formula in cell C6, with input cells for the PMT arguments:

  • Rate is the interest rate for the loan.
  • Nper is the total number of payments for the loan.
  • Pv argument is the present value; also known as the principal.

compare monthly payments side by side

Based on the PMT formula results in row 6:

  • If my car budget is $300 per month, I'd choose the 3-year term, to pay the loan off faster.
  • If my car budget is lower, I'd go with the 4-year term, to reduce my monthly costs.

3) Compare Multiple Options

What if you want to test multiple options for one of your formula variables, like the number of payments variable, shown above?

  • You don't have to set up each option individually, like I did for those PMT formulas.
  • Use one of Excel's built-in analysis features -- an Excel data table.

3a) Use a Data Table

A data table makes it easy to test one or two variables from a formula, and see the differences, side-by-side.

For example, in the screen shot below, the data table show results for 2 variables - interest rate and number of payments.

In the sections below, you'll see how to set up Excel data tables, make them calculate efficiently, and clear them, if necded.

compare monthly payments in data table

4) Create Data Table With 1 Variable

In this example, you will build a one-variable 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.

4a) Set Up Data Table

To set up the data table:

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

ribbon data table command

4b) Data Table Dialog Box

The Data Table dialog box opens, where you can enter the input cell details

  • Click in the Column Input cell box
  • Then, in the Excel workbook, click on cell C3
    • That cell contains the variable for the number of payments.
  • Finally, click the OK button, to close the Data Table dialog box.

data table input cell

  • 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

4c) Excel TABLE Function

If you click on one of the calculated monthly payment cells, the formula bar shows that the cell contains a TABLE function.

In the screen shot above, the TABLE function has an empty first argument (row_ref), and cell C3 as the second argument (column_ref).

  • The curly brackets at the start and end of the formula indicate that this is an array formula
  • The TABLE function is not a standard Excel function, like SUM, VLOOKUP, or other worksheet functions.
  • The TABLE function can only be used on an old-style Excel Macro sheet, like the one shown in the screen shot below

excel TABLE function on macro sheet

5) Create Data Table With 2 Variables

In this example, you will build a two-variable data table. With the 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.

6) 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

7) Speed Up Workbook Calculations

To prevent the data table from slowing down your Excel workbook calculations, you can change the calculation option for your workbook.

Most Excel workbooks are set for Automatic calculation. With that setting, Excel automatically recalculates all of the open workbooks:

  • every time there is a change, in any of the open workbooks
  • any time you open another workbook

7a) Change Calculation Setting

To prevent Excel from automatically doing all the Data Table calculations each time, follow these steps:

  • On the Excel Ribbon, click the Formulas tab
  • At the far right, click the Calculation Options command
  • In the drop down list of options, click Automatic Except for Data Tables

After you change this setting, the Data Table calculations will not occur when a recalculation is done on the entire workbook.

data table 2 variables

7b) Recalculate the Data Table

If you change the calculation mode to Automatic Except for Data Tables, the Data Tables will not update automatically.

To manually recalculate your data table, follow these steps:

  • Select the main formula cell in the Data Table
  • Press the F9 key on the keyboard, to force a calculation

7c) Faster Calcultions with Excel VBA Macros?

Excel calculation expert, Charles Williams. experimented with macros, to get see if he could get faster calculation with Data Tables.

  • In his article, Charles says, "What-If Data Tables are a great tool but they tend to calculate very slowly, in fact more slowly than I think they should."
  • Then he explains, "What-If data tables calculate slowly because Excel has to recalculate many times...So you need to be in Manual calculation mode, or possibly in Automatic except Data Tables mode.
  • His experiments showed that, "The process starts with a multi-threaded recalculation, then iterates using single-threaded recalculation, then finishes with an additional recalculation."

Charles wrote a proof-of-concept VBA macro, to see if he could make the data table calculation run faster. You can see his code, and download his sample file, in his article: Excel What-If Data Tables: Faster calculation with VBA.

8) Video: Create a Data Table

In this video, Mynda Treacy shows how to set up Data Tables in Excel, using multiple variables.

See how much a savings account will grow, with different amounts deposited monthly.

  • In the first example, there is a 1-variable Data Table, for different amounts.
  • In the second example, the Data Table uses 2 variables - amount and interest rate.
  • In the third example, a 2-variable Data Table calculates the breakeven point for a pizza shop. Conditional formatting highlights the data, so it's easier to read

9) Download the Sample File

Click here to download the Data Tables sample file. The zipped Excel workbook file is zipped, and in xlsx format. There are no macros in the workbook

More Tutorials

Scenario Manager

VLOOKUP  

INDEX and MATCH

Count Functions

 

Last updated: April 16, 2024 3:35 PM