# Excel’s What-If Analysis: Data Table Tool

Have you ever used Excel’s “What-If Analysis: Data Table” feature? Have you ever heard of it?

If not, by the end of this post you may realize that it is the exact Excel feature you need that will replace many formulas when performing complicated data analysis.

Continue reading to lift the veil of this mysterious tool.

Let’s look at an example where we’re trying to determine how much we can invest in a monthly retirement account to achieve a particular return over a set number of years.

The following are the arguments we’ll use in our analysis:

**-$200**– this is how much money we’ll invest in the retirement account each month.**20**– this is the number of years till we reach retirement age**4%**– this is the annual interest rate

To calculate the amount of money accrued at the end of the 20 years, we can use Excel’s Future Value function (**FV**).

The **FV** function calculates the __future value__ of an investment based on a constant interest rate. You can use **FV** with either periodic, constant payments, or a single lump sum payment.

The syntax for the FV function is as follows:

FV( rate, nper, pmt, [pv], [type] )

**rate**– (*required*) The interest rate per period.**nper**– (*required*) The total number of payment periods in an annuity.**pmt**– (*required*) The payment made each period; it cannot change over the life of the annuity. Typically,**pmt**contains principal and interest but no other fees or taxes. If**pmt**is omitted, you must include the**pv****pv**– (*optional*) The present value, or the lump-sum amount that a series of future payments is worth right now. If**pv**is omitted, it is assumed to be 0 (zero), and you must include the**pmt****type**– (*optional*) The number 0 (*end of the period*) or 1 (*beginning of the period*) indicates when payments are due. If**type**is omitted, it is assumed to be 0.

For our example, the formula would appear as follows:

=FV(B5/12, B4*12, B3)

Notice that in the **rate** argument, we must divide the 4% (*cell B5*) by 12 because we are paying monthly whereas the

**rate**argument defaults to a yearly period.

The same adjustment must be made to the **nper** argument for the same reason, multiplying the years (*cell B4*) by 12.

The monthly payment (*cell B3*) is a negative value because it is a cash outflow. We’re taking money out of the account.

# Playing the “What If” Game

Suppose we want to see how much money we have at the end of 20 years using different interest rates. We could repeatedly change the “**Return p.a.**” cell (** B7**) and try to remember what we saw at each change in the percentage, or…

…we can use the amazing **What-If Analysis: Data Table** feature.

All we need to do is build our logic prototype as we have here, then use **What-If Analysis** to build a matrix of calculations for each interest rate we are interested in.

Begin by creating a list of interest rates you wish to calculate against.

Next, select the cell to the right of the list of interest rates that is 1 row above the first listed rate.

This cell will hold the prototype formula we created earlier in cell **B7**.

Since we have already created the **FV** formula, we will simply reference it in this cell.

=B7

The idea is that we want to repeat the prototype **FV** formula down this column, swapping out the percentage rate at each row with the rate listed on that same row.

Instead of creating these long, complicated formulas that use a mixture of relative and absolute references (*we know how much everyone enjoys those*), we can perform the following steps:

- Highlight the range of cells that encompasses the prototype formula, the listed interest rates, and the cells that will display the results (
*cells*).**F2**through**G19** - Select
**Data (tab) -> Forecast (group) -> What-If Analysis -> Data Table**.

In the **Data Table** dialog box, we must decide whether to use the **Row Input Cell** or **Column Input Cell** as the changing range.

As our interest rates are listed in a column, we will populate the **Column Input Cell** field.

What we place in that field is a reference to the address of the cell that holds the original percentage rate used by the prototype formula, cell **B5**.

Click **OK** and we have our table of results for each listed interest rate.

The feature essentially says, “For every reference of cell **B5** in the prototype formula, replace it with the value(s) of the cells in the left **column** of the highlighted range.”

# PRO TIP: Customizing the Prototype Formula

One thing that can be a bit confusing for viewers of this table is the repeated result of the prototype formula located at the top of the table.

One way to camouflage this formula is to display it as an icon. Perform the following steps:

- Select the cell that holds the prototype formula (
*cell*).**G2** - Press
**CTRL-1**to open the**Number Formatting** - Select the
**Custom**category (*left*) and erase everything displayed in the**Type:** - Press
**Windows-Period**to open the Emoji library. - Select an emoji, perhaps something like a bag of money, and press
**OK**. - Consider changing the font color of the cell to make it more interesting.

# Working With Multiple Arguments

Let’s take this example to a higher level. Suppose we want to show all combinations of the listed percentages against a list of years.

- Move the prototype formula to the cell directly above the first listed interest rate (
*in this example, cell*).**F2** - Create a list of years that will reside across the top of the results matrix (
*in this example, cells*).**G2**through**M2** - Select all cells that contain the prototype formula, interest rates, years, and result cells (
*F2**through*).**M19**

- Select
**Data (tab) -> Forecast (group) -> What-If Analysis -> Data Table**.

In the **Data Table** dialog box, the **Column Input Cell** is the same as before (*cell B5*), and the

**Row Input Cell**is set to cell

**B4**(

*this is the cell reference in the prototype formula that will be changed with each iteration through the top row of listed years.*)

# Beware of Performance Issues

If you are using the What-If Analysis feature on tables with hundreds of columns and/or thousands of rows, you may encounter a slowdown in performance. This is because the entire data table must be recalculated each time an element in the table is changed.

If you are making several changes, you can hold off on updating the table until all the changes have been made. To do this, we need to adjust the way Excel responds to sheet modifications.

Select **Formulas (tab) -> Calculation (group) -> Calculation Options** and change this from **Automatic** to **Automatic Except for Data Tables**.

Once you have made the needed changes to the table, you can manually update the calculations by pressing the **F9** key.

# Conclusion

The What-If Analysis: Data Table feature isn’t exactly the most intuitive feature in Excel’s arsenal. It takes a few uses to get the hang of it. But once you’ve used it a few times, it’s actually not that bad, and it can be one of the most useful skills that will easily impress those around you.

## Practice Workbook

Feel free to Download the Workbook HERE.