How to Make a One Way Data Table in Excel

Performing "what-if" analysis is a core part of making smart business decisions, but manually plugging different numbers into a formula over and over again is tedious and slow. Excel’s One-Way Data Table is a powerful feature designed to solve this exact problem, letting you see how changing one key variable impacts your bottom line. This article will walk you through, step by step, how to set up and use a one-way data table to analyze different scenarios instantly.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is a One-Way Data Table in Excel?

A One-Way Data Table is a feature within Excel's "What-If Analysis" toolset. In simple terms, it takes one input variable from a formula and shows you all the potential output results as that single input changes. Instead of manually typing, "What if my price is $10?... What if it's $12?... What if it's $15?" you can create a table that calculates all these outcomes for you automatically.

Imagine you run a small e-commerce store and want to figure out the best price for a new product. Your profit depends on the sale price, but also on costs like manufacturing and shipping. A data table can show you precisely how much profit you'll make at various price points, like $25, $30, $35, and $40, all in one clean view.

It's called a "one-way" table because it only tests the impact of one changing variable. (Excel also has Two-Way Data Tables, which let you test two variables simultaneously, such as price and ad spend).

Graphed

Still Building Reports Manually?

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

Watch the Demo

Setting Up Your Core Formula or "Model"

Before you can build a data table, you need a basic calculation set up in your worksheet. The data table needs a formula to work with. Let's build a simple model to project revenue for a webinar you're planning to host.

Your model needs three things:

• An input variable. This is the number you want to change. For us, this will be the Ticket Price.
• Other constants or assumptions. These are numbers that will stay the same for now, like the Estimated Attendees.
• An output formula. This is the calculation that produces the final result you care about, like Total Revenue.

Here’s how to set this up on a blank Excel sheet:

1. Label Your Cells: In cells A1, A2, and A3, type the labels: Ticket Price, Estimated Attendees, and Total Revenue.
2. Enter Your Initial Values: In cell B1, enter your starting ticket price, let's say 50. In cell B2, enter the number of attendees you expect, like 150.
3. Write the Output Formula: In cell B3, you need a formula to calculate the total revenue. Type =B1*B2 and press Enter. This formula multiplies the ticket price by the number of attendees.

Your sheet should now look like this, with cell B3 showing a result of 7500.

This small setup is your "model." The one-way data table will use the formula in cell B3 as its foundation for calculating all the other potential outcomes.

How to Create the One-Way Data Table: Step-By-Step

With your model in place, you're ready to build the data table. We want to see how the Total Revenue changes if we adjust the Ticket Price. We'll set up our table to test ticket prices from $30 up to $70.

Step 1: Structure Your Table Layout

First, you need to list the input values you want to test. You can list them either down a column or across a row. A column layout is more common and what we'll use here.

Find a blank space on your sheet (we'll use column D and E). In cells D2 through D10, type out the different ticket prices you want to analyze:

• Cell D2: 30
• Cell D3: 35
• Cell D4: 40
• and so on, up to 70

Step 2: Link Your Output Formula

This is the most critical and often misunderstood step. You need to tell the data table which result you want to calculate. You do this by referencing the cell that contains your final output formula from the original model.

In our layout, the list of input variables starts in cell D2. In the cell directly above and to the right (cell E1), you must link to your revenue formula. Simply click on cell E1 and type =B3, then press Enter. This tells the table: "For every input value in column D, I want you to calculate the result from cell B3."

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Select the Table Range

Now, select the entire range that makes up your data table. In our case, this includes your list of input variables (the prices) and the linked formula cell. So, click and drag to select from cell D1 to E10.

It's important that you include both the column of inputs and the column where the results will appear, as well as the formula reference at the top.

Step 4: Launch the Data Table Tool

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

1. Click the Data tab.
2. Find the What-If Analysis dropdown (in the Forecast group).
3. Click on Data Table... from the dropdown menu.

This will open a small "Data Table" dialog box.

Step 5: Define the Input Cell

The dialog box has two fields: "Row input cell" and "Column input cell." This is where you tell Excel which input from your original model should be replaced by the values in your table.

• Since our list of ticket prices (30, 35, 40 etc.) is in a column, we will use the "Column input cell" field.
• Click into the "Column input cell" box.
• Now, click on cell B1 in your original model. This is the cell that contains your initial Ticket Price.

You're telling Excel: "Take each value from column D, plug it into cell B1 one by one, and then record the result from B3 in the table."

The "Row input cell" should be left blank. Click OK.

Step 6: Review Your Automatically Calculated Results

Instantly, Excel fills column E with the calculated revenues for each corresponding ticket price in column D. You can now see every scenario laid out perfectly without any manual work!

To make it more readable, feel free to add headers like "Ticket Price" to D1 and "Total Revenue" to E1. Your final table should look like this:

If you look in the formula bar for any of the result cells (e.g., E2), you'll see a special array formula: {=TABLE(,B1)}. You can't edit or delete these formulas individually, they are part of the table as a whole. To remove it, you have to delete all the generated results at once.

Troubleshooting and Tips for Data Tables

All My Results are the Same!

This is the most common issue. It nearly always happens because the "Column input cell" (or Row input cell) is pointing to the wrong place. Double-check that it is referencing the correct input cell in your original model (cell B1 in our example), not a cell within the data table itself.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How Do I Update the Table?

The data table is dynamic. If you change a number your model relies on, the table will update instantly. For example, if you change the value for "Estimated Attendees" in cell B2 from 150 to 200, the entire "Total Revenue" column in your data table will recalculate to reflect this new assumption.

Using a Horizontal (Row-Oriented) Table

If you'd rather list your inputs horizontally, the process is almost identical. Your list of variables would go across a row (e.g., D2:J2). Your linked formula would go to the left and one cell down, in C3. When filling out the dialog box, you would use the "Row input cell" field instead of the column one.

Practical Business Examples

Besides pricing, one-way data tables are great for:

• Loan Analysis: See how changing the interest rate (your input) affects the monthly mortgage payment (your output).
• Commission Scenarios: Calculate how different sales conversion rates (your input) would affect the total commission earned (your output).
• Retirement Planning: Test how different annual contribution amounts (your input) change your final nest egg amount (your output).

Final Thoughts

Excel's one-way data table is an incredibly efficient tool for sensitivity and what-if analysis. It saves you from mind-numbing manual work, allowing you to quickly visualize how a single change can ripple through your calculations and impact your overall goals. Master this simple feature, and you'll find yourself making more informed decisions in less time.

While data tables are perfect for scenarios inside Excel, this kind of analysis often needs data from multiple business systems at once. Manually exporting data from Shopify, Google Ads, and Salesforce to build a model is time-consuming. We built Graphed to solve this challenge. Rather than wrestling with CSV files, we let you connect to your data sources directly and build real-time dashboards just by asking questions. This lets you see the full picture and get actionable insights without the usual reporting headache.