How to Do a Two Variable Data Table in Excel

Testing how a change in one number affects your business is simple. But what happens when you need to see how changes in two different variables at the same time impact your bottom line? This is where many people get stuck, manually plugging in numbers over and over. This guide will show you how to use Excel’s powerful two-variable data table to see hundreds of potential outcomes at once, all without repetitive work.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is a Two-Variable Data Table?

A two-variable data table is an Excel What-If Analysis tool. At its core, it’s a calculator-on-steroids that shows you the results of a single formula when two different input variables change. You basically create a grid where one set of values runs down a column, another set runs across a row, and Excel fills in all the possible combined outcomes in the middle.

Imagine you run an e-commerce store. You want to see how changing your product’s price and your monthly ad spend will affect your overall profit. A two-variable data table lets you set a range of potential prices (e.g., $35, $40, $45, $50) and a range of ad spends (e.g., $1,000, $1,500, $2,000) and calculates the resulting profit for every single combination automatically. It turns dozens of manual calculations into a single, instant action.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

First, Build Your Financial Model

Before you can use a data table, you need a working model to test. After all, the data table is only as accurate as the formulas it’s referencing. The setup is critical: you must have individual cells for your two input variables and a final cell with the formula for the single result you want to measure.

Let's build a simple profit calculator for a product we're selling. Our model will be based on two main variables we want to play with: Unit Price and Ad Spend.

Here’s the setup in Excel:

Inputs and Assumptions:

• Cell B2: Unit Price = $45
• Cell B3: Monthly Ad Spend = $5,000
• Cell B4: Cost Per Unit = $17.50 (This is a fixed assumption for now)
• Cell B5: Base Units Sold = 1,000 (Units we'd sell with zero ad spend)

Formulas and Calculations:

• Cell B7 (Total Units Sold): We'll assume for our model that every $20 in ad spend generates one additional sale. The formula is: =B5+(B3/20)
• Cell B8 (Total Revenue): This is simply the unit price multiplied by the number of units sold. The formula is: =B7*B2
• Cell B9 (Total Costs): This includes the fixed ad spend plus the variable cost of producing all the units. The formula is: =B3+(B7*B4)
• Cell B11 (Calculated Profit): This is the final result we want to analyze. The formula is Revenue minus Costs: =B8-B9

With this model, changing the Unit Price in cell B2 or the Monthly Ad Spend in B3 will make all the other formulas update, ultimately changing the Calculated Profit in cell B11. This is the foundation we need for our data table.

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

Once your model is ready, it's time to build the data table that will run all your scenarios. It may feel a little abstract the first time, but if you follow these steps carefully, it will click into place.

Step 1: Structure Your Table Framework

Find an empty area on your worksheet. You’ll create a grid where you list one set of input variables down a column and the other set across a row.

• Column Inputs: Starting one cell below and one cell to the right of your planned top-left corner (e.g., in cell E5), list the different Unit Price values you want to test. For example, enter $35, $40, $45, $50, and $55 in cells E6 through E10.
• Row Inputs: In the top row of your grid, starting one cell to the right of the corner (e.g., in cell F5), list the different Monthly Ad Spend values. For instance, enter $3,000, $4,000, $5,000, $6,000, and $7,000 in cells F5 through J5.

You should now have an "L" shaped structure of numbers with an empty cell at the corner (cell E5 in our example) and a large blank area in the middle.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Link Your Formula to the Corner Cell

This is the most important step. In that empty corner cell (E5), you must tell Excel which final calculation you want it to perform for every scenario. Do this by making a direct reference to the output formula in your original model.

Click on cell E5 and type =B11. Since B11 is the cell containing our profit calculation, you are essentially telling the data table, "This grid is all about calculating the profit." You can format this corner cell with a custom number format like ,,, (three semicolons) to hide the text, as its purpose is for the formula, not for display.

Step 3: Select the Entire Table Range

Now, select the entire range for your table. This includes the corner cell with the linked formula, the row of ad spend values, the column of unit price values, and the empty cells in the middle where the results will appear. In our example, you would select from E5 to J10.

Step 4: Open the Data Table Tool

With the entire range selected, go to the Data tab on the Excel ribbon. Click on the What-If Analysis dropdown menu and then select Data Table....

Step 5: Define Your Row and Column Input Cells

A small dialog box will pop up with two fields: "Row input cell" and "Column input cell." This part tells Excel where the variables in your table's structure correspond to the variables in your original financial model. This is where people often get confused.

• Row input cell: This refers to the input cell in your original model that corresponds to the values you listed in the top row of your data table. Since our top row contains Ad Spend values, you must click in this field and then select cell B3 (the original Monthly Ad Spend cell).
• Column input cell: This refers to the input cell in your original model that corresponds to the values you listed in the left column of your new data table. Since our left column has Unit Price values, click in this field and then select cell B2 (the original Unit Price cell).

Double-check that these cell references point to your original model inputs, not the headers in your new table. Click OK.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 6: Review and Analyze Your Results

Instantly, Excel will populate the empty cells with the calculated profit for every single combination of Unit Price and Ad Spend.

To make the table even easier to understand, use Conditional Formatting. With the results area still selected (F6:J10), go to the Home tab, click Conditional Formatting → Color Scales, and choose an option like the Green-Yellow-Red scale. Excel will automatically color the cells, highlighting the highest-profit scenarios in green and the lowest-profit (or loss) scenarios in red. This turns a grid of numbers into a clear, strategic map of your most profitable options.

Common Issues and Troubleshooting Tips

If your data table isn't working as expected, it's usually one of a few common mistakes.

• All results are the same value: This often happens if you've incorrectly linked your input cells in the Step 5 dialog box. Make sure the "Row input cell" and "Column input cell" point to the original input cells in your base model (B3 and B2, respectively), not cells within the data table itself.
• The table shows errors (like #VALUE!): An error in the data table means there's likely an error in your original model's calculation. Double-check all of your formulas to ensure they work correctly before building the data table.
• Editing a single result cell is not allowed: The output of a data table is an array formula. You cannot change an individual result cell. To make changes, you must edit your original base model, change the input variables along the row/column, or recreate the table entirely.
• The table doesn't update when I change the model: Check your calculation options by going to Formulas → Calculation Options. If it's set to "Manual," the data table won't recalculate automatically. Set it to "Automatic" or manually press the F9 key to refresh all calculations in your workbook.

Final Thoughts

Mastering two-variable data tables transforms your Excel skills from simple calculation to powerful scenario planning. You can now test hundreds of hypotheses about your business in seconds, taking the guesswork out of strategic decisions and helping you find the optimal path forward backed by data.

While an amazing tool for analysis within spreadsheets, these tables are often just one part of the bigger picture. After all, your ad spend lives in a platform like Facebook Ads, your pricing in Shopify, and your actual sales results elsewhere. Pulling all that data together manually to power a model is exactly the kind of slow, friction-filled work that steals time from making decisions. That is precisely why we created Graphed. We connect directly to all your marketing and sales tools, so you can analyze real-time performance, build cross-platform dashboards, and ask complex "what-if" questions using simple English, without the manual data pulls or spreadsheet setup.