Home > Formulas > Sum > Data Table
Excel Data Table Examples

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? 

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?
 4year loan term at 5% interest
 3year 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.
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.
Based on the PMT formula results in row 6:
 If my car budget is $300 per month, I'd choose the 3year term, to pay the loan off faster.
 If my car budget is lower, I'd go with the 4year 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 builtin 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, sidebyside.
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.
4) Create Data Table With 1 Variable
In this example, you will build a onevariable 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 WhatIf
Analysis
 In the drop down menu, click Data Table.
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.
 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.
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 oldstyle Excel Macro sheet, like the one shown in the screen shot below
5) Create Data Table With 2 Variables
In this example, you will build a twovariable 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:
 In cells B8:B13, type the number of payments for loans terms of
1 to 6 years
 In cells C7:G7, enter the interest rates between 2% and 6%
 In cell B7, enter a PMT function, referring to the loan information
cells: =PMT(C2/12,C3,C4)
 Select cells B7:G13  the heading cells and the cells for the
results
 On the Ribbon's Data tab, in the Data Tools group, click WhatIf
Analysis, and then click Data Table.
 Click in the Row Input cell box, and then click on cell C2, which
contains the variable for the interest rate.
 Click in the Column Input cell box, and then click on cell C3,
which contains the variable for the number of payments.
 Click OK, to close the 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 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:
 Select all the cells in the data table, including the heading
 On the keyboard, press the Delete key
To clear out the resulting values only:
 Select all resulting values in the data table.
 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.
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, "WhatIf 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, "WhatIf 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 multithreaded recalculation, then iterates using singlethreaded recalculation, then finishes with an additional recalculation."
Charles wrote a proofofconcept 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 WhatIf 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 1variable Data Table, for different amounts.
 In the second example, the Data Table uses 2 variables  amount and interest rate.
 In the third example, a 2variable 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