In Excel, a data table is a What-If Analysis tool that shows how changing one or two inputs affects a formula’s result. Instead of retyping an interest rate ten times to see ten different loan payments, you list the rates once and Excel fills in every answer at the same time.
This guide walks through a one-variable data table step by step, then covers row-oriented and two-variable tables, recalculation settings and common errors. If you were looking for a regular formatted table with filters and banded rows, see the note at the end.
Set Up a Model First
A data table needs a working formula that depends on an input cell. This example calculates a monthly loan payment:
| Cell | Label | Value |
|---|---|---|
| B1 | Loan amount | 25000 |
| B2 | Annual interest rate | 6% |
| B3 | Term (months) | 60 |
| B4 | Monthly payment | =PMT(B2/12,B3,-B1) |
B4 returns about $483. Now you can test what the payment would be at different interest rates without changing B2.
How to Create a One-Variable Data Table
Step 1: List the input values
In an empty column, type the values you want to test, one per cell. For example, enter 4%, 5%, 6%, 7% and 8% in D3:D7.
Step 2: Reference the formula
In the cell one row above and one column to the right of the first value, here E2, enter =B4. This tells the data table which result to calculate.
Step 3: Select the range
Select D2:E7, which includes the formula row and the column of input values.
Step 4: Open the Data Table dialog
Go to Data > What-If Analysis > Data Table. In Excel for Microsoft 365, What-If Analysis is in the Forecast group.
Step 5: Set the column input cell
Because your test values run down a column, click in the Column input cell box and select B2, the cell the values should replace. Leave Row input cell empty and click OK.
Step 6: Read the results
Excel fills E3:E7 with the monthly payment for each rate. Change the loan amount in B1 and every result updates. Format the results as currency so they’re easy to read.
Row-Oriented Data Tables
If you’d rather list your inputs across a row, put the values in a row (for example E1:I1), put the formula reference one column to the left and one row below (D2), select D1:I2, and fill in the Row input cell instead.
Test More Than One Formula
A one-variable table can show several results side by side. Add another formula reference to the right of the first one in the top row, such as =B4*B3 in F2 for the total amount paid, then select the larger range (D2:F7) before opening the Data Table dialog.
How to Create a Two-Variable Data Table
To see how two inputs interact, such as interest rate and loan term, place the formula reference in the top-left corner, one set of values across the top row and the other down the first column. Select the whole block, then set both the Row input cell (B3 for terms) and the Column input cell (B2 for rates). A full worked example is in creating a two-variable data table in Excel, and the same layout powers a sensitivity table.
Microsoft’s guide to calculating multiple results with a data table shows the same layouts with more examples.
Speed Up Workbooks with Large Data Tables
Data tables recalculate every time anything on the sheet changes, which can slow big models down. Go to Formulas > Calculation Options and choose Automatic Except for Data Tables. Press F9 whenever you want the tables to update.
Edit or Delete a Data Table
- The results are created with a
{=TABLE(,B2)}array formula, so you can’t edit or delete a single result cell. - To change inputs, edit the values in the input column or row. The results update automatically.
- To remove the table, select all of the result cells and press Delete. To keep the numbers, copy the results and use Paste Special > Values first.
Troubleshooting Data Tables
- “Input cell reference is not valid.” The input cell must be on the same worksheet as the data table. Move the table, or link a cell on the same sheet to your input.
- Every result is the same. You chose the wrong input cell, or the formula doesn’t actually depend on it. Check that the formula refers to the input cell directly or through other formulas.
- Results don’t update. Calculation is set to Automatic Except for Data Tables, or Manual. Press F9.
- “Cannot change part of a data table.” You tried to edit or delete one result cell. Select the whole result range instead.
- The table shows the input values instead of results. The formula reference cell is missing or in the wrong place. For a column table, it must sit one row above and one column right of the first input value.
- Data Table is greyed out. You’re editing a cell, the sheet is protected, or you’re in Excel for the web, which may not offer every What-If Analysis tool. Use the desktop app.
Looking for a Regular Excel Table?
Some people say “data table” when they mean a formatted table with filter buttons and banded rows. To create one, click any cell in your data and press Ctrl + T (or go to Insert > Table), confirm the range, and check My table has headers. There is more on creating a table in Excel with multiple columns and rows, and on undoing a table in Excel if you want to convert it back to a normal range.
Frequently Asked Questions
What is a data table used for?
It answers “what if” questions, such as how a payment, profit or price changes as an input moves. It’s common in loan comparisons, budgeting and pricing models.
How many variables can a data table handle?
One or two. For more inputs, use Scenario Manager or build separate tables.
Can the input cell be on another sheet?
No. The input cells must be on the same sheet as the data table.
Does a data table work on a Mac?
Yes. In Excel for Mac, go to Data > What-If Analysis > Data Table. The steps are the same.
What’s the difference between a data table and Goal Seek?
A data table shows results for many inputs at once. Goal Seek works backward from a single target result to find the input that produces it.
Wrapping Up
Build a model with a clear input cell, list the values to test, reference the formula, and run Data > What-If Analysis > Data Table. Use a column input cell for values running down, a row input cell for values running across, and both for a two-variable table.
Data tables sit alongside the other tools in What-If Analysis in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.