How to Use the Data Table Function in Excel

Excel's Data Table feature is one of the most powerful and underused tools for sensitivity and what-if analysis. It allows you to transform a static spreadsheet into a dynamic model, seeing how changes in one or two variables affect your final numbers, all at once. This article will walk you through setting up both one-variable and two-variable Data Tables, complete with practical examples you can start using today.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is a What-If Analysis?

At its core, a "what-if" analysis is the process of changing values in cells to see how those changes affect the outcome of formulas on the worksheet. It's a way to explore different scenarios without having to manually type in new numbers over and over again. For example, you might ask:

• "What if our cost of materials increases by 10%? How would that impact our profit margin?"
• "What if we achieve a 5% conversion rate on our website instead of 3%? How many more leads would we get?"
• "If interest rates change, how will that affect our monthly loan payment?"

Excel has three primary tools in its What-If Analysis group on the Data tab: Scenario Manager, Goal Seek, and Data Tables. While all are useful, Data Tables are specifically built for testing a whole range of possibilities for one or two variables simultaneously and displaying all the outcomes in a neat table.

Understanding One-Variable Data Tables

A one-variable data table lets you see how changing a single input value affects the results of one or more formulas. Let's use a common business scenario: projecting revenue based on different marketing budgets.

Imagine your company has determined that for every $1 you spend on marketing, you generate $4.50 in revenue. Your marketing budget is your key variable, and you want to model out potential revenue at various spending levels.

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 Formula

First, set up your core model. It doesn't need to be complicated. Create cells for your main assumptions and the formula that calculates the result.

1. Create a label for "Marketing Spend" (e.g., in cell A1) and enter an initial value, like $10,000 (in cell B1).
2. Create a label for "Revenue Multiple" (e.g., in cell A2) and enter your multiplier, like 4.5 (in cell B2).
3. Create your output formula. In cell B4, with a label like "Projected Revenue," enter the formula: =B1*B2. This will give you your baseline projection of $45,000.

Your setup should look something like this:

Step 2: Create a List of Your Variable Inputs

Now, decide what values you want to test for your variable (Marketing Spend). In a single column a little bit away from your initial setup, list out the various budgets you want to analyze. For example, in cells A7 through A12, you could list:

• $5,000
• $10,000
• $15,000
• $20,000
• $25,000
• $30,000

These values will be the basis for your data table.

Step 3: Link the Output Formula

This is a critical step. In the cell to the right of your variable header and one row above your first variable value (in our case, cell F6), reference your output formula. Simply type = and click on the cell containing your "Projected Revenue" formula.

Why do this? You're telling the Data Table tool, "This is the formula result I want you to recalculate for every new input."

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Generate the Data Table

Now you're ready to create the table.

1. Select the entire range of your table, from the cell with the formula reference down to the last variable and across to the column where the results will appear (in our case, select range A6:B12).
2. Go to the Data tab on the Ribbon.
3. In the Forecast group, click on What-If Analysis, and then select Data Table...
4. A small dialog box will pop up. Because our list of variables (Marketing Spend) is in a column, we will use the Column input cell: field.
5. In the "Column input cell" field, select the original input cell for your variable. This is the cell your main formula refers to. For us, that's cell B1.

Click OK. Excel will instantly populate the results column, showing you the projected revenue for each marketing spend level. The best part? It's dynamic. If you change your Revenue Multiple in cell B2 from 4.5 to 5, the entire table updates automatically.

Understanding Two-Variable Data Tables

A two-variable data table takes this a step further, allowing you to see how changing two different inputs in a formula affects the outcome. This is perfect for understanding the combined impact of multiple factors.

Let's build on our previous example. We already know how different marketing budgets impact revenue. But what if our "Revenue Multiple" isn't fixed? What if it could also range from 3 to 6 depending on campaign effectiveness? A two-variable data table can model this out perfectly.

Step 1: Set Up Your Table Framework

We'll use the same core setup as before (Marketing Spend, Revenue Multiple, and the Projected Revenue formula). This time, the structure of your what-if table is slightly different.

1. List your first variable's values down a column. Let's use the same "Marketing Spend" values from before, in cells A7:A12.
2. List your second variable's values across a row, starting one column to the right of the column header. For instance, in row 6 (cells B6:F6), list several Revenue Multiples like: 3.0, 3.5, 4.0, 4.5, 5.0, etc.

Your structure should now be an 'L' shape of input values.

Step 2: Link the Output Formula

For a two-variable table, the reference to your output formula must go in the empty corner cell where your row and column variables meet. In our layout, this is cell A6. Type =B4, which links it to our original "Projected Revenue" output of $45,000.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Generate the Two-Variable Data Table

With everything in place, it’s time to generate the table.

1. Select your entire table range, including both sets of variables and the corner formula cell (in our latest example, A6:D10).
2. Go to the Data tab on the Ribbon.
3. In the Forecast group, click on What-If Analysis, and then select Data Table...
4. The dialog box will ask for two inputs. For "Row input cell" specify the original cell for the revenue multiple (e.g., B2), and for "Column input cell" specify the original cell for marketing spend (e.g., B1).

Click OK. The grid will populate with results based on the combinations of the two variables, giving you dynamic insights into both variables' impacts.

Essential Tips for Working with Data Tables

Once you get the hang of them, Data Tables are straightforward. Here are a few best practices to keep in mind to make them even more effective:

• Data Tables are Dynamic: Remember, the results are live. If you update the main formula linked in the corner or any of the other base assumptions not included in the table, the entire grid of results will recalculate immediately. This makes them great for interactive scenario planning during meetings.
• Results Cannot Be Individually Edited: The output grid of a Data Table is a special array formula (you'll see something like {=TABLE(B2,B1)}). You cannot just type a new value into one of the result cells. To change the results, you must change the input variables, the base assumptions, or the core formula itself.
• Use Formatting for Clarity: These tables can be dense with numbers. Use Conditional Formatting to highlight the best or worst outcomes, or apply currency formatting to make the values easier to interpret at a glance. For instance, you could use a green-yellow-red color scale to quickly spot the most and least profitable scenarios.
• Manage Calculation Speed: In extremely large workbooks with massive Data Tables, automatic recalculation can slow things down. If you notice a lag, you can go to File > Options > Formulas and under "Calculation options," select Automatic Except for Data Tables. With this setting, your tables will only recalculate when you manually press the F9 key.

Final Thoughts

Excel's Data Tables offer a powerful way to conduct what-if analysis, turning a simple formula into a dynamic model that reveals the potential impact of one or two key variables. It moves you from one-dimensional thinking to exploring a full spectrum of possibilities, which is incredibly valuable for finance, marketing, and operational planning.

As powerful as this analysis is, it often highlights the challenge of getting all your data in one place to begin with. Models like this in Excel are often powered by manually exported data from your sales, marketing, and analytics platforms. For exactly this reason, we created Graphed to automate the entire data collection and reporting process. We connect directly to your data sources like Google Analytics, Shopify, and Salesforce to build real-time, interactive dashboards instantly using natural language. It's the perfect complement to tools like Excel, taking care of the tedious data gathering so you can spend more time on high-value strategic analysis.