How to Use Excel Data Tables to Analyze Multiple Scenarios at Once

What if you could test 10 different price points, profit margins, or interest rates—all without changing your formula even once?

That’s exactly what Excel Data Tables let you do. Whether you’re a business owner, financial analyst, or student, data tables are one of Excel’s most powerful—and most underused—what-if analysis tools.

In this guide, you’ll learn how to create both a one-variable and a two-variable data table in Excel, with a clear real-world example to make it easy to follow.

What Is an Excel Data Table?

An Excel Data Table is a range of cells that shows how changing one or two input values in your formula affects the results. Instead of manually editing a formula and recording each outcome, a data table does all the heavy lifting automatically.

There are two types:

• One-Variable Data Table—Changes a single input (like a price or interest rate) and shows how the result shifts.
• Two-Variable Data Table—Varies two inputs at the same time, giving you a full comparison grid.

Real-World Example: Bookstore Profit Calculator

Let’s walk through a simple scenario that makes this concept click immediately.

Imagine you own a bookstore with 100 books in storage. You sell some books at a high price of $50 and the rest at a lower price of $20.

The key question is how does the percentage you sell at the higher price affect your total profit?

Understanding the Profit Formula

Here’s the formula that drives everything:

In Excel, this is calculated with a formula like =(D7 * 100 * 50) + ((1 – D7) * 100 * 20), where D7 holds the percentage sold at the higher price.

How to Create a One-Variable Data Table in Excel

A one-variable data table lets you plug in a list of values for a single input—like testing profit at 10%, 20%, 30%… all the way to 100%—and see the result for each instantly.

Step-by-Step Instructions

1. Set up your formula. Enter your profit formula in a cell (e.g., D10). Make sure it references the input cell (e.g., D7 for the high-price percentage).
2. List your input values. In a column (e.g., F2:F11), type the percentage values you want to test—like 10%, 20%, …, 100%.
3. Reference your formula. In the cell one row above and one column to the right of your input list (e.g., G1), enter a reference to your formula cell: =D10.
4. Select the data table range. Highlight the full range, including both the input values and the formula reference (e.g., F1:G11).
5. Open What-If Analysis. Go to the Data tab → Forecast group → What-If Analysis → Data Table.
6. Set the Column Input Cell. Since your test values are in a column, enter D7 as the Column Input Cell. Click OK.

Excel will instantly fill in the profit for every percentage value you listed. No manual work required.

How to Create a Two-Variable Data Table in Excel

Want to test two things at once? A two-variable data table lets you vary both the high-price percentage and the high-price amount simultaneously—giving you a full grid of outcomes.

For example, what’s the total profit if you sell 40%, 50%, or 60% of books at $40, $45, $50, or $55?

Step-by-Step Instructions

• Use the same profit formula in cell D10, referencing both input cells (D7 for % and D5 for high price).
• Set up rows and columns. List percentage values down a column (e.g., F2:F6) and price values across a row (e.g., G1:J1).
• Place the formula at the intersection. In cell F1 (top-left corner of the table), enter =D10.
• Select the entire table range (e.g., F1:J6) and open What-If Analysis → Data Table.
• Set both input cells. Row Input Cell = D5 (high price). Column Input Cell = D7 (percentage). Click OK.

You now have a complete matrix showing profit at every combination of price and percentage. This is incredibly useful for pricing strategy, sales planning, or any scenario with two key variables.

Pro Tips for Using Excel Data Tables Effectively

• Data tables auto-recalculate when you change the underlying formula or inputs—no need to rebuild them.
• You can’t delete individual cells inside a data table. If you want to remove it, select the entire output range and delete it.
• Use conditional formatting on top of your data table to highlight the best-performing values with color-coded cells.
• Combine with Scenario Manager or Goal Seek for even more powerful what-if analysis in Excel.

Frequently Asked Questions (FAQ)

What is a data table in Excel used for?

A data table in Excel is used for what-if analysis. It automatically calculates results for multiple input values, so you can compare scenarios without writing separate formulas.

What is the difference between a one-variable and two-variable data table?

A one-variable data table tests a range of values for a single input. A two-variable data table tests combinations of two different inputs at the same time, displaying results in a grid.

Can I use a data table with any Excel formula?

Yes. As long as your formula references one or two input cells, you can use it as the basis for a data table. It works with financial formulas, statistical calculations, and custom business models.

Why is my data table not working in Excel?

The most common reasons are the formula reference isn’t in the correct position (one row above and one column to the right of inputs), the wrong input cell is selected, or automatic calculation is turned off. Go to Formulas → Calculation Options → Automatic to fix the last issue.

Final Thoughts

Excel Data Tables are a game-changer for anyone who needs to make data-driven decisions quickly. Instead of running scenarios one by one, you get a complete picture in seconds.

Whether you’re analyzing sales pricing like in our bookstore example, projecting investment returns, or modeling business growth, mastering data tables will save you time and make your spreadsheets far more powerful.

Ready to try it? Open Excel, build the bookstore model, and run your first data table in under 5 minutes. You’ll wonder how you ever analyzed data without it.