Excel’s data table feature is a what-if analysis tool that allows users to create tables (rows/columns) of results based on a one or two variable formula. Data tables are useful for simple calculations like future value tables or mortgage payment tables to examine different scenarios based on varying input assumptions. Our previous post examined array formulas with vlookup (Vlookup Array Formula Tutorial) and data tables are an extension of the discussion of array formulas.
Download the finished workbook here
In the following example we use a data table to show rates of return for an investment and then use the data table as the basis of an interactive chart using an offset function in an array formula.
The formula for Future Value is:
FV = PV * (1 + i) ^ t
Where:
FV means future value
PV means present value
i is the interest rate
t is time
To Create A Data Table
1. Set up variable inputs, optionally (recommended) name the cells to use in the formula;
2. To the right of the PV label, in cell B1, type a number like 1. In this example 1 means $1, our present value.
3. In a separate cell, create the formula that references the named ranges. In this example the formula appears in cell C4 which will become the upper left corner of the data table;
4. To the right and below the formula, add variable inputs;
5. Select the range to use for the data table, including the formula in the upper left corner;
In this example select cells C4:K24;
6. On the ribbon, select the Data tab > What If Analysis > Data Table command;
Select a row input cell (refer to a cell with a name); In this example, cell B2 is the row input (interest).
Select a column input cell (refer to a cell with a name); In this example, cell B3 is the column input (time).
Click the OK button to generate the data table.
Note: The formulas produced by the data table in cells D5:K24 are array formulas. Be aware that array formulas remove the ability to insert or delete a row or a column inside the data table.
To Create An Interactive Chart Based On The Data Table
1. In cell C26, type a 0 (zero);
2. Select (highlight) cells D26:K26;
3. Type an = (equals) sign;
Enter the following formula:
=OFFSET(D5:K5,C26,0)
To create an array formula, hold the Ctrl+Shift keys and press the Enter key.
The offset function offsets the range D5:K5 by the value in cell C26 (zero rows)
4. To add an interactive spinner control, show the Developer tab of the ribbon. To show the Developer tab, right click any tab of the ribbon, choose the Customize Ribbon… command. In the Customize Excel Options dialog, check the Developer tab.
5. On the Developer tab of the ribbon, select the Insert > Form Controls > Spinner command.
6. Click and drag a small spinner control in a cell to the left of cell C26.
7. Right click the spinner control, left click the Format Control command.
In the Format Control dialog:
Set the minimum value to 0
Set the maximum value to 20 (the number of years in column C)
Set the link cell to cell C26
8. The final step is to create the interactive chart.
Select cells D26:K26
On the ribbon, select the Insert tab > 2D Line Chart command
9. To see the chart become interactive, click the up arrow on the spinner control to increase the value in cell C26.
Note that as the value increases the offset function plots different rows of the data table in the chart.
Final note: I am obligated to give credit to Tushar Mehta for his excellent charting tutorials. Learn more about Tushar’s chart tutorials here: http://tushar-mehta.com/excel/charts/ In fact, I always encourage Excel students to study Tushar’s tutorials on: 3D Surface Charts, Normal Distribution (Bell Curves), Waterfall (Cash flows), and Values That Differ By Magnitude.