How to Create a Data Table for What-If Analysis in Excel

Microsoft Excel’s Data Table feature is a powerful tool for performing what-if analysis, allowing you to test various scenarios and see the outcomes based on different input values. Whether you’re evaluating loans, investments, or product pricing with changing variables, Data Tables help you organize and visualize the results in a structured table. Let’s walk through how to create both one-variable and two-variable data tables for scenario analysis.

Create a One-Variable Data Table

A one-variable data table allows you to see how a single changing value affects your results. For example, you can test how different interest rates impact the future value of an investment.

Steps to Create a One-Variable Data Table:

1. Set Up Your Formula:
   • In our example, we calculate the future value of an investment using the FV function, with constants for the annual interest rate, number of payments, and payment amount.
2. Input Values:
   • Enter the different interest rates in a column (or row, if you prefer).
3. Place the Formula:
   • If your values are in a column, place the formula one cell above and to the right of the first value (in our example, we place the formula in column D, one row above the interest rates in column C).
4. Select Data:
   • Select the cells containing the formula, values, and expected outputs (for example, C2 to D6).
5. Create Data Table:
   • Go to the Data tab and click the What-If Analysis drop-down.
   • Choose Data Table.
6. Configure Data Table:
   • In the Data Table window, enter the cell reference for the changing variable. In our case, the interest rate is in B3, so we enter B3 in the Column Input Cell field.
7. View Results:
   • Click OK, and Excel will populate the table with the future values for each interest rate.

You will now see the future value of your investment for each interest rate you entered, all in one neat table.

Create a Two-Variable Data Table

If you’d like to analyze how two variables impact your results, you can create a two-variable data table. For instance, in addition to testing how different interest rates affect the future value, you can also change the number of payments to get a more detailed analysis.

Steps to Create a Two-Variable Data Table:

1. Set Up Your Formula:
   • As before, calculate the future value of an investment using the FV function.
2. Input Values:
   • Enter the different interest rates in a column (column C) and the number of payments in a row (row 2, starting at D2).
3. Place the Formula:
   • Place the formula directly above the column of interest rates and to the left of the row of payment values.
4. Select Data:
   • Select the cells containing the formula, values, and expected outputs (for example, C2 to F6).
5. Create Data Table:
   • Go to the Data tab and click What-If Analysis > Data Table.
6. Configure Data Table:
   • In the Data Table window, enter both the row and column input cell references. In this case:
     • Enter B3 (interest rate) for the Column Input Cell.
     • Enter B4 (number of payments) for the Row Input Cell.
7. View Results:
   • Click OK, and Excel will generate the table showing the future values for different interest rates and varying numbers of payments.

This two-variable data table provides a complete view of how both interest rate and number of payments influence the future value of your investment.

Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!