How to Use Excel Data Table

Excel's Data Table feature is a powerful tool for what-if analysis, yet it's often overlooked. Instead of manually plugging in different numbers to see how they affect a result, you can use a Data Table to see dozens or even hundreds of outcomes all at once. This article will walk you through exactly how to build both one-variable and two-variable data tables step-by-step, helping you make smarter, data-driven decisions in minutes.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is an Excel Data Table? (And Why Should You Care?)

First, let's clear up a common point of confusion. An Excel Data Table is not the same as a standard Excel table created using "Format as Table" (or the shortcut Ctrl+T). A standard table is primarily for organizing, sorting, and filtering data. A Data Table is a dedicated analysis tool found under the "What-If Analysis" menu.

Its job is simple but game-changing: it takes one or two input variables in your formula and automatically calculates the results for a whole range of different values. In essence, it runs multiple calculations simultaneously and displays all the possible outcomes in a neatly organized table.

Think of it as an automated scenario planner for your numbers. Instead of asking "What is our monthly loan payment with a 4.5% interest rate?" you can ask, "What would our monthly payment be for every interest rate between 3% and 7%, in 0.25% increments?" A Data Table answers this instantly, saving you from a tedious copy-paste-and-edit marathon.

Common Scenarios for Using a Data Table

Data Tables are useful any time you want to see how changing a variable or two impacts a key result. Here are a few relatable examples from different departments:

• For Finance Teams: Model the effect of changing interest rates on monthly loan payments, see how different revenue growth rates impact profitability, or evaluate a project's net present value (NPV) under various discount rates.
• For Sales Managers: Calculate potential commission payouts for your team by modeling different sales volumes and bonus percentages. You can quickly find the sweet spot that motivates reps without breaking the budget.
• For Marketers: Analyze how changes in ad spend and conversion rates affect your overall cost per acquisition (CPA). Or, you could model how adjustments in customer retention rates and average order value (AOV) impact lifetime value (LTV).
• For Product Managers: Test different pricing strategies by creating a table that shows how various price points and estimated sales volumes ripple through to total revenue and profit margin.

Graphed

Still Building Reports Manually?

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

Watch the Demo

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

Let's walk through the most common use case: a one-variable table. We'll use a simple loan calculator as our example. Our goal is to see how different interest rates affect the monthly payment.

Step 1: Set Up Your Initial Model

First, you need a basic financial model with your inputs and your formula. Find an empty space on your worksheet and set it up like this:

• Cell B2: Loan Amount
• Cell C2: $350,000
• Cell B3: Interest Rate (Annual)
• Cell C3: 4.75%
• Cell B4: Term (Years)
• Cell C4: 30
• Cell B5: Monthly Payment

In cell C5, enter the PMT formula to calculate the monthly payment. This formula calculates the payment for a loan based on constant payments and a constant interest rate.

=PMT(C3/12, C4*12, -C2)

Let’s quickly break down the formula:

• C3/12: This is the monthly interest rate (we divide the annual rate by 12).
• C4*12: This is the total number of payments (the number of years multiplied by 12).
• -C2: The loan amount, entered as a negative number because it's a cash outflow.

Once you press Enter, cell C5 should show the result, which in this case is $1,829.49. This is our baseline calculation.

Step 2: Structure Your Data Table Layout

Now, create the structure for the Data Table. We want to test different interest rates, so we'll list them in a single column.

1. In a column nearby, say starting in cell E3, list all the interest rates you want to test. For example: F3: 3.50%, F4: 3.75%, F5: 4.00%, and so on, up to 7.00%. This is your list of input values.
2. This next step is crucial. In the cell to the right and one row above your list of input values (cell F2 in our example), you must link directly to your original formula's output. So, in cell F2, type =C5 and press Enter. This tells the Data Table which final calculation you want to run for each variable.

Your layout should now have a column of interest rates, with the original monthly payment formula result sitting at the top right.

Step 3: Generate the Data Table

With the structure in place, telling Excel to fill in the results takes just a few clicks.

1. Select the entire range of your Data Table, including the formula reference at the top and the column of input values. In our example, this would be the range E2:F17.
2. Go to the Data tab on the Ribbon.
3. In the Forecast group, click on What-If Analysis, and then select Data Table... from the dropdown.
4. A small dialog box will pop up with two fields: "Row input cell" and "Column input cell."
5. Because our variable values (the interest rates) are arranged in a column, we will use the Column input cell field. Click inside this field, and then click on the original input cell that your formula uses - in this case, cell C3, which contains our baseline 4.75% interest rate.
6. Leave the "Row input cell" blank. Click OK.

Instantly, Excel will populate the cells next to your interest rates with the corresponding monthly payment for each one. It works by taking each value from your input column (cell range E3:E17), substituting it into the original input cell (C3), recalculating the formula in C5, and displaying the result in your table.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Build a Two-Variable Data Table

Things get even more interesting with a two-variable table, which lets you see how changing two inputs at the same time affects the outcome. Let's expand our loan example to analyze how both the interest rate and the loan term affect the monthly payment.

Step 1: Set Up the Two-Variable Structure

The layout for a two-variable table is a bit different. One variable will run down a column, and the second will run across a row.

1. Keep your column of interest rates as it was (from E3 down).
2. Now, list your second variable - the loan term in years - across the top row, starting one cell to the right of your formula link. For example, in cells F2, G2, H2, and I2, enter 15, 20, 25, and 30.
3. This is the most critical step: In the top-left corner of the table structure (the cell at the intersection of your row and column variables), you need to reference the original formula output. In our example, this would be cell E2. So, in cell E2, type =C5.

Your table should now look like a grid, with the formula result in the corner, interest rates running down the side, and loan terms running across the top.

Step 2: Generate the Table

The process is nearly identical to before, but this time we'll fill in both fields in the dialog box.

1. Select the entire table range, including the corner formula, the row of terms, and the column of interest rates. In this case, that's range E2:I17.
2. Go to Data > What-If Analysis > Data Table...
3. Now, fill in both fields:
4. Click OK.

Excel will instantly fill in the entire grid with values, showing you the exact monthly payment for every single combination of interest rate and loan term. You've just performed dozens of calculations in a few seconds.

Tips and Best Practices for Data Tables

Data Tables are fantastic, but there are a few things to keep in mind to get the most out of them.

Performance Can Be a Concern

Data Tables are calculation-heavy. If you have a large Data Table linked to a complex model, it can slow down your workbook because Excel has to recalculate everything every time you make any change on the sheet. To manage this, you can adjust Excel's calculation settings. Go to File > Options > Formulas and under Calculation options, select Automatic Except for Data Tables. Now, your Data Tables will only update when you manually press the F9 key to recalculate.

Graphed

Still Building Reports Manually?

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

Watch the Demo

You Can't Edit Individual Results

Once a Data Table is created, you cannot change or delete a single result cell within it. The results are generated by an array formula: {=TABLE(C4,C3)}. The entire block of results is treated as one locked unit. To modify the outcomes, you must either change the original formula (in our example, the PMT formula in C5) or change the input variables listed in the table's row/column headers.

Remember to Format Your Results

Excel doesn't automatically apply formatting to the data table results. The cells will just contain raw numbers. To make your table easier to read, select your results and apply the correct number formatting. For our example, highlighting the results and applying the 'Currency' format would make it much more presentable.

Final Thoughts

Excel's Data Tables are a fantastic tool for getting a comprehensive view of potential outcomes, saving you from repetitive manual work and helping you understand the sensitivity of your models. Mastering them allows you to quickly answer key business questions and analyze scenarios with more confidence.

Analyzing scenarios is critical for making smart decisions, but it can be a slow, manual process when you have to first export performance data from a dozen different marketing and sales platforms just to create your model in a spreadsheet. Since we lived this frustration ourselves, we built Graphed to dramatically simplify this process. We let you connect places like Google Analytics, Shopify, and Salesforce directly, so you can build real-time dashboards and model scenarios using simple questions in plain English instead of building complex spreadsheets. It's like having a data analyst on your team who's ready 24/7 to provide instant answers.