How to Create a DataTable in Excel

Ever find yourself tweaking numbers in a spreadsheet over and over again to see how it affects your bottom line? One change, then another, then another - it’s a tedious way to explore different scenarios. Excel has a powerful, often overlooked feature called a DataTable that automates this entire "what-if" process for you. This guide will walk you through exactly how to create both one-variable and two-variable DataTables to model different outcomes and make smarter decisions, faster.

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 is a DataTable in Excel?

First things first, an Excel DataTable is not the same as a standard table you create using "Format as Table." A DataTable is a specific tool within Excel’s "What-If Analysis" suite, which also includes Scenario Manager and Goal Seek. Its job is to take one or two variables in a formula and calculate all possible results based on a range of different inputs you provide.

Think of it as a pre-built "scenario matrix." Instead of manually copying your formula and plugging in different numbers, a DataTable does it all for you in one go, laying out the potential outcomes in a clean, organized table.

This is incredibly useful for:

• Financial Modeling: See how different interest rates and loan lengths will affect your monthly payments.
• Sales Forecasting: Project revenue based on different unit prices and sales volumes.
• Budgeting: Analyze how changes in variable costs (like ad spend) impact profitability.
• Marketing Analysis: Determine how various conversion rates and cost-per-click values affect your return on ad spend (ROAS).

There are two types of DataTables you can create. Let’s break down both.

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

Setting Up Your Initial Model

Before you can build a DataTable, you need a model with a formula. A DataTable doesn't work in a vacuum, it needs an existing calculation to work with. Let’s build a simple profit projection for an online course to use in our examples.

Set up your spreadsheet like this:

• B2: Course Price
• B3: Number of Sales
• B4: Ad Cost per Sale
• B6: Total Revenue (Formula: =B2*B3)
• B7: Total Ad Cost (Formula: =B3*B4)
• B9: Total Profit (Formula: =B6-B7)

Fill in some initial values. For example: Course Price: $299 Number of Sales: 100 Ad Cost per Sale: $50

With these inputs, your Total Profit in cell B9 will be $24,900. Now that our model is built, we can use a DataTable to see how changes affect this profit number.

How to Create a One-Variable DataTable in Excel

A one-variable DataTable lets you test a range of values for a single input cell and see the effect on one or more formula results. In our example, let's see how our profit changes if the "Number of Sales" varies.

Step 1: Structure Your Table

Find some empty space on your worksheet. In one column, type out the different values you want to test for your variable. Let's list potential sales numbers from 50 to 250 in increments of 25. For example, in cells D2 through D10:

• D2: 50
• D3: 75
• D4: 100
• D5: 125
• ... down to 250

This will be the input column for our DataTable.

Step 2: Link Your Formula

In the cell directly above and to the right of your input column (cell E1), reference the final formula you want to see the results for. Type =B9 into E1. This tells Excel that your goal is to see the different outcomes for the Total Profit formula.

Your setup should now look like this:

    E  

D =B9 50 75 100 ...

(Optional Tip: You can test multiple formulas at once! If you also wanted to see how "Total Revenue" changes, you could put =B6 in cell F1. The DataTable will fill out results for both.)

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 3: Launch the DataTable Tool

Select the entire range that contains your variables and your formula reference. In our example, that would be from D1 to E10. This selection must include both the list of variables and the linked formula cell(s).

Next, go to the Data tab on the Ribbon, click What-If Analysis in the Forecast group, and select Data Table....

Step 4: Configure the Input Cell

A small dialog box will appear with two fields: "Row input cell" and "Column input cell." Since our list of potential sales numbers is arranged in a column (D2:D10), fill in the "Column input cell" field.

Click into the "Column input cell" box, then click on your original input cell that corresponds to this variable—here, B3 ("Number of Sales").

Finally, click OK.

Step 5: See the Results

Excel will automatically fill the cells below D2 with the calculated profit for each sales level, based on your model. You now have a clear view of how profit scales as sales increase, without manually changing values.

How to Create a Two-Variable DataTable

Sometimes, changing one variable isn't enough. A two-variable DataTable lets you see how results change when you modify two different inputs simultaneously. Let’s expand our example to see how both "Number of Sales" and "Course Price" affect our Total Profit.

Step 1: Structure a Grid

Again, find some empty space. Prepare a grid layout:

• List the values for your first variable ("Number of Sales") down a column (cells D2:D10), as before.
• List the values for your second variable ("Course Price") across a row, starting one cell to the right and one cell up, e.g., E1 through H1 (like $199, $249, $299, $349).

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 2: Link the Formula in the Corner

In the top-left corner of your grid (cell D1), link to your master formula by typing =B9.

Your grid should look like this:

    E      F      G      H  

D =B9 $199 $249 $349 50 75 100 ...

Step 3: Launch the DataTable Tool

Select the entire grid D1:H10, including the formula link and both variable sets.

Go to Data > What-If Analysis > Data Table... again.

Step 4: Configure Both Input Cells

In the dialog:

• Row input cell: refers to the variable across the row, i.e., Course Price. Click on B2 in your model.
• Column input cell: refers to the variable down the column, i.e., Number of Sales. Click on B3.

Click OK.

Step 5: Analyze the Results

Excel will fill the grid with profit values for each combination of sales and price. You can now see, for example, that selling 100 units at $349 yields more profit than selling 125 units at $249.

Practical Tips & Common Errors

• DataTables are LIVE: Results depend on your original model. If you change the ad cost in B4, the DataTable updates automatically.
• The {TABLE} Formula: Results cells are filled with a special array formula: {=TABLE(row_input, col_input)}. Do not edit individual result cells. To update, delete the result area and re-run the DataTable.
• Performance Issues: Large models with many permutations may slow down your sheet. Manage this via File > Options > Formulas, and select Automatic Except for Data Tables for faster recalculations. Refresh with F9 as needed.
• Conditional Formatting: To better interpret the matrix, apply color scales via Home > Conditional Formatting > Color Scales. Green indicates high profit, red indicates low.

Final Thoughts

Excel's DataTables are a powerful tool for quick scenario analysis without manual calculations. Mastering one- and two-variable tables allows you to model business decisions more effectively, presenting a range of outcomes grounded in data.

While spreadsheets excel at isolated "what-if" analyses, integrating real-time business data from platforms like Shopify, Google Analytics, and your CRM can be challenging. That’s why we built Graphed, it connects directly to your marketing and sales tools, consolidates data into dashboards, and lets you ask questions in plain English—transforming hours of spreadsheet work into seconds.