**One-variable Data Table:** You can use one-variable data table to see how different values in a single cell in one or more formulas affect the results of those formulas. For example, you can use one-variable data table if you want to see how much you have to repay for a loan if the interest rate is 2%, 3%. 4%, etc. for a given period of time (note: only the interest rate is changing in this case).

Exclusive Holiday Season Exclusive Offer: Save 30% on Your First Year of SugarSync!

**Two-variable Data Table:** You can use two-variable data table to see how different values in two cells in a formula will affect the result of that formula. For example, to see the monthly repayment for a loan if the interest rate (2%, 3%. 4%, etc.) and the loan terms ( 5, 10, 15 years, etc.) are changing (note: in this case, both interest rate and the loan terms are changing).

## How to Create a One-Variable Data Table

You can create a one-variable data table in a worksheet either for a row input variable or for a column input variable. For the purpose of this tutorial, I use a Morgage Analysis worksheet which can be downloaded from here.

### Step 1:

Type out the values and formulas to calculate the repayment/month for the loan payment as shown in the example given. Leave enough empty columns for you to create the data table. You can use Financial function PMT to calculate the monthly repayment amount (Formulas tab | Function Library group | Financial | PMT) - [=PMT(C6/12,C7,-C5)]

Once the formula for the worksheet is done correctly, you can create a data table to see how changes in interest rate will affect the monthly repayment. To do that;

### Step 2:

Type out the range of interest rates that you want to calculate the repayment amount for.

### Step 3:

Select the range of cells, including the calculated field showing the monthly repayment amount. In this example, cell range from B10 to C23.

### Step 4:

Click on Data tab | Data Tools group | What-If Analysis | Data Table

### Step 5:

Since the changing cell (input cell, here the Interest Rate, cell $C$6) is in column, enter $C$6 in "Column input cell" field and click OK.

This will create a one-variable data table showing the monthly repayment amount for different interest rate, varying from 2.00% to 5.00%.

## How to Create a Two-Variable Data Table

You can create a two-variable data table in a worksheet with a row input variable and a column input variable. For the purpose of this tutorial, I use the same Morgage Analysis worksheet used in the one-variable data table demo. In this case, we will have two variables, one is the varying interest rate (from 2% to 5%) and the other is the loan term, varying from 10 years to 40 years with an increment of 5 years.

### Step 1:

Type out the values and formulas to calculate the repayment/month for the loan payment as shown in the example given. Leave enough empty columns for you to create the data table. You can use Financial function PMT to calculate the monthly repayment amount (Formulas tab | Function Library group | Financial | PMT) - [=PMT(C6/12,C7,-C5)]

### Step 2:

Type out the range of interest rates and the loan terms that you want to calculate the repayment amount for.

### Step 3:

Select the range of cells, including the calculated field showing the monthly repayment amount. In this example, cell range from B10 to I23.

### Step 4:

Click on Data tab | Data Tools group | What-If Analysis | Data Table

### Step 5:

Since there are two changing cells (one in the row and the other in the column), you need to enter both the variables. In the "Row input cell" field, enter $C$7 and the "Column input cell" field, enter $C$6. Then click OK.

This will create a two-variable data table showing the monthly repayment amount for different interest rate (varying from 2.00% to 5.00%) and for different loan terms (varying from 10 years to 40 years).

You cannot delete only one value in a data table, if you want to delete, you have to delete the entire data table. In the given case, you have to delete values in cell range from C11 to I23.

Watch this video to see how a Data Table is created:

Do you find this information useful? Why don't you tell your friends by sharing it on Facebook, Google+ or Twitter. You can also follow me on Twitter** @sarayoo.info** or **Google+** or Like me on my **Facebook** for more updates, **technology tips** and **tricks**, **iPhone, iPad, other iOS devices tips**, **iOS App Deals**,** Blogging tips**, etc. Please leave your comments in the comment section or **contact me** if you have any other questions.