How to Use Excel Data Tables for What-If Sensitivity Analysis

The Tedium of Sensitivity Analysis

If you build financial models, pricing calculators, or inventory forecasts in Microsoft Excel, you constantly face the “What If” question. You have built a beautiful spreadsheet calculating the monthly payment for a massive business loan based on a 5% interest rate. Your boss walks in and asks, “What if the interest rate climbs to 5.5%? What about 6%? What if we pay it off over 15 years instead of 20?”

The amateur response is to manually type 5.5% into the input cell, write down the new result on a scrap of paper, type 6%, write down that result, and then type the original 5% back in so you do not break the spreadsheet. If the boss asks for ten different scenarios across two different variables (interest rate and loan term), this manual “plug-and-chug” method takes hours and is highly prone to human error.

Excel has a built-in feature designed to instantly calculate and display all of these scenarios simultaneously without altering your original formulas: the Data Table.

Setting Up the Foundation

Before you can use a Data Table, you must have a working formula. For this example, imagine you have a simple loan calculator:

• Cell B1: Loan Amount (£100,000)
• Cell B2: Interest Rate (5%)
• Cell B3: Loan Term in Years (20)
• Cell B5: The PMT formula calculating the Monthly Payment based on those three variables.

The One-Variable Data Table

Let’s answer the boss’s first question: What happens to the monthly payment (Cell B5) if the interest rate (Cell B2) fluctuates between 3% and 7%?

Step 1: Build the Input Column

1. Find a blank area on your spreadsheet (e.g., Column D).
2. Starting in D2 and going down the column, type the different interest rates you want to test (e.g., 3%, 4%, 5%, 6%, 7%).

Step 2: Connect the Result

The Data Table needs to know which formula it is supposed to be testing.

1. Click the cell exactly one row above and one column to the right of your first input (Cell E1 in this example).
2. Type an equals sign = and click your original monthly payment formula cell (B5). Press Enter. Cell E1 now displays the current monthly payment.

Step 3: Run the Data Table

1. Highlight the entire grid containing your inputs and the formula cell (highlight D1 down to E6).
2. Go to the Data tab on the Excel ribbon.
3. In the “Forecast” group, click What-If Analysis and select Data Table….
4. A small dialogue box appears asking for a Row Input Cell and a Column Input Cell. Because you typed your test variables (3%, 4%, etc.) in a vertical column, click inside the Column input cell box.
5. Click the original Interest Rate cell in your model (Cell B2). You are telling Excel, “Take the column of test numbers I highlighted, and plug them one by one into Cell B2.”
6. Click OK.

Instantly, Excel calculates the monthly payment for every single interest rate and populates the empty cells next to your inputs. If you change your original loan amount from £100,000 to £200,000, the entire Data Table will instantly recalculate every scenario.

The Two-Variable Data Table

What if the boss wants to see the impact of changing the Interest Rate and the Loan Term simultaneously? A one-variable table cannot do this, but a two-variable table can.

Step 1: Build the Grid

1. Find a blank area. This time, type your test Interest Rates down a column (e.g., D3 down to D7).
2. Type your test Loan Terms across a row, starting one cell to the right and one cell up from your first interest rate (e.g., type 10, 15, 20, 25 in cells E2, F2, G2, H2).

Step 2: Connect the Result

The formula link must go in the very top-left corner of the grid, where the row and column intersect (Cell D2 in this example).

1. Click D2, type =, and click your original Monthly Payment formula (Cell B5).

Step 3: Run the Table

1. Highlight the entire grid (D2 down to H7).
2. Click Data > What-If Analysis > Data Table….
3. Row input cell: Click your original Loan Term cell (B3), because your test terms are written across a row.
4. Column input cell: Click your original Interest Rate cell (B2), because your test rates are written down a column.
5. Click OK.

Excel instantly generates a massive matrix, showing the exact monthly payment for every possible combination of interest rate and loan term.

Conclusion

Excel Data Tables eliminate the need to manually recalculate complex scenarios. By spending two minutes setting up a dynamic sensitivity grid, you can instantly answer any “What If” question your boss throws at you, transforming a static calculator into a powerful forecasting tool.