Excel Data Tables

Data Tables and other tools like Goal Seek are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

Data Tables

Data tables is another one of the What If Analysis tools available in the forecasts group on the data tab along with Goals Seek. We recently took a look at Goals Seek. Now we're gonna 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. 

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 percent. 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 press F2, we use 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 the yearly. 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 percent, 3.5, 4 percent, 4.5, 5 percent or 5.5. 

We already know what it will be if it's 5 percent. But we want to check out these other interest rates. 

So in order 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. 

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 the Data Table dialog box with two inputs, the row input cell or the column input cell. 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 is easy enough. The 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? So 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. 

Those are the only two questions you need to answer. As soon as you click OK, Excel goes to work and projects all the different monthly payments, basically substituting C15 with 3 percent, 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. 

Two-Variable Data Table

Starting this from scratch, we have a formula that calculates 2 percent 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 percent 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 Esc 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 percent. So 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. 

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. So we just need to tell the data table which cells to replace. 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 40000 with the values in the rows 40, 50, 60, 70 and 80. So I'm going to select 40000 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 OK. 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, 50, 60, 70 and 80,000 if my annual return is either 1, 2, 4, 6, 8 or 10 percent. 

Recap 

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