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

datatable01

A data table lets you experiment with one or two variables in a formula, and see the results, in a compact layout.

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.

datatable06

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.

datatable02

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.

datatable03

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.

datatable04

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.

datatable05

Download the Sample File

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

Take the Data Tables Poll

Going back to the question that I asked at the start of this article – Do you use Data Tables? Please take this short poll, and let me know.

___________________________

2 comments to See Formula Results in an Excel Data Table

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

  • 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

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>