Data Tables are a What-If Analysis tool used to see how changing one or two input variables affects a formula result.
They are commonly used in financial modeling, forecasting, and sensitivity analysis.
Why Use Data Tables?
- Analyze different scenarios
- Perform sensitivity analysis
- Compare multiple outcomes
- Support decision-making
- Build financial models
Types of Data Tables
One-Variable Data Table
Used when you change one input variable to see multiple results.
Example:
Suppose:
- Cell B1 = Price
- Cell B2 = Quantity
- Cell B3 = Total Sales
- Formula in B3: =B1*B2
You want to test different values of Price.
Steps for One-Variable Data Table
- Enter formula reference (e.g., =B3) in a new cell
- List possible input values vertically or horizontally
- Select entire table area
- Go to Data → What-If Analysis → Data Table
- Choose:
- Column Input Cell (if values are vertical)
- Row Input Cell (if values are horizontal)
- Click OK
Excel calculates results for each input automatically.
Two-Variable Data Table
Used when you change two input variables at the same time.
Example:
Test:
- Different Prices
- Different Quantities
And see Total Sales results.
Steps for Two-Variable Data Table
- Enter formula reference at top-left corner
- List first variable values in first column
- List second variable values in first row
- Select full table area
- Go to Data → What-If Analysis → Data Table
- Set:
- Row Input Cell
- Column Input Cell
- Click OK
Excel generates a full result grid.
Important Rules
- The formula must reference input cells
- Do not manually edit result cells
- Data Tables automatically recalculate
- Works best with numeric inputs
Data Table vs Goal Seek
| Data Table | Goal Seek |
|---|---|
| Multiple results | One result |
| Scenario comparison | Target-based calculation |
| One or two variables | Only one variable |
Common Errors
- Incorrect formula reference
- Wrong input cell selection
- Overwriting result cells
- Circular reference issues
Practical Uses
- Loan EMI comparison
- Break-even analysis
- Profit forecasting
- Investment return scenarios
- Pricing strategy analysis
Benefits of Data Tables
- Quick scenario testing
- Better financial planning
- Clear comparison of results
- Professional modeling tool
- Improves decision-making
Conclusion
Data Tables are a powerful What-If Analysis feature in Excel. They allow users to evaluate multiple scenarios quickly and efficiently, making them essential for financial modeling and business analysis.