Three weeks back I covered creating Single Variable Data Tables in Excel. As promised, in this tutorial I will cover creating a two variable data table. This tutorial assumes that you have read the earlier one and are comfortable with creating a single variable data table.

Create a new excel file as shown in the screenshot above. In cell A5, enter the formula `=B1 * (1 + B2) ^ B3`

.

As you can see, the $5000 invested at 7.5% for 5 years will give $7,178.15.

A two variable data table gives you results by varying two different variables in the equation. In our case, the equation is that of compound interest i.e.

Formula for calculating compound interest:

A = P * (1 + r/n) ^ nt

Where:

- P = principal amount (initial investment)
- r = annual interest rate (as a decimal)
- n = number of times the interest is compounded per year
- t = number of years
- A = amount after time t

We will be varying the annual interest rate and the number of years to find the varying results.

After you have created the file according to the screenshot above, select the data range **A5:F10**.

In Excel 2007, goto **Data > What-If Analysis > Data Table**. In Excel 2003, the menu path is **Data > Table** or you can use the shortcut key *Alt + D + T* in this order.

This will popup a window where you will be asked to enter Row Input Cell and the Column Input Cell. Select the Column Input Cell as **$B$2** and the Row Input Cell as **$B$3** and hit OK.

The cells will be populated as shown in the screenshot below. As usual, you can choose to format the same currency.

### How it works?

Cells B6:F10 hold the formula `{=TABLE(B3,B2)}`

.

Here, the values B4 to F4 are substituted for B3 in the formula in cell A5 and values A6 to A10 are substituted for B2 in the formula in cell A5 and the results are then populated accordingly.

Hence, for 1 year at 8%, the amount is $5,400, for 3 years at 10%, the amount is $6,655.00 and so on.

You can download the sample XLS file for your reference.

Two Variable Data Tables example (2454 downloads)
Pingback: Summary Sunday: Sep 08, 2008 - Sep 13, 2008 » Techtites

Pingback: Best of Techtites 2008 » Techtites

Pingback: Data Tables in Excel (Tutorial Thursday) | Techtites

MaxIt’s great example, and what about 3 columns?

AjayExcel doesn’t support this as far as I am aware

Akram AyyubiIts a good example. Watch a video on data table at the below link

ujI get duplicate values in the entire selected range when I try creating a data table.

Please help. I tried with the same steps.

AjayDoes it work if you hit f9