Data Tables in Excel tutorial

Select A5:B10

In my last Excel Tutorial, I covered using SUMIFS and SUMPRODUCT.

Data Tables is also an advanced topic in Microsoft Excel that falls under the category of What-If Analysis. What-If or Sensitivity Analysis is carried out to study the variation of the output to changes in the input variable.

Consider a case of compound interest, where you invest a certain amount of money in a bank deposit and the amount is compounded every year.

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

Now, if suppose we want to see what the final amount will be at different interests rates, we can quickly use a data table for the same.


Excel Help describes a data table as:

A data table is a range of cells that shows how changing one or two variables in your formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet.

Without wasting any more time on descriptions, lets get down to creating a data table for our compound interest example above.

Open a new excel file and enter the following as given in the screenshot below:

The cell B5 has the formula =B1 * (1 + B2) ^ B3

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

Now, we will create a data table to see what amount we will receive by changing the interest rate.

Fill cells A6 to A10 with different interest rates. I’ve filled it with values from 6% to 10%. Now, select the cells A5:B10.

Select A5:B10

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 leave the Row Input Cell blank and hit OK.

Data Table prompt

The values will be filled in as shown below. You can choose to format them as currency, but that I have left it upto you.

How it works?

Well, we did get the results, but the question remains on what just happened.

To understand this simply, the Column Input Cell is the variable whose value we are going to vary. If supposing the data was presented horizontally instead of vertically which is our case, then you would need to select it as a Row Input Cell.

The values in cells A6 to A10 are then passed to the formula in cell B5 and the corresponding results are populated in cells B6 to B10.

If you look at cells B6 to B10, they contain the formula {=TABLE(,B2)}

Can you try creating a Data Table using a Row Input Cell? Why don’t you post your solution below or on your blog and post a link below in the comments area.

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

OneVariableDataTable

Download1862 downloads

3 Responses to “Data Tables in Excel tutorial”

  1. [...] 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 [...]

  2. itzik says:

    thanks for? the tutorial
    I tried to place the row / column input cell in a another sheet, it did not work.
    is it possible at all to work this tool when the row / column input cell in another sheet ?
    thanks again
    itzik

Leave a Reply

%d bloggers like this: