See Formula Results in an Excel Data Table

Formula Results in an Excel Data Table

Do you ever use the Data Table feature in Excel? It’s one of the “What-if Analysis” tools, found on the Ribbon’s Data tab, along with Scenario Manager, and Goal Seek.

You can experiment with one or two variables in a formula, and see formula results in an Excel Data Table, in a compact layout.

Here is the Data Table command, in the What-If Analysis drop down on the Data tab.

Data Table command on Excel Ribbon
Data Table command on Excel Ribbon

Get Ready to Build a Data Table

Before you can build a data table, you need a formula on your worksheet. It should use cell references in its arguments, like this PMT function, in cell C7.

Excel PMT function
Excel PMT function

The variables are in cells C2, C3, and C4, and any one or two of those could be used in a data table.

One Variable Data Table

For example, you can use a one-variable data table to show the monthly payments for the loan, using the number of months, in cell C3, as the variable. The options for the months variable are entered in a column – B9:B13.

one-variable data table dialog box
one-variable data table dialog box

To create the table, select cells B7:C13, and use the Data table command on the Ribbon’s Data tab, with $C$3 as the Column Input Cell.

Not too surprisingly, the data table below shows that the payment is lower, if the number of months is higher.

one-variable data table
one-variable data table

Two Variable Data Table

In a two-variable data table, you could keep the number of months as the first variable, and add the interest rate as the second variable. In this example, the PMT function is in cell B7 – the top left corner of the data table.

  • The interest rates are in row 7, across the top of the data table, and the row input cell is C2, where the annual interest rate is entered.
  • The number of months are in column B, and the column input cell is C3, where the number of months is entered.
Data Table dialog box
Data Table dialog box

When you create the two-variable data table, it shows the monthly payments required for each combination of the two variables – from a 12 month loan at 2%, to a 72 month loan at 6% annual interest.

two-variable data table
two-variable data table

Download the Sample File

To download the sample file, and see the detailed instructions, please visit the Data Table page on my Contextures website.

___________________________

0 thoughts on “See Formula Results in an Excel Data Table”

  1. I had not used this feature before and wasn’t aware of it. The payment scenario seems like a good spot to use it in the future. Thanks for the post.

  2. I don’t really understand the use case for these. Why wouldn’t I just write the formula myself in C8? I’d lock in C$7 and $B8, then then copy it through the table.
    The only thing I could think of is if it were a really complex formula, and a huge data table, and the Data Table tool offered a recalculation performance benefit. But with modern processors, I doubt you’d have an issue with just a 2-variable formula. (and, would there actually be a performance benefit?)
    When would you recommend using this tool?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.