Exploring One and Two Variable Data Tables in Excel

Data Tables are another one of the what-if analysis tools available in the forecast group on the data tab along with Goal Seek. We recently took a look at Goal Seek; now we're going to take a look at Data Tables. Data Tables show the range of effects of one or two different variables on a formula.

We're going to start off by creating a one-variable data table. In this exercise, we want to imagine that someone has taken out a $500,000 loan at an interest rate of 5%. They need to pay back that loan over the course of 360 months; that amounts to 30 years.

So it's a 30-year mortgage, and with all those different elements combined, we have figured out that their monthly payment is going to be $2,684.11. Now, if I go to the cell that contains that value and I'll press F2; we used the Excel payment function to figure out that monthly payment. Basically, we'll divide the interest rate by 12 so we can get a monthly interest rate rather than the annual interest rate. We'll take the number of payments, which is 360.

Again, we're using the monthly time period rather than annual, and we'll use, of course, our present value, which is the loan amount of $500,000. That formula calculates the monthly payment. But let's say this person who's taking out this loan wants to shop around and wants to figure out what their monthly payment would be if their interest rate was 3%, 3.5%, 4%, 4.5%, 5%, or 5.5%. We already know what it will be if it's 5%, but we want to check out these other interest rates.

So to create the data table, we'll need to select the entire data table. Now, this is not how you select the data table because you're going to leave out the calculation of the monthly payment. This is not how you select the data table because you'll leave out the variable interest rates.

This is not a table because again, you've left out the interest rates, even though you have the formula. And this is not the data table because you're leaving out the formula. So the full data table includes the formula and the interest rates.

Now we can go over to the data tab, head over to the forecast group, click the dropdown and look for data table. If I click data table, I will be given a data table dialog box with two inputs, the row input cell or the column input cell. Now I'm going to say there are only two questions that need to be answered here.

So you can figure out what to do to get the answer. First question's easy enough. First question is where are the interest rates that you want to replace in the original formula? Are they in a column or are they in a row? Well, it's obvious to see in the data table, they're in a column.

So that is the cell that I'm going to need to fill in. Then the next question is what is the location of the interest rate that needs to be replaced in the formula? What is the location of the interest rate that needs to be replaced in the formula? We can replace it with 3%, 3.5%, 4%, and so on. Well, the location of the cell that contains the interest rate in the formula is C15.

And that's the only two questions you need to answer. As soon as you click okay, Excel goes to work and projects all the different monthly payments, basically substituting C15 with 3%, 3.5%, 4%, 4.5%, etc. So that is a one-variable data table.

Let's take a look and see what a two-variable data table looks like. Starting from scratch, we have a formula that calculates 2% annual growth over 10 years, depending on an initial contribution. For this particular exercise, someone has invested $40,000 and they're getting an annual return of 2% every year.

That is going to be for a period of 10 years. At the end of 10 years, their total return on investment is $48,760. Now, I need that original formula.

So what I'm going to do is I'm going to copy that formula. I'll press escape after I copy inside the cell. Then I'll go in and paste here.

You want to copy inside the cell because you want to keep the same cell referencing. If I go to that cell and press F2, I'll see that it is still referring to the original 40,000 and 2%. Now I need to select the data table.

Just like I explained earlier, you're going to select all the different variables as well as the formula. Now our results are going to show up inside the gray cells that are no longer being selected here. So let's apply the data table.

Let me just get rid of that dialog box. Okay, there we go. I'll go to the data tab.

I'll go to What-If Analysis. I'll click the dropdown. I'll choose data table.

Now, the questions are a little different here. We're using both the column input cell and the row input cell because we want to input those values into the formula. We just need to tell the data table which cells to replace.

Well, for the formula I have in that cell, we already saw that the formula was referring to these two values. So for this value, when it comes to the row input cell, 40,000 is what I'm going to need to select because I want to replace 40,000 with the values in the row 40,000; 50,000; 60,000; 70,000; and 80,000. So I'm going to select 40,000 here.

Column input cell, well, these are the values that I want to go into the formula, but I'm going to need to replace it with the current value, which is in C45. Now, all I need to do is click okay. That formula has been recreated throughout the entire grid, and I can see what my value over the course of 10 years is going to return at 40,000; 50,000; 60,000; 70,000; and 80,000 if my annual return is either 1%, 2%, 4%, 6%, 8%, or 10%.

So that's how you can use the data table to project different results for your calculations depending on variables.