Two Variable Data Tables in Microsoft Excel Tutorial

Select Data > What-If Analysis > Data Table

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.

Select Data > What-If Analysis > Data Table

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.

Enter parameters for the Data Table

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.

TwoVariableDataTable

Download1629 downloads

8 Responses to “Two Variable Data Tables in Microsoft Excel Tutorial”

  1. [...] September 11 – Two Variable Data Tables in Microsoft Excel (Tutorial Thursday) [...]

  2. [...] Two Variable Data Tables in Microsoft Excel [...]

  3. [...] We created a One Variable Data Table. The next step is learning how to create a Two Variable Data Table. [...]

  4. Max says:

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

  5. uj says:

    I get duplicate values in the entire selected range when I try creating a data table.
    Please help. I tried with the same steps.

Leave a Reply

%d bloggers like this: