How to Use Data Table in Excel What-If Analysis

Planning for the future often involves asking a lot of "what if" questions. What if our sales increase by 15%? What if our project costs are 10% higher? What if we change the interest rate on a loan? Answering these manually means creating dozens of different scenarios in your spreadsheet, but Excel has a much smarter way to handle this. This guide will walk you through setting up and using a Data Table, one of Excel's most powerful What-If Analysis tools for seeing a wide range of outcomes instantly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is What-If Analysis in Excel?

What-If Analysis is a set of tools in Excel that allows you to experiment with your data by changing certain values in your cells to see how those changes affect the outcome of formulas. Instead of creating a dozen different copies of your financial model to test different assumptions, you can use these tools to see multiple results in one central place. Excel provides three main tools under the What-If Analysis umbrella, found in the Data tab:

• Scenario Manager: Lets you create and save different groups of values (scenarios) and switch between them. It's great for comparing a "best case," "worst case," and "likely case" side-by-side.
• Goal Seek: Works backward. If you know the result you want from a formula, Goal Seek can figure out what input value will get you there. For example, "What interest rate do I need to get my monthly loan payment down to $500?"
• Data Tables: Lets you see the results of one formula when you substitute multiple different values for one or two variables. This is the perfect tool for sensitivity analysis and is the focus of this tutorial.

Understanding Excel Data Tables

At its core, a Data Table is a feature that calculates a range of results based on one or two changing variables in a formula. It takes a complete calculation (like a loan payment formula) and reruns it with all the possible inputs you provide, displaying the outcomes in a neat table. This saves you from the mind-numbing task of copying your formula over and over and manually plugging in different numbers.

There are two types of Data Tables you can create:

1. One-Variable Data Table: Use this when you want to see how changing one input variable affects one or more formulas. For example, how do different interest rates affect a monthly loan payment?
2. Two-Variable Data Table: Use this when you want to see how changing two different input variables affects a single formula. For example, how do different interest rates and different loan terms affect a monthly loan payment?

Let's walk through how to build both.

How to Use a One-Variable Data Table in Excel

To understand the process, let's work with a practical example: calculating the monthly payment for a business loan. We want to see how different interest rates will impact our monthly payment.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Set Up Your Initial Data and Formula

First, set up a small reference table with your inputs and the primary formula. This area will be the engine for the Data Table.

• In cell B1, enter the Loan Amount (e.g., $500,000).
• In cell B2, enter the original Annual Interest Rate (e.g., 5.0%). This is the key cell we will be substituting.
• In cell B3, enter the Term in Years (e.g., 30).

Now, in another cell (say, B5), enter the PMT formula to calculate the monthly payment. The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.

The syntax is PMT(rate, nper, pv):

• rate: The interest rate per period. Since our rate is annual and payments are monthly, we'll use B2/12.
• nper: The total number of payments. Since our term is in years, we'll use B3*12.
• pv: The present value, or the total amount that a series of future payments is worth now, in other words, the loan amount. We'll use B1.

So, your formula in cell B5 will be: =PMT(B2/12, B3*12, B1)

Excel will display the result as a negative number (e.g., -$2,684.11) because it represents a payment, or cash outflow. If you prefer to see it as a positive number, simply put a minus sign before the formula: =-PMT(B2/12, B3*12, B1).

Step 2: Create a Column of Variable Inputs

Now, set up the area for the Data Table itself. A couple of columns away from your main data, create a column with all the different interest rates you want to test. For this example, let's list rates from 4.0% to 6.0% in column D (from D2 to D12).

Step 3: Link Your Formula to the Table

In the cell directly above and to the right of your variable values (in this case, cell E1), you need to reference your original formula. Don't re-type the formula, just link to the cell that contains it.

In cell E1, type: =B5

This tells the Data Table which final calculation it needs to run for each variable.

Step 4: Select Your Range and Create the Data Table

Now, highlight the entire range that makes up your table—the column of variables and the column where the results will go, including the linked formula cell at the top (D1:E12).

Next, navigate to the Data tab on the Ribbon, click on What-If Analysis, and select Data Table...

Step 5: Fill in the Data Table Dialog Box

A small dialog box will pop up. Because our variables (the interest rates) are in a column, we need to fill in the "Column input cell" field. This is where you tell Excel which cell in your original formula should be replaced by the values from the column.

Our column variables are the interest rates, and the original interest rate in our setup is in cell B2. So, click in the "Column input cell" box and select cell B2.

Click OK.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 6: Analyze the Results

Excel instantly fills the column next to your variables with the corresponding monthly payments. You now have a clear view of how each increment in the interest rate affects the payment, all without changing your original formula.

The numbers in the result column (E2:E12) are generated by a special array formula: {=TABLE(,B2)}. You can't edit or delete an individual cell's result, if you need to make changes, you have to clear the entire array of results (but not the top formula cell) and rerun the tool.

How to Use a Two-Variable Data Table in Excel

What if you want to see how both the interest rate and the loan term affect your monthly payment? This is where a two-variable data table comes in handy. It allows you to analyze the impact of two inputs on one single formula.

Step 1: Start with the Same Initial Setup

We'll use the same base data and formula from our first example: the loan information in cells B1:B3 and the PMT formula in cell B5.

Step 2: Set Up a Matrix of Row and Column Variables

Find some empty space on your worksheet. This time, you'll need a column for one variable and a row for the other.

• Let's use the column for the interest rates again (e.g., 4.0% to 6.0% in cells D3:D13).
• Now, create a row for the second variable: the loan term in years (e.g., 15, 20, 25, 30 in cells E2:H2).

Step 3: Link the Formula in the Corner

For a two-variable table, the link to your original formula must be in the top-left corner of the matrix, at the intersection of your row and column variables. In our example, this is cell D2.

In cell D2, type: =B5

Step 4: Select Your Range and Open the Data Table Tool

Select the entire matrix, including the formula in the corner and both the row and column headers with your variables (D2:H13). Then go to Data > What-If Analysis > Data Table...

Step 5: Fill in Both Input Cell Fields

The dialog box now requires both fields to be filled out:

• Row input cell: Tell Excel where the original input for your row variables (loan terms) is. Our row variables are the loan terms, originally in cell B3. So, select cell B3.
• Column input cell: Tell Excel where the original input for your column variables (interest rates) is. Our column variables are in cell B2. So, select cell B2.

Click OK.

Step 6: Interpret the Matrix of Outcomes

Excel will instantly populate the entire matrix with monthly payments corresponding to each combination of interest rate and loan term. You can now easily compare scenarios. For instance, you can cross-reference an interest rate of 4.5% with a 20-year term to find the exact monthly payment for that specific scenario.

Practical Tips and Common Issues

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Keep Your Base Model Clear

Always keep your original calculations (the loan data in our example) separate and visible. The Data Table relies on these cells, so changing a value in the base model (like the loan amount) will automatically update the entire analysis table.

Recalculation Speed

Data Tables are live. If any data on the sheet changes, Excel will recalculate the entire table. For large tables with very complex formulas, this can sometimes slow down your computer's performance. If you have many large tables, you can set calculation to "Automatic Except for Data Tables" in File > Options > Formulas.

Cannot Change Part of a Data Table

As mentioned, the results are an array. If you try to delete or edit a single cell within the generated results, Excel will give you a "Cannot change part of a data table" error. To remove the results, you need to select all result cells and press 'Delete'.

Troubleshooting: Why do all my results show the same value?

This is the most common issue new users face. It almost always means you've selected the wrong cell in the "Row input" or "Column input" fields. Double-check that you are pointing to the original input cells (B2 and B3 in our example) that your main formula in B5 actually depends on. Don't select a cell that is part of the Data Table itself.

Final Thoughts

Excel's Data Tables are a fantastic tool for performing sensitivity analysis without the pain of manual calculations. By mastering one-variable and two-variable tables, you give yourself the ability to quickly evaluate ranges of outcomes, make more informed decisions, and answer any "what-if" question about your data a stakeholder could throw at you.

Once your analysis grows beyond a single spreadsheet, wrangling data from platforms like Shopify, Salesforce, or Google Analytics becomes the next bottleneck. At Graphed, we've focused on eliminating that manual work. You just connect your data sources, describe the analysis you need in plain English using our chatbot, and Graphed builds a live, interactive dashboard for you in seconds—letting you ask business-critical questions without ever exporting another CSV file again.