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

• Start a new spreadsheet
• Enter the same labels as in the image below
• Enter the same values for the Rate, Months, and Loan

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

• Click inside cell D2
• Click inside the formula bar
• Enter the following formula:

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

• Press the return key on your keyboard
• Excel should give you the answer £207.58

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.

• So in cells C3, C4, and C5 enter 8%, 7%, 6%
• Your spreadsheet should look like the one below

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.

• So click inside cell C2
• Highlight the cells down to D5
• Your spreadsheet should look like this one:

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:

• From the Excel menu bar, click on Data
• From the drop down menu, click on Table
• A small dialogue box appears like the one below:

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 click in side the "Column input cell" text box
• Enter B3 as the Input Cell
• Click the OK button
• Excel will work out the new monthly totals for you
• You should have the same values as in the image below:

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.