How to Insert a One Variable Data Table in Excel

Ever find yourself tweaking one number in a spreadsheet over and over again just to see how it affects your final result? Excel's one-variable data table is a powerful "what-if" analysis tool designed to stop that repetitive work in its tracks. This guide will walk you through exactly how to set one up, step by step, so you can analyze multiple outcomes instantly.

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 Exactly is a One-Variable Data Table?

A one-variable data table in Excel allows you to test how changing a single input cell impacts the result of one or more formulas. Instead of manually typing in different values for that one variable, the data table does all the heavy lifting for you, presenting the results in a clean, organized table.

Imagine you're trying to figure out the monthly payment for a home loan, but you want to compare different interest rates. The single variable here is the interest rate. The formula result you care about is the monthly payment. A one-variable data table will take a list of different interest rates you provide (e.g., 5.0%, 5.25%, 5.5%) and calculate the corresponding monthly payment for each one, all at once.

This transforms a tedious, manual process into an automated, insightful analysis, allowing you to see a range of possibilities at a glance. It's perfect for things like:

• Financial forecasting (e.g., how different sales growth rates affect profit).
• Budgeting (e.g., how different cost assumptions change your bottom line).
• Sales commission calculations (e.g., testing various commission percentages).

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 Spreadsheet for a Data Table

Before you can insert the data table, you need to structure your spreadsheet correctly. A little bit of prep work ensures everything works smoothly. We'll stick with a simple loan payment calculation for our example.

Step 1: Create Your Base Model and Formula

First, you need the original calculation set up. This is the "model" your data table will use for its "what-if" scenarios.

Organize your known values in separate cells. For a loan, this would be:

• Loan Amount: e.g., $400,000
• Annual Interest Rate: e.g., 5.5%
• Loan Term (in Years): e.g., 30

Next, write the formula you want to analyze. To calculate a monthly mortgage payment, Excel's PMT function is perfect. The syntax is =PMT(rate, nper, pv), where:

• rate: The interest rate per period. Since we have an annual rate but need a monthly payment, we must divide our interest rate cell by 12.
• nper: The total number of payment periods. Since our term is in years, we must multiply the term cell by 12.
• pv: The present value, or the total loan amount.

Let's say your values are in cells B1, B2, and B3. Your formula in another cell (like B5) would look like this: =PMT(B2/12, B3*12, B1)

Note: The PMT function returns a negative number by default, as it represents a cash outflow. If you prefer a positive number, simply put a minus sign before the formula, like this: =-PMT(...).

Now you have your base model: the inputs and the formula a data table can work with.

Step 2: Structure the Data Table Layout

With your base formula complete, it's time to set up the structure for the data table itself. This is where you'll list the different input values you want to test.

1. List Your Input Variables: In a single column nearby, list all the different values you want to test for your chosen variable. In our case, this is the interest rate. You could list rates like 4.5%, 5.0%, 5.5%, 6.0%, and 6.5% in cells D2 through D6.
2. Link Your Formula: This is the most crucial layout step. In the cell that is one column to the right and one row above your list of input variables, you must reference your original formula cell. In our example, since the interest rates are in D2:D6, this would be cell E1. In cell E1, you would enter =B5 (where B5 is the cell containing your original PMT formula).

Your layout should now look something like a capital 'L' on its side. The vertical part is your list of inputs, and the corner point at the top right is your formula link. The space to the right of your inputs is where Excel will populate the results.

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

How to Insert the One-Variable Data Table: Step-by-Step

With a properly structured spreadsheet, creating the data table is surprisingly fast. Just follow these steps.

Step 1: Select Your Entire Data Table Range

Click and drag to select the entire table you've just laid out. This selection must include both your list of input variables and the cell containing the linked formula. For our example, you would select the range D1:E6.

Step 2: Navigate to the What-If Analysis Tool

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

1. Click on the Data tab.
2. Look for the Forecast group.
3. Click on What-If Analysis.
4. Select Data Table... from the dropdown menu.

Step 3: Define the Column Input Cell

A small dialog box will appear with two fields: "Row input cell" and "Column input cell." This is where you tell Excel what the data table is actually substituting.

Since your list of test values (the interest rates) is arranged in a column, you will use the Column input cell field.

Click into the "Column input cell" field. Then, click on the single cell in your original model that contains the input this data belongs to. For our example, this is the original interest rate cell: B2.

You're telling Excel: "Take each value from column D, plug it into cell B2 one by one, recalculate the formula in B5, and show me the result."

Leave the "Row input cell" field blank if your variables are in a column.

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 4: Click OK and Review the Results

Click OK. Excel will instantly populate the rest of the table with the calculated results. Each cell in column E next to an interest rate will now show the corresponding monthly payment for that rate. You've successfully automated your what-if analysis!

Tips for Working with Data Tables

Once you've built your table, here are a few extra tips to get the most out of it.

• Formatting for Clarity: The results come back unformatted. Format them to match your data. In our example, you'd format the results as Currency to make them easier to read.
• The Uneditable {TABLE} Formula: If you click on one of the result cells, you'll see a special formula like {=TABLE(,B2)}. This is an array formula that cannot be individually edited or deleted. This is intentional, it protects the structure of the table. To change the results, you must either change the values in your input column (D2:D6) or edit your original calculation formula (in B5).
• Dynamic Updates: The best part about data tables is that they are dynamic. Change your Loan Amount from $400,000 to $450,000 in cell B1, and the entire data table of results will update automatically. You can also change the interest rates in your input column (e.g., from 4.5% to 4.75%) and the corresponding result will instantly recalculate.
• Visualizing the Results: Data tables are perfect for creating charts. Just highlight your input column and the results column (e.g., D2:E6) and insert a line or bar chart to visually represent how the monthly payment changes as interest rates rise.

More Use Cases for One-Variable Data Tables

Loan payments are just the beginning. You can use this tool for countless scenarios across different business functions:

• Sales Commissions: Use a list of different weekly sales totals as your input variable to see how a salesperson's commission payout would change.
• Pricing Analysis: Input a range of different product prices to see the impact on total revenue or profit margin, assuming a certain sales volume.
• Ad Campaign Budgeting: Test a series of different daily ad spends as your input variable to forecast your monthly total ad expenditure.
• Inventory Management: See how different order quantities affect your total holding costs for the year.

Final Thoughts

Excel's one-variable data table is a fantastic feature that automates repetitive what-if analysis, saving you significant time and helping you make better-informed decisions. By structuring your model, setting up the table layout, and pointing Excel to the right input cell, you can quickly analyze a wide range of outcomes without any manual calculations.

Digging into spreadsheets like this is often just the first step in getting answers about your business. When you're ready to move beyond manual data wrangling in Excel, tools like Graphed are here to help. We automate the entire reporting process by connecting directly to your marketing and sales platforms - like Google Analytics, Shopify, advertisement manager accounts, and various CRMs - allowing you to create live-updating dashboards by simply describing what you want in plain English. This gives your entire team the ability to get the data-driven answers they need in seconds, not hours.