How to Use Microsoft Excel Tables

In Excel, a data Table is a way to see how altering the values in a formula effects the result. Excel will work out the new results for you, based on the new values you give it. Take the following as an example:

You decided to take out a loan of ten thousand pounds. You want to pay back the loan over 5 years. The first bank you try sets a interest rate of 9 percent per year. You use the PMT formula to work out how much you have to pay back every month:

=PMT(9% / 12, 12 * 5, -10000)

The formula gives you a figure of £207.58 per month.

However, another three banks are after your business. They are offering interest rates of 8 percent, 7 percent, and 6 percent. You can use the PMT function again to work out the monthly payments for these interest rates, or you could just use a cell reference for that 9% figure in the formula.

Another way to work out the monthly payments for the new interest rates is to use a Table. Excel will then use the PMT function, and the new interest rates, and work out the answers for you. We'll see how to do that now.

Excel Tables

So the Rate (interest rate) is 9 percent, the Months value is 60, and the Loan is 10, 000 pounds.

=PMT(B3 / 12, B4, -B5)

Just in case you're unsure about that PMT Function, here's what it's doing. The first argument for PMT is the rate, meaning the interest rate. The cell B3 is where we had our interest rate of 9 percent. We need to divide that by 12 (the number of months in a year), otherwise the bank will be charging us 9 percent a month! The second argument, where we have B4, is nper. This is just the total number of months in our loan. The third argument is how much we want to borrow. We have this amount in cell B5. It is a minus figure because it's a debt.

Now that we have a function in place, we can construct our Excel Table. First, we need to tell Excel about those other interest rates. It will use these to work out the new monthly payments. Remember, Excel is recalculating the PMT function. So it needs some new values to calculate with.

We have deliberately put the PMT function in cell D2. This is one Row up, and one Column to the right of our first new interest rate of 8%. The new monthly payments are going to go in cells D3 to D5. Excel needs you to set the table out this way.

So that Excel can work out the new totals, you have to highlight both the new values and the Function.

As you can see, the cells C2 to D5 are highlighted. This includes our new interest rate values, and our function in cell D2. We can now create a Table. So do this:

There's not much to fill in on that dialogue box. But the term "Input Cell" does need explaining.

The Input Cell is the cell that you want Excel to substitute. The thing we want Excel to substitute is the interest rate. We had our interest rate in cell B3. So we use this as the Input Cell.

We wanted Excel to fill downwards, down a column. So we need the second text box on the dialogue box "Column input cell". If we were filling across in rows, we would use the "Row input cell" text box.

So at an interest rate of 9 percent, we would be paying back just over two hundred and seven pounds. Excel has worked out that an interest rate of 8 percent will lower the monthly payments to just over two hundred and two pounds. At a 6 percent interest rate, the payments will be just over one hundred and ninety three pounds.

If you click inside cells D3, D4 and D5, then look at the formula bar, you will see this:

{=TABLE(,B3)}

That's Excel's way of telling you that a Table has been created.

We'll do one more Table. This time we'll use a more simple formula than PMT, and we'll use Rows instead of Columns. We'll do that in the next part.

Move on to another Excel Table example -->

<--Back to the Excel Contents Page