How to Create a What-If Data Table in Excel

Ever find yourself in a spreadsheet, manually tweaking a couple of numbers over and over just to see how they impact your final result? It's a tedious process of guess-and-test that eats up valuable time. Excel’s What-If Analysis tools are designed to solve this exact problem, and the Data Table feature is one of the most practical tools in the kit. This guide will walk you through exactly how to build both one-variable and two-variable data tables to automate your scenario planning and make better-informed decisions.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is a What-If Data Table?

A Data Table in Excel is a powerful feature that allows you to see the results of a single formula when one or two of its input variables change. Instead of typing in a new interest rate 20 times to see how it affects your monthly loan payment, you can create a table that calculates all 20 outcomes for you instantly. This process is often called sensitivity analysis because it shows how sensitive a result is to changes in your inputs.

There are two types of data tables you can create:

• One-Variable Data Table: Use this when you want to see how changes in one input variable affect a formula. For example, how does changing your marketing budget impact projected revenue?
• Two-Variable Data Table: Use this when you want to see how changes in two separate input variables affect a formula. For example, how do changes in both the price of a product and the number of units sold impact total profit?

The beauty of data tables is their simplicity and speed. You set up a model once, define your variables, and Excel does all the repetitive calculations in a fraction of a second.

Before You Begin: Set Up Your Base Model

A data table can’t work without an existing model to analyze. Before you can build the table, you first need a simple setup on your worksheet with your inputs and the primary formula you want to test. Pro-tip: Keep your base model organized and clearly labeled. This makes it much easier to link everything correctly later on.

Let's use a common and easy-to-understand example: a simple loan payment calculator. Here’s what our basic model will look like:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Define Your Inputs

First, set up cells for your core variables. These are the numbers you would normally change manually.

• In cell B2, type "Loan Amount" and put $400,000 in cell C2.
• In cell B3, type "Annual Interest Rate" and put 6.5% in cell C3.
• In cell B4, type "Loan Term (Years)" and put 30 in cell C4.

Step 2: Create Your Output Formula

Now, create the formula that will be the centerpiece of our analysis. We want to calculate the monthly loan payment based on our inputs. Excel's PMT function is perfect for this.

In cell B6, type "Monthly Payment". In cell C6, enter the following formula:

=PMT(C3/12, C4*12, C2)

Here’s a quick breakdown of that formula:

• C3/12: This is the rate. Since our interest rate in C3 is annual, we divide it by 12 to get the monthly interest rate.
• C4*12: This is the nper (number of periods). Our term in C4 is in years, so we multiply it by 12 to get the total number of monthly payments.
• C2: This is the pv (present value), which is simply the total loan amount.

Excel will show the result as a negative number (e.g., -2,528.23) because it represents a cash outflow or payment. You can wrap the formula in an ABS() function like =ABS(PMT(C3/12, C4*12, C2)) if you prefer to see a positive number. Now that our model is set up, we're ready to build our data tables.

How to Create a One-Variable Data Table Step-by-Step

Let's say we want to see how different interest rates affect our monthly payment while the loan amount and term stay the same. Here, the "Annual Interest Rate" is our single variable.

Step 1: Structure Your Table Axis

First, you need to create the structure for your table somewhere else on the sheet.

1. List Your Variable Values: In a single column, list all the different interest rates you want to test. For our example, let's enter interest rates from 5.0% to 8.0% in cells F8 through F14.
2. Link the Formula: This is the most important - and often missed - step. In the cell that is one row above and one column to the right of your list of variables (cell G7 in our case), you must reference your original formula. Do this by typing =C6 into cell G7. This tells the Data Table what result it needs to calculate.

Your structure should look something like this:

• Column F (F8:F14): Your list of interest rates.
• Cell G7: A reference to your final calculation (=C6).

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Launch the Data Table Tool

1. Select your entire table range, including both your list of variables and the formula reference cell. In our example, highlight the range F7:G14.
2. Navigate to the Data tab on the Ribbon.
3. In the Forecast group, click on What-If Analysis.
4. From the dropdown menu, select Data Table....

Step 3: Configure the Data Table Inputs

A small dialog box will pop up with two input fields: "Row input cell" and "Column input cell." This is where you tell Excel which input from your original model should be replaced.

• Since our list of test variables (the interest rates) is arranged in a column, we will use the "Column input cell" field.
• Click in the "Column input cell" box, and then click on cell C3, which is the original "Annual Interest Rate" cell in our base model. This tells Excel: "Take each value from column F, plug it into cell C3 one by one, and record the output from the formula in C6."
• Leave the "Row input cell" blank.

Step 4: See Your Results

Click OK. Instantly, Excel fills in the column next to your list of interest rates with the corresponding monthly payment for each rate. You now have a complete sensitivity analysis showing exactly how a change in the interest rate impacts your monthly payment.

How to Create a Two-Variable Data Table Step-by-Step

Now, let's get a bit more advanced. What if we want to see how the monthly payment changes based on both the Annual Interest Rate and the Loan Term? This calls for a two-variable data table.

Step 1: Set Up Your Grid Structure

A two-variable table is structured as a grid with variables running down the first column and across the top row.

1. Create the Column Variable: Just like before, list one set of your variables in a column. Let’s use the interest rates again in cells F8:F14.
2. Create the Row Variable: Across the top row of your table structure, list the values for your second variable. Let's test loan terms of 15, 20, 25, and 30 years in cells G7:J7.
3. Link the Formula (Crucial!): For a two-variable table, the formula reference must go in the very top-left corner cell where the row and column of variables intersect. In our example, this is cell F7. In cell F7, enter =C6 to link it back to our original Monthly Payment formula.

Step 2: Select the Range and Launch the Tool

1. Highlight your entire grid, which includes both sets of variables and the formula reference cell. In this case, select the range F7:J14.
2. Go back to the Data tab, click What-If Analysis, and choose Data Table... once again.

Step 3: Configure Both Input Cells

This time, you'll need to fill in both fields in the dialog box.

• Row input cell: The variables in your top row are the Loan Terms. So, for this field, click on the original Loan Term input cell in our model, which is C4.
• Column input cell: The variables in your first column are the Interest Rates. For this field, click on the original Annual Interest Rate cell, which is C3.

In simple terms, you are telling Excel: "For each combination, take the value from the row and plug it into C4, take the value from the column and plug it into C3, and then record the result from C6 in the appropriate cell in the grid."

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Analyze Your Results Grid

Click OK. Excel will now populate the entire grid, showing you the monthly mortgage payment for every single combination of interest rate and loan term. You can now find, for example, the monthly payment for a 20-year loan at a 6.0% interest rate at a glance.

Best Practices for Using Data Tables

Here are a few final tips to get the most out of your What-If Data Tables:

• They are Dynamic: Data tables are live. If you change any part of your original model (for example, you update the Loan Amount in cell C2 from $400,000 to $450,000), the entire table recalculates and updates instantly.
• Formatting for Clarity: For larger two-variable tables, use Conditional Formatting to apply color scales. This can help you visually spot trends, such as where payments are lowest or highest, without having to scrutinize every number.
• Watch Out for Workbook Calculation: For extremely large or complex data tables, you might notice your workbook slowing down, as Excel recalculates every time a change is made. If this happens, you can set calculations to manual by going to Formulas > Calculation Options > Manual. Just remember to press F9 to recalculate when you want updated results.
• Editing Your Results: The results in a data table are generated by an array formula (you might see something like {=TABLE(C4,C3)} in the formula bar). This means you cannot edit or delete an individual result cell within the table, you must edit the source data or clear the entire result area.

Final Thoughts

Mastering tools like the What-If Data Table helps you move from simply recording data to actively modeling possibilities and asking better questions. It automates repetitive work, enabling you to quickly analyze various scenarios, understand risks and opportunities, and ultimately make more strategic decisions based on data, not just guesswork.

Creating these analyses in Excel is incredibly useful for a single data model, but what happens when you need to answer questions that span multiple sources? You might build a sales projection model using data exported from Salesforce, then another one to analyze ad spend from Google Ads. This manual process of exporting, cleaning, and modeling data from different platforms is where things get really slow and inefficient. We built Graphed to solve exactly this problem. Instead of wrestling with CSVs, you just connect your data sources — like Salesforce, Shopify, and Google Analytics — and ask our AI analyst questions in plain English. You can say things like, “Chart our Shopify revenue versus Facebook Ad spend for the last 90 days” and get an interactive dashboard instantly. It turns hours of what-if prep work into a quick, real-time conversation.