Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this worksheet called DataTable row 3 contains different interest amounts and they're incremented by an eight of a percent. The formulas in all of these cells except for cell C3, and in column B we've got amounts. Maybe we're considering borrowing money, and these are incremented by 25,000. And what we'd like to create here is ultimately a grid containing the monthly payments with these different interest rates and amounts being borrowed. In cells B1 and B2, we have got 3% and 200,000.
Let's put in a formula here in B3. We're going to be using the Data Table feature. It's found on the Data tab under What-if Analysis. Let's first set up a formula in cell B3. Using the function PMT, this allows us to calculate the monthly payment based on an interest rate, in this case 3%. We want to divide this by 12 since we're paying our interest monthly, the term here 360 months. We'll show you later how we might want to change that.
We're setting up here what might be called a master formula, and we want to borrow 200,000. If we do press ENTER now, the amount that we see will be expressed negatively. So let's put a minus in front of the B2 here, so what we are about to see will be the monthly payment on a loan of 200,000 at 3% over 360 months and there is that amount. Now, think of this as a master formula. The Data Table feature is going to ask us what we need to use for a percent change.
We will be referring to this 3% cell, but as we select these cells, Data Table is going to fill all these in rapidly. Data tab, What-If Analysis, Data Table. It asks us for a Row Input cell. Which row entries, meaning the percents here are we referring to? We refer to cell B1. The column input cell, that's cell B2, okay, and there's our list.
Now, although we have a formula here, we really don't need these input cells anymore, they can be empty. We could just highlight these and press Delete. It's not going to change our results at all, we could get rid of the color if we wanted too, and as we look at the results we might say, well, what are these numbers going to be if it's 15-year loan? Let's put in the number 180 in cell A1, and as we put in the 180 and press Enter, we've got a new set of numbers. And so each of these cells here is reacting to the cells out of row 3 for the percent change, the cells out of the column B for the amount being borrowed.
And so, we can change these results rapidly. And furthermore, since we earlier had set up some formulas here, now I did this ahead of time, and this isn't always a requirement, and isn't always a characteristic of a Data Table. But if we want to show these numbers starting at 2.5%, I'm going to type in 2.5%. I've already got formulas in place there that take into account that we want each of these to be an eight of a percent higher. So now we range from 2.5% all the way up to 4%, and we could do the same thing down the left hand side, each of these cells has formulas.
And again, this is not necessarily the characteristic of a Data Table. You might want to set this up yourself this way. So what we can also do here to make this look more interesting is select these cells, and then as we do the quick analysis in the lower right hand corner invites us to make some changes. Click it. How about Data Bars, how would they look here? Maybe not so good, how about Color Scales, well that could be interesting. How about Icon Sets? Maybe that could be interesting as well. It divides the data into thirds; we may or may not want to use that.
If we do make this choice, we will have to make the columns wider, we could do that. So I'm able to decide on Color Scale. And if you are familiar with Conditional Formatting you might want to jump over to the HOME tab and then go to Conditional Formatting, and get a preview as to how other Color Scale options might appear. Maybe we'll choose this one. But even though we could have done this with formulas and in a different way, the Data Table concept here facilitates the idea that we created a Data Table. We can make changes to row 3 here to change the percents, or the column B entries to change the amount, or go back to cell A1, maybe change that to 360 that way it was earlier.
I've got flexibility here by way of Data Table, found on the Data tab, in the What-If Analysis group, Data Table.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69969 Viewers
80 Video lessons · 127384 Viewers
52 Video lessons · 62342 Viewers
59 Video lessons · 48035 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.