Join Dennis Taylor for an in-depth discussion in this video Using data tables, part of Excel 2016 Essential Training.
- [Instructor] On this worksheet called Data Table, imagine we're trying to set up some formulas here to show what the monthly payment's going to be if you borrow different amounts. We see these in column B moving downward. Notice how they're 25,000 apart, and across the top we see various interest rates. Perhaps these are for a house. We also see the number 360 up here, suggesting 360 month paybacks. What's going to be the monthly payment? And ultimately what we're headed for here, is to create a giant table here showing what the monthly payment's going to be here.
And we can easily consider changing these amounts here, maybe if we're borrowing a car, maybe these'll be 20,000 to 30,000, something like that. And we can certainly change the number up here. Now, the feature we're about to use is called Data Table. Imagine we've got a giant grid set up here. We're headed for some answers. What we do first is put a master formula in the upper-left corner. Now, I don't really have to make the column wider, but I am going to zoom in and make the column wider, so we can see this clearly. So holding down the CTRL key, I'll use the mouse wheel to zoom in here, and make the column B wider temporarily, not truly necessary, but I wanna make sure this is large and clear.
The function we use here is called PMT. You might or might not be familiar with it. It's an easy to use function. Requires three arguments. The first one is the rate. Here's the yearly rate, 3%. Now, when you use this function, nearly always you will be dividing by 12. However, if this were a quarterly payment, we'd divide by four. If you paid only once a year, we'd simply put in B1, comma... The number of periods in the payback period. 360 months in our example here, so we use cell A1.
Comma... And the amount we're borrowing, let's say it's 200,000. Right here. And as I press enter, you'll see, we do get an answer, but it's negative. And it's not wrong. In fact, it is correct. This is money you would pay out every month, if you were to borrow $200,000, 3% annual interest, you wanna pay it back over 30 years every month. This is your monthly payment, principal and interest. However, it's going to be easier to work with the numbers, rather than having a bunch of negatives here, let's simply change the function.
I'll double click to edit, and put a minus in front of the B2. Now, this is our master formula, and although it relates to these two cells, ultimately we can make these numbers be anything we want. When I set this up, the 200,000 was the same as this. That's not a requirement. 3% same as that, not a requirement. But just easy to set up this way. Now, zooming back. Let's highlight all of these cells, including the row on top, and the column down the left-hand side.
We're about to use this feature found on the Data tab. It's in the group called Forecast, under What If analysis. Data Table... And you see the pop-up tip. See the results of multiple inputs at the same time. Now, we need a row input cell and a column input cell. There could be a little bit of confusion here if you're familiar with pivot tables. In a pivot table, down the left-hand side, the entries there are referred to as row labels, or the row area. But here when we say row input cell, we don't mean the numbers in column B, think of it as the numbers that are in row three here, the different percentages.
But in our formula that we wrote, the percent came out of cell B1. So B1 is analogous to the data that we see in row three. The column input cell relates to the data we're seeing in column B, and in our formula, we used cell B2 to pick up that number. Click OK. We've got a bunch of answers. Not formatted, so let's highlight all these cells here, and a quick format could be from the Home tab. Simply click the comma button. And I think you can make a case for saying here, do we really need to see the pennies? Now we are not changing the results if we don't show them, so I think there will be times when you want to see these, and other times not, but for a simpler look here, and easier to read, let's just use that feature called Decrease Decimals.
By the way, notice how that says show fewer decimal places. We are not changing the result. Although in some cases here, you'll see a rounded number, but the true value stays there. And we'll do that twice, and perhaps readjust the column widths at some point. Now, when we set up this list, this is the master formula. Notice on each cell here, as I click this cell, look in the formula bar. I click here. It says the same. We don't change these cells. This is a lot simpler than writing a formula, and copying it to all these cells, a formula that would involve mixed references on an absolute reference.
This has more flexibility. It's easier to set up. And if, for example, we really were looking for a house, say in the $300,000 range, we might make this be 300,000 right here, and for the moment, we'll see a change in the list to the right. But then make this one be 305,000. Something like that. Or any range you wish. Then maybe highlight both of these. We'll double click. Copy down the column. And now we see different amounts from 300,000 down to 355,000 using these different percentage amounts.
There's no requirement that says these must be equally spaced, but I think most often that's likely something you would want. And same thing here too. We're showing increments here every 1/8 of a percent, but you certainly have the ability to change this, and once the table has been set up, we don't have to worry about these formulas in here adjusting. It happens automatically. And if we wanted to change this to be a 15 year loan payoff, we'd change that 360 over in cell A1 to 180. And as soon as I press enter, all those numbers will change substantially.
And even though we used the master formula here, initially on the two cells above it in the cell A1, we could simply delete this cell, and delete this cell, and, although that looks a little funny, we could leave it there, or we could even apply a white font if we wanted to just to hide it because in a certain sense, we don't need to see it. So you've got some real flexibility here. So here we've got a tighter range than we had before, if we were borrowing money, say, for a car, maybe you wanna start at 25,000...
And then 26,000... Highlight both of those, and double click to copy that down the column. That'll take care of those pretty fast, and that probably would not be a 15 year loan. So if it's a five year loan, let's put in a 60. If it were a four year loan, we'd put in a 48. That number in the upper-left corner. So, here's the monthly payback if you borrow these different amounts here, ranging from 25,000 up to 36,000. We see the different percentages here. This is your monthly payment if the loan is for five years.
60 months. And adding just a little bit of pizazz here, but certainly not necessary, you could highlight these cells, and on the Home tab go over to Conditional Formatting, try some Color Scales. Maybe something like that. So we see an added visual here. So the feature's called Data Table. It's easier to set up than writing cumbersome formulas. We simply need to provide one master formula. In this case we used the PMT formula in the cell right here. So an easy way to create a large table here. And again, we could make this bigger, larger.
If we extended this farther, for example... I'm gonna go over to cell B14, 15. Highlight both of those. Drag this down to here maybe. 40,000... We could also do the same up above, but re-highlighting all of the data this way... Once again, we could go to the Data tab, and we're simply expanding it now. What If analysis, Data Table. Like before, row input cell was B1. Column input cell B2. OK. We got our answers that way, and then we'd have to make the adjustment on the format here.
Home tab... Comma, no decimals, and then reapply Conditional Formatting to all these cells. Probably best here to clear the rules from the selected cells, and then come back and apply it again using Color Scales. Either of those choices. And so, it's a great tool for providing a series of answers as we've seen in these examples.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros