How to Create a One Variable Data Table in Excel

Manually calculating different outcomes for your business projections can feel like a guessing game. Changing one number in a spreadsheet, like price or ad spend, forces you to track all the dependent formulas to see the final impact, then repeat the process over and over. This is where Excel’s One-Variable Data Table comes in, allowing you to test countless scenarios at once without any of the manual work. This article will show you exactly how to set up and use this powerful what-if analysis tool.

What Exactly is a One-Variable Data Table?

A One-Variable Data Table in Excel is a what-if analysis tool that lets you see how changing a single input cell impacts the results of one or more formulas. Instead of manually plugging in different values one by one, you create a list of potential inputs, and the data table automatically calculates the corresponding outputs for you in a neat, organized table.

Think of it like this: you're a small e-commerce business owner trying to decide on the perfect price for your best-selling product. You know that changing the price will affect your revenue, total costs, and ultimately, your profit. You could change the price in your spreadsheet from $29 to $30, see the new profit, then change it to $31, and so on. A One-Variable Data Table automates this entire sequence, showing you the projected profit for every single price point from, say, $25 to $40, all in one go.

This is fundamentally different from a Two-Variable Data Table, which lets you test how two separate variables (like price and units sold) simultaneously affect a single formula. For now, we'll focus on how to master the one-variable version.

First, You Need a Solid Data Model

Before you can use a data table, you need to build a small model that connects your inputs to your outputs. The data table relies on this structure to run its calculations. Your model doesn’t need to be complicated, it just needs clear, separate cells for your inputs and at least one formula that produces an output.

Let's stick with our e-commerce pricing example. Here’s how you could structure a simple profit calculation model.

Step 1: Define Your Inputs

Find a clean space in your spreadsheet and label your inputs. These are the core numbers that drive your calculations. For our scenario, we'll need:

• Price Per Unit: The selling price for one product.
• Units Sold: The forecasted number of units you expect to sell.
• Cost Per Unit: The variable cost to produce one product.
• Fixed Costs: Costs that don't change with sales volume, like rent or salaries.

In Excel, this might look something like this:

A1: -- Inputs -- A2: Price Per Unit B2: $30 A3: Units Sold B3: 1,500 A4: Cost Per Unit B4: $12 A5: Fixed Costs B5: $10,000

Step 2: Create Your Output Formulas

Next, create the formulas that will calculate your final results based on the inputs above. It's best practice to keep these separate from the inputs to maintain clarity.

• Revenue: Calculated as Price Per Unit × Units Sold.
• Total Variable Costs: Calculated as Cost Per Unit × Units Sold.
• Profit: Calculated as Revenue - Total Variable Costs - Fixed Costs.

Following our layout, the formulas would be:

A7: -- Outputs -- A8: Revenue B8: =B2*B3 A9: Total Variable Costs B9: =B4*B3 A10: Profit B10: =B8-B9-B5

With this simple model in place, you’re ready to build the data table. The key takeaway here is that cell B10 (Profit) depends on the value in cell B2 (Price Per Unit). This relationship is what makes the what-if analysis possible.

How to Create a One-Variable Data Table: A Step-by-Step Guide

Now that our model is set up, let's create the data table to test how different price points affect our profit. We will use a column-oriented table, where our variable inputs are listed down a single column.

Step 1: Set Up the Table Structure

Go to an empty area of your sheet. To build a column-based table, you’ll need one column for your different inputs (our prices) and an adjacent column where Excel will place the results (the profits). Let's say you start this in column D.

Leave the top-left cell of your table structure (D12 in our example) intentionally blank.

Step 2: List Your Variable Inputs

In the first column of your table structure, starting one cell down (D13), list all the different values you want to test for your single variable. In our case, this is the "Price Per Unit." Let’s test prices from $25 to $35.

D13: $25 D14: $26 D15: $27 ...and so on, down to D23 for $35.

Step 3: Reference Your Output Formula

This is the most important step. In the cell to the right of your blank header cell (E12), you need to link to the final output formula you want the table to calculate. You do this by creating a direct reference to the cell containing your original profit formula.

Click on cell E12 and type =B10 (or whatever cell contains your profit calculation). Press Enter.

Optional Tip: You aren't limited to one output! If you also wanted to see how Revenue changes, you could link to that formula in the next cell over (F12). You'd type =B8 in F12. The data table will calculate results for every formula in the header row.

Your structure should now look like this, with the values in E12 and F12 showing the current calculations from the model:

Step 4: Select the Entire Table Range

Click and drag your mouse to select the entire range for your data table. This includes the column of input variables and the header row with the formula references. In our example, you would select the range D12:E23 (or D12:F23 if you added the Revenue formula).

Step 5: Open the Data Table Tool

With your range selected, navigate to the Excel ribbon at the top of the screen:

1. Click on the Data tab.
2. Find the Forecast group (in some versions, it's called "Data Tools").
3. Click on What-If Analysis.
4. From the dropdown menu, select Data Table....

Step 6: Configure the Data Table Dialog Box

A small dialog box will appear with two fields: "Row input cell" and "Column input cell." This is where you tell Excel which input from your original model it should replace with the values from your table.

• Since we organized our list of prices in a column, we will use the Column input cell field.
• Click into the Column input cell field.
• Now, click on the cell in your original model that contains the input you are testing. For us, this is cell B2, which contains our original "Price Per Unit."

Leave the "Row input cell" blank, then click OK.

Step 7: Analyze Your Results

Instantly, Excel fills the table! It automatically takes each price from column D, substitutes it into your original model's Price cell (B2), recalculates the Profit (B10), and places that result in the corresponding row in column E. You now have a complete scenario analysis that clearly shows the relationship between your selling price and your total profit, without having to change a single cell manually more than once.

Pro Tips for Working With Data Tables

Once you’ve built your first table, keep these points in mind to get the most out of the feature:

• Results Are an Array: The outputs generated by the data table are created using an array formula, which looks like {=TABLE(,B2)}. This means you cannot edit or delete the contents of a single result cell. To change the results, you must either modify the original model formulas (like changing fixed costs) or update the input variables listed in the first column of your table.
• Automatic Recalculation: Data tables are live. If you change any formula in your original model or any of the input values you typed in column D, the results in the table will update automatically.
• Can I Use Rows Instead of Columns? Absolutely. To create a row-oriented table, list your input variables across a single row. Then, link to your output formula(s) one cell down and to the left. When you fill out the Data Table dialog box, you'll use the Row input cell field instead, linking it to the same original input cell (B2 in our example).

Common Use Cases for One-Variable Tables

Data tables are incredibly versatile and can be applied to nearly any business function. Here are a few more common scenarios where they prove invaluable:

• Marketing & Sales:
• Financial Planning:
• Operations & E-commerce:

Final Thoughts

Setting up a One-Variable Data Table in Excel is a straightforward process that transforms static spreadsheets into dynamic models. By automating scenario analysis, you free up valuable time to focus on interpreting the data and making smarter, more informed decisions for your business.

While data tables in Excel save a lot of manual calculation, they still require careful setup of a model and structure. You have to build the logic before you can get the answers. At Graphed, we designed a tool to skip these steps entirely. By connecting your live data sources like Google Analytics or your CRM, you can ask questions in plain English - like "forecast our revenue if we increase our ad spend by 15%" - and get instant dashboards and insights without ever building an Excel model first. It's the "what-if" analysis you need, delivered in seconds.