Locked lesson.

Upgrade

Quick reference

What-If Analysis: Data Tables

Create Data Tables to view results based on one or two variables. 

When to use

We create a one- or two-variable data table whenever we want to see what our results will be with variable inputs. 

Instructions

Data Tables can be useful to view results with variable inputs. We can create one or two variable data tables. 

In our example, we will be using the PMT function again to calculate the monthly payments on a loan. We will then create a one and two-variable data table to see what our monthly payment will be if we have a varying loan amount and interest rate. 

The PMT Calculation

We first need to calculate our monthly payments using PMT. 

One-Variable Data Table

First, we will create a one-variable data table so we can see what our monthly payment will be if the interest rate varies. We need to include the result of the PMT calculation in our data table. 

  • Click in cell C16 and type =C10 to link to the calculation. 

  • Select the PMT calculation and all the variable interest rates. 

  • From the Data tab, in the Forecast group, click What-If Analysis and select Data Table. 

As this is a one-variable data table, we only need to define one set of input values. 

  • Click in Column input cell. 
  • Select the interest rate cell from the table. 

 

  • Click OK. 

We can check our calculations are correct by referring to the known values in the input table. 

Two-Variable Data Table

Next, we will create a two-variable data table. This time, we will use both the column and the row input cells to build our table. 

  • Click in cell B16 and type =C10 to link to the calculation. 

  • Select the data table including the PMT calculation. 

  • From the Data tab, in the Forecast group, click What-If Analysis and select Data Tables. 

As this is a two-variable data table, we need to define two sets of input values. 

  • Click in Column input cell. 
  • Select the interest rate cell from the table. 
  • Click in the Row input cell. 
  • Select the loan amount cell from the table. 

  • Click OK. 

Hints & tips

  • To hide the PMT calculation, select the cell and press CTRL+1. On the Number tab, select Custom and add ;;; to the Type field. 
Login to download
  • 00:04 Another What If analysis utility that we can use in Excel that can be
  • 00:08 extremely useful are Data Tables.
  • 00:11 And we have a choice of a one variable data table or a two variable data table.
  • 00:16 So what exactly are data tables and why are they useful?
  • 00:19 Well, we're going to start out in this example by creating a one variable data
  • 00:24 table.
  • 00:25 So if you take a look at the top here,
  • 00:27 you can see that I have a PMT calculation that I need to do.
  • 00:31 If you recall, PMT allows us to calculate the monthly
  • 00:35 repayments on a loan based off of the information above.
  • 00:40 So maybe I want to take out a loan from the bank for $300,000.
  • 00:44 The interest rate is 3% annually, and I want to borrow it over a term of 25 years.
  • 00:51 So I'm going to work out in a moment what my monthly payment is going to be.
  • 00:55 Now a data table is something we can set up that uses that PMT calculation,
  • 01:01 but shows us what our monthly payments are going to be with a variable interest rate.
  • 01:07 So you can see below,
  • 01:08 I've just listed out some of the interest rates are from 1 to 5.5%.
  • 01:13 So maybe I want to know what my payment is going to be if this interest rate
  • 01:18 goes up or goes down, that is what we can use a data table for.
  • 01:22 And we use a one variable data table because we have one variable, right,
  • 01:27 the variable is the interest rate only.
  • 01:29 So let's do our PMT calculation first of all, and
  • 01:32 then I'll show you how to create the one variable data table.
  • 01:35 So we're going to click in our cell, we're going to use PMT.
  • 01:39 We're going to use three of the five arguments here, the first one is the rate.
  • 01:44 So remember, this is an annual interest rate.
  • 01:48 And because we're working out monthly repayments, we need to divide it by 12.
  • 01:53 NPER, is the number of payments, so this relates to the term.
  • 01:58 Now again, I have the term listed out in years, 25 years, so
  • 02:02 I need to turn this into months.
  • 02:04 So we're going to do this cell multiplied by 12 this time.
  • 02:09 And then the final argument is PV, which is present value.
  • 02:12 So that is basically the current loan amount which is 300,000.
  • 02:16 Let's close the bracket, hit Enter.
  • 02:19 And you can see currently, I'm going to be paying 1,422.63 a month.
  • 02:26 Now this is showing as a negative value because the money is coming out of your
  • 02:30 bank account.
  • 02:31 If you want to make it positive, remember you can just edit the formula.
  • 02:34 Just add a minus on the front of the PMT calculation and
  • 02:37 that's going to give you the same value but just a positive number.
  • 02:41 So now that we have our PMT calculation, we can use this to create our data table.
  • 02:46 Now the most important thing here is that you need to put the PMT calculation in
  • 02:51 a cell so that it allows you to select kind of like a table version just here.
  • 02:56 So when I'm creating my data table,
  • 02:59 I need to be able to select the values, but also the PMT calculation.
  • 03:04 So in general, I would make a selection like this.
  • 03:06 So I want my PMT calculation to go into cell C16 to make this really easy.
  • 03:12 So we're just going to link to the cell to pull that down to there.
  • 03:17 Now, I can select the PMT and also my interest rates and create my data table.
  • 03:24 So let's jump up to Data.
  • 03:26 We're going to go into What If analysis and we're going to choose Data Table.
  • 03:30 Now here we have two different input cells, Row input cell and
  • 03:33 Column input cell.
  • 03:34 Now because this is a one variable data table,
  • 03:37 we only need to complete one of these.
  • 03:40 And this is going to be the column input cell,
  • 03:42 because we're inputting numbers into the column moving down.
  • 03:46 And what is our variable?
  • 03:48 What variable are we using?
  • 03:49 We're using the interest rate.
  • 03:51 So we need to select that from our little table at the top there.
  • 03:56 When we click on OK, it fills down our monthly payments.
  • 04:00 Now we can very easily check to make sure that this is working correctly based on
  • 04:05 the data that we have in the table above.
  • 04:07 So, If we have an interest rate of 3%, the monthly payment is 1,422.63.
  • 04:13 So let's just check that in the table, 3%, 1,422.63.
  • 04:18 So this is working fine.
  • 04:21 So now I can see what my monthly payment is going to be if
  • 04:24 that interest rate goes down, or if it goes up, nice and simple.
  • 04:28 Now two variable data tables are very similar.
  • 04:31 So let's take a look at an example.
  • 04:33 Now this time we have a very similar thing.
  • 04:36 We have our interest rates listed down in the column, but
  • 04:40 this time we have different loan amounts in row 16.
  • 04:44 So this time I want to create a data table that's going to show me what my
  • 04:49 monthly payment is going to be based off of a variable interest rate and
  • 04:53 a variable loan amount.
  • 04:55 So we have two variables here, so we'll be using a two variable data table.
  • 05:01 Now the process of doing this calculation is exactly the same.
  • 05:04 I've already done the PMT calculation at the top, so
  • 05:07 we just need to bring that into this table.
  • 05:10 So we're going to press = and we're going to link to the cell.
  • 05:14 Now we can very easily select everything, go up to the Data tab,
  • 05:20 > into What If analysis > and Data Table.
  • 05:24 Now this time we're going to fill values in in both of these cells because we
  • 05:28 are doing two variables.
  • 05:30 So if you recall, the column input cell last time was the interest rate amount,
  • 05:35 so that is exactly the same but the row input cell this time is the loan amount.
  • 05:40 Let's click on OK and like magic, we have those monthly payments.
  • 05:46 Let's do a quick visual check to make sure that this is working correctly.
  • 05:50 So using our table at the top,
  • 05:53 if we have a loan amount of 300,000 at an interest rate of 3%,
  • 05:58 it should be 1422.63, which I can see that it is.
  • 06:03 So that is basically how data tables work.
  • 06:06 Now, I want to leave you with just one final little tip.
  • 06:09 If you're presenting this data to somebody else, it might be that you don't want
  • 06:14 this PMT calculation to show in the corner of the table.
  • 06:17 Now, what a lot of people will do is just change the font to white to disguise it.
  • 06:22 But a much better way of doing this,
  • 06:25 is to press Ctrl+1 to open up the Format Cells dialog box,
  • 06:30 go down to Custom formatting, and simply type in three semi-colons.
  • 06:36 Click an OK, and you'll see that that hides the value in the cell.
  • 06:41 But if you take a look in the formula bar, it's still showing that this cell is
  • 06:45 actually linking to cell C10 to pull the value in.
  • 06:48 So, it doesn't delete any formulas or anything like that,
  • 06:53 it's still sitting there behind,
  • 06:55 it just doesn't display the text or the value in the cell.

Lesson notes are only available for subscribers.

Gift this course