How to Create a Two-Way Data Table in Excel

Building a financial model or forecast in Excel often involves seeing how changing one or two key variables impacts your final numbers. A two-way data table is the perfect tool for this "what-if" analysis, letting you visualize dozens of potential outcomes in a simple, organized grid. This article will walk you through exactly how to set one up, step-by-step, using a real-world business example.

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 Exactly is a Two-Way Data Table?

A two-way data table, also known as a data matrix, is a feature in Excel that shows you how different values for two variables in a formula will change the final result. Think of it as a powerful scenario planner. Instead of manually changing your input cells over and over to see different results, a data table automates the entire process and shows you all possible combinations at once.

This is different from a one-way data table, which only allows you to see the impact of changing a single input variable. The two-way version is ideal for more complex questions, such as:

• "How will my product's profit change based on various price points and marketing budgets?"
• "How will my loan's total interest paid change based on different interest rates and loan durations?"
• "How many sales do I need to break even at different conversion rates and website traffic levels?"

At its core, a two-way data table has three components:

1. A row input cell with a list of values you want to test.
2. A column input cell with a second list of values.
3. A single formula that depends on both of those input cells.

Once set up, Excel calculates the formula for every possible combination of row and column values, filling the table with the results.

How to Create a Two-Way Data Table: A Step-by-Step Guide

The best way to learn is by doing. Let's build a simple profit forecast for a product to see how changing the Unit Price and the monthly Marketing Spend affects the overall Profit.

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 1: Set Up Your Initial Formula and Inputs

First, you need a basic model where your core calculation is already set up. In a blank Excel sheet, create a small model like the one below. This model should contain the two variables you want to test (we'll call these the "input cells").

Here's our setup in cells A1:B7:

Cell A1: Unit Price → Cell B1: 40 Cell A2: Variable Cost Per Unit → Cell B2: 15 Cell A3: Units Sold (Estimated) → Cell B3: 1,000 Cell A4: Marketing Spend → Cell B4: 5,000

And here are the calculation formulas:

Cell A5: Revenue → Cell B5: =B1*B3

Cell A6: Total Costs → Cell B6: =(B2*B3)+B4

Cell A7: Profit → Cell B7: =B5-B6

With these numbers, our initial profit is $20,000. This profit calculation in cell B7 is the cornerstone of our data table.

Step 2: Structure Your Data Table Layout

Now, find some empty space in your sheet to build the structure for the data table itself. This structure includes the headers for your rows and columns and, most importantly, a link to the final formula you want to test.

1. Create Column Headers: Choose one of your variables - let’s use Unit Price. In a column, list the different prices you want to test. For example, in cells E3 to E8, enter: 35, 40, 45, 50, 55, 60.
2. Create Row Headers: For your second variable, Marketing Spend, list the values you want to test across a row. For example, in cells F2 to J2, enter: 3000, 5000, 7000, 9000, 11000.
3. Link the Formula: This is the most crucial - and most often missed - step. In the cell at the top-left corner of your table structure (cell E2 in our example), you must link directly to the final result formula from your model. Simply type =B7 and press Enter. The cell should now display 20000, matching our original profit calculation.

Your empty table structure should now look something like this. You can leave the corner cell (E2) as a value or hide it by formatting the text color to white, as its only purpose is to tell the data table what to calculate.

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 3: Select Your Table and Launch the What-If Analysis Tool

Now that the structure is ready, it's time to let Excel fill in the blanks.

1. Select the entire range of your data table, including the row headers, column headers, and the linked formula corner cell. In our example, this would be the range E2:J8.
2. Navigate to the Data tab on the Excel ribbon.
3. In the Forecast group, click on What-If Analysis.
4. From the dropdown menu, select Data Table....

A small dialog box with two input fields will appear.

Step 4: Define Your Row and Column Input Cells

This dialog box is where you tell Excel how the headers of your table map back to the input cells in your original model.

• Row input cell: This field needs the reference to the original input cell that your row headers correspond to. Our row headers (in F2:J2) represent different values for Marketing Spend. So, click into this box and select cell B4 (our original Marketing Spend cell).
• Column input cell: This field needs the reference to the original input cell that your column headers correspond to. Our column headers (in E3:E8) represent different values for Unit Price. So, click into this box and select cell B1 (our original Unit Price cell).

Click OK.

Step 5: Analyze the Results

Instantly, Excel will populate the center of your table with the calculated profit for every combination of Unit Price and Marketing Spend. The table shows you that if you price at $45 and have a Marketing Spend of $7000, your profit will be $23,000. Or, if you price at $60 with a spend of $11000, your profit will be $34,000.

You now have a powerful sensitivity analysis tool that you can review to make strategic decisions.

Common Issues and Best Practices

Data tables are fantastic, but you might run into a few common hurdles. Here’s how to troubleshoot them and get the most out of your analysis.

The Problem: The table is filled with zeros or the same number repeated.

This is almost always due to one of two reasons:

1. You forgot to link the calculation formula in the top-left corner cell (E2 in our guide). Make sure that cell contains =B7 (or whichever cell holds your final calculation).
2. You mixed up the Row and Column input cells in the dialog box. Double-check that the "Row input cell" points to the original cell for your row headers, and the "Column input cell" points to the original for your column headers.

The Problem: I changed a number, but the table didn't update.

Excel sometimes has its calculation options set to update Data Tables manually to avoid slowing down large workbooks.

• Go to File > Options > Formulas.
• Under Calculation options, check if it's set to "Automatic" or "Automatic Except for Data Tables".
• If it's the latter, your table will only update when you press the F9 key on your keyboard to force a recalculation.

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

The Problem: Excel won't let me change a single cell inside the data table.

This isn't a bug, it's a feature. The output of a data table is an array formula, which means all the result cells are locked together. You cannot edit or delete an individual result. To clear it, you must select all the generated results inside the table (F3:J8 in our example) and press the Delete key.

Best Practice: Use Conditional Formatting

Once your table is generated, apply Conditional Formatting to make the insights pop. Select all the calculated results inside your table, go to the Home tab, click Conditional Formatting > Color Scales, and choose a green-to-red scale. This will instantly highlight the best and worst-performing scenarios, making your analysis even faster and more intuitive.

Final Thoughts

Excel's two-way data table is one of the most powerful 'what-if' analysis tools available for uncovering how two key variables impact an important outcome. Once you master the simple setup of linking your formula and defining your input cells, you can generate comprehensive scenario models for everything from product launches and campaign budgets to financial forecasts.

Creating these models in Excel is effective, but they still depend on having clean data manually pulled from your various platforms. We built Graphed to cut out that painful process entirely. By connecting directly to tools like Google Analytics, Shopify, and Salesforce, we let you create real-time dashboards and run complex analyses using just simple conversational language. This allows you to get straight to insights and scenarios without all the spreadsheet wrangling.