A Data Table in Excel 2010 lets you expand on a formula to show the different results for different values of one of the parts within the formula. You can list the different values for that part down a column and show in a separate column the formula results corresponding to each value of the input data column.

This lets you easily compare the results of a formula for different input values without having to edit the formula over and over again. And because the input values can be arranged in a single vertical column the data table is called a one input data table. You can also use this same principal to showing two sets of values at the same time in a two input data table.

Create a One Input Data Table

As an example suppose you want to take out a mortgage of £100,000 over 20 years. The interest rate you are quoted is 4% and you ask what the monthly repayment will be. The mortgage broker quotes you a repayment of £606 which you feel you cannot afford. So you ask for a fresh quote based on 25 years, and the quote is £528 which you feel you can just manage.

The mortgage broker may be using the Payment function in Excel 2010 which looks like =PMT(interest per month, timescale in months, loan amount) and he or she can apply different periods to show different results. However it is often useful to be able to see all the different payments for different timescales all at once and this can be done in a data table.

To do this we'll first open a new workbook in Excel 2010 and in cell D4 type in the interest rate 4%. Then in cell D5 we enter timescale as 20 and in cell D6 enter the loan amount £100,000. We'll put the monthly payment calculation in cell G4 where we enter the formula =-PMT(D4/12,D5*12, D6) and press Enter. The result shows as £605.98.

Now we want to enter different values for the number of years and we do this in cell F5, F6, F7 and so on downwards with a different value in each cell. In our example we enter 10 in cell F5, 15 in cell F6, 20 in cell F7, 25 in cell F8 and 30 in cell F9. Now we create the One Input Data Table.

To do this first select cells F4 to G9. These are the two columns with the list of timescales and the empty column next to it with the formula in the top cell. Then choose the Data Tab and click on the down arrow on the What If Analysis command button and select Data Table. In the Data Table panel we need to tell Excel that the column data (of years) is to be used in the original formula based on cell D5, so in the Column Input cell type D5, then click OK to complete the one input data table.

The table shows all the different years we entered in the column under F5 and alongside each the corresponding payment amount. This is a one input table.

Create a Two Input Data Table

Suppose you want to vary the amount you want to borrow as well as the loan term, and you want to see all the possible combinations of repayments? You can do this in a two input Data Table.

As before we open a new workbook in Excel 2010 and in cell D4 enter 4%, in cell D5 we enter 20 and in cell D6 enter £100,000. This time we'll put the monthly payment calculation in cell F4, not G4. So in F4 we enter =-PMT(D4/12,D5*12, D6) and press Enter. The result still shows as £605.98.

To allow Excel to show the formula results for two sets of different criteria we add the first in a column directly under the formula and the other in a row directly right of the formula. So this time we enter the years in cells F5 to F9. Then in cells G4 to J4 we enter a series of loan amounts in cells across the sheet, for example £50,000, £75,000, £100,000 and £125,000.

To create the two input data table we first select cells F4 to J9, i.e. all the cells from the vertical data and horizontal data, with the formula at the intersect. Then in the Data tab choose What If Analysis, Data Table. In the data table panel enter D5 into the column input cell and D6 into the row input cell, then click OK to create the two input data table.

The table shows all the different years we entered in the column under F5 and all the different loan amounts across from G4. At each intersect you'll see the corresponding payment amount. This is a two input table.

To summarise, the difference between a one input and a two input data table is the positioning of the formula over the data to be varied. In a one input table the formula is directly above the column of data and in a two input table the formula is above and one cell to the right of the column data with the row data to the immediate right of the formula.

Clear as daylight? The best way to really get to grips with Excel 2010 and its many very useful features such as data tables would be to attend an instructor lead training course. That way you can really boost your Excel skills in a short time.