How to Use the Data Table Feature in Excel to Perform What-If Analysis

When building financial models, loan calculators, or forecasting spreadsheets in Excel, you often need to see how changing one or two variables impacts the final result. Instead of manually typing different numbers and recording the output, Excel’s built-in Data Table feature automates this process, allowing you to instantly generate a matrix of possible outcomes—known as What-If Analysis.

What is a Data Table?

A Data Table is a range of cells that shows how changing variables in your formulas affects the results of those formulas. There are two types:

• One-variable Data Table: Tests how changing a single variable affects one or more formulas.
• Two-variable Data Table: Tests how changing two different variables affects a single formula.

How to Create a Two-Variable Data Table

Imagine you are calculating a monthly mortgage payment. Your formula relies on the Interest Rate and the Loan Term. A two-variable data table will show you a grid of monthly payments across various rates and terms simultaneously.

Step 1: Set Up Your Base Calculation

1. In cell B1, enter the Loan Amount (e.g., 300000).
2. In cell B2, enter the base Interest Rate (e.g., 5%).
3. In cell B3, enter the base Loan Term in months (e.g., 360).
4. In cell B4, calculate the payment using the PMT function: =PMT(B2/12, B3, -B1). The result is your base monthly payment.

Step 2: Build the Table Grid

1. Select a new, empty area of the sheet. In the top-left cell of your grid (e.g., E2), reference your formula by typing: =B4.
2. Below that cell (E3 downwards), list your varying Interest Rates (e.g., 4%, 4.5%, 5%, 5.5%, 6%).
3. To the right of that cell (F2 onwards), list your varying Loan Terms (e.g., 180, 240, 360).

Step 3: Run the What-If Analysis

1. Highlight the entire grid, making sure to include the top-left formula cell, the row of terms, the column of rates, and all the empty space in between (e.g., select E2:H7).
2. Go to the Data tab on the Excel ribbon.
3. In the “Forecast” group, click What-If Analysis, then select Data Table….
4. A dialog box will appear asking for input cells:
   • Row input cell: Click on your original Loan Term cell (B3), because the loan terms run across the top row of your grid.
   • Column input cell: Click on your original Interest Rate cell (B2), because the interest rates run down the left column of your grid.
5. Click OK.

Excel will instantly populate the entire grid, calculating the exact monthly payment for every combination of interest rate and loan term. If you ever update the formula or the base loan amount in B1, the entire Data Table will recalculate dynamically, providing a powerful, instant forecasting tool.