How to Compare Formula Results with Excel Data Table (2024)

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??

In this tutorial, you'll see how to:

-- Set up loan payment calculation

-- Compare multiple options in formula

-- Compare loan payments 1 variable

-- Compare loan payments with 2 variables

Calculate Monthly Payments with PMT

With the PMT function, you can calculate the monthly payment for a loan, based on 3 variables:

1. interest rate
2. number of payment periods
3. amount of the loan

These side-by-side PMT formulas show what your monthly payments will be, based on changing one variable in the formula -- the number of payments.

The formula in cell C6 divides the annual rate by 12, to calculate the monthly interest rate:

• =PMT(C2/12,C3,C4)

For a 48-month term, the monthly payment is $230.29, and for a 36-month term, it's $299.71 per month.

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.

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.

-- Set up a 1 variable Data Table

-- Set up a 2 variable Data Table

-- Speed up workbook calculations

-- Clear a Data Table

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.

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.

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.

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

• 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

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.

   

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.

   

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.

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

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.

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

Use Excel VBA

You can also use macros to get faster calculation with Data Tables. See this post by Excel calculation expert, Charles Williams: Excel What-If Data Tables: Faster calculation with VBA

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

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

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