MS Excel - Excel What-If Analysis Using Data Tables

A Data Table in Microsoft Excel is a What-If Analysis tool used to examine how changing one or two input values affects the result of a formula. It is particularly useful when you want to compare many possible outcomes without creating a separate formula for every possibility.

For example, suppose you have calculated the monthly loan payment based on the loan amount and interest rate. Instead of changing the interest rate manually each time, you can create a Data Table to see the monthly payment at several different interest rates at once.

1. What Is What-If Analysis?

What-If Analysis is a group of Excel features that allows you to examine how changing input values can affect the results of a calculation.

The basic idea is:

“What will happen if I change this value?”

For example:

What happens to profit if the selling price increases?
What happens to EMI if the interest rate changes?
What happens to total revenue if the number of units sold changes?
What happens to loan repayment if the loan period changes?

A Data Table helps answer these questions systematically.

2. What Is a Data Table?

An Excel Data Table is a special What-If Analysis tool that displays multiple results from a single formula by substituting different values for one or two input cells.

There are two types:

One-variable Data Table
Two-variable Data Table

A one-variable Data Table changes one input value at a time.

A two-variable Data Table changes two input values and shows the resulting combinations.

3. One-Variable Data Table

A one-variable Data Table is used when you want to see how changing one input affects the result.

For example, imagine that a company sells a product for ₹500 per unit.

Suppose:

Selling price = ₹500
Units sold = 1,000
Cost per unit = ₹300

Revenue is:

=Selling Price * Units Sold

The company may want to know how revenue changes when the number of units sold is 500, 750, 1,000, 1,250, or 1,500.

Instead of creating separate calculations, a one-variable Data Table can display all the results.

4. Example of a One-Variable Data Table

Suppose cell B2 contains the number of units sold.

Cell B3 contains the selling price.

Cell B4 calculates revenue:

=B2*B3

You could create a list such as:

Units Sold	Revenue
500
750
1,000
1,250
1,500

The Data Table automatically calculates the revenue for each units-sold value.

This allows you to understand the effect of sales volume on revenue.

5. Creating a One-Variable Data Table

First, create your original calculation.

For example:

B2 = 1000
B3 = 500
B4 = B2*B3

Here, B4 contains the revenue.

Next, place different possible values in a column.

For example:

D2 = Units Sold
D3 = 500
D4 = 750
D5 = 1000
D6 = 1250
D7 = 1500

In the cell immediately above the results column, enter a reference to the original formula:

E2 = B4

The arrangement becomes:

D	E
Units Sold	Revenue
500
750
1,000
1,250
1,500

Select the complete Data Table range.

Then go to:

Data → What-If Analysis → Data Table

In the Data Table dialog box, identify the input cell that should be replaced by the values in your table.

Since the values represent different numbers of units sold, select the cell containing the original units-sold value, such as B2, as the Column input cell.

Click OK.

Excel calculates the corresponding revenue for every value.

6. Why the Input Cell Is Important

The input cell tells Excel:

“Replace this cell with each value in my Data Table and calculate the result.”

For example, if B2 contains the number of units sold, Excel temporarily substitutes:

into B2.

It then recalculates the formula in B4 for each value.

The original worksheet value is not permanently replaced by the Data Table calculations.

7. Two-Variable Data Table

A two-variable Data Table is useful when two different inputs affect the same result.

For example, a business may want to examine revenue based on:

Different selling prices
Different quantities sold

Suppose the selling prices are:

₹400
₹450
₹500
₹550
₹600

And quantities are:

A two-variable Data Table can calculate revenue for every combination.

The result might look conceptually like this:

Units / Price	₹400	₹450	₹500	₹550	₹600
500	2,00,000	2,25,000	2,50,000	2,75,000	3,00,000
750	3,00,000	3,37,500	3,75,000	4,12,500	4,50,000
1,000	4,00,000	4,50,000	5,00,000	5,50,000	6,00,000
1,250	5,00,000	5,62,500	6,25,000	6,87,500	7,50,000
1,500	6,00,000	6,75,000	7,50,000	8,25,000	9,00,000

This gives a complete view of how changing both variables affects revenue.

8. Creating a Two-Variable Data Table

Suppose:

B2 = Units Sold
B3 = Selling Price
B4 = B2*B3

Create a table where the possible selling prices are placed across the top row and possible quantities are placed down the first column.

For example:

	₹400	₹450	₹500	₹550
500
750
1,000
1,250

The top-left cell of this table should contain a reference to the formula result, such as:

=B4

Select the entire table.

Then choose:

Data → What-If Analysis → Data Table

In the dialog box:

Set the Row input cell to the original selling-price cell.
Set the Column input cell to the original units-sold cell.

Click OK.

Excel calculates every combination.

9. Difference Between One-Variable and Two-Variable Data Tables

Feature	One-Variable Data Table	Two-Variable Data Table
Inputs changed	One	Two
Purpose	Study one factor	Study two factors
Layout	One row or one column of values	Row and column values
Example	Different interest rates	Interest rates and loan periods
Number of combinations	One set of alternatives	Multiple combinations

10. Loan Example

Data Tables are especially useful for financial calculations.

Suppose you want to calculate monthly loan payments.

Assume:

Loan amount = ₹5,00,000
Annual interest rate = 8%
Loan period = 5 years

You could calculate the monthly payment using the PMT function:

=PMT(B3/12,B4*12,-B2)

Where:

B2 = Loan amount
B3 = Annual interest rate
B4 = Loan period in years

Now suppose you want to know how the payment changes when the interest rate is:

7%
8%
9%
10%
11%

A one-variable Data Table can calculate all these possibilities.

This is much faster than manually changing the interest rate and recording the result.

11. Two-Variable Loan Analysis

You could also examine both:

Interest rate
Loan period

For example:

Loan Period / Rate	7%	8%	9%	10%
3 years	Result	Result	Result	Result
5 years	Result	Result	Result	Result
7 years	Result	Result	Result	Result
10 years	Result	Result	Result	Result

This allows you to compare different borrowing scenarios in one worksheet.

12. Advantages of Data Tables

Data Tables provide several advantages.

Easy comparison

Multiple possibilities can be viewed together rather than being calculated separately.

Saves time

You do not have to repeatedly change input values manually.

Supports decision-making

Businesses can compare different prices, sales volumes, costs, interest rates, or other variables.

Reduces repetitive formulas

A single formula can be used to evaluate many possible input combinations.

Useful for financial planning

Data Tables are particularly useful for loans, investments, sales forecasts, budgets, and profitability analysis.

13. Limitations of Data Tables

Data Tables also have some limitations.

A two-variable Data Table can examine only two changing input variables at a time.

Large Data Tables can require additional calculation time because Excel must calculate many combinations.

They are primarily designed for sensitivity analysis rather than permanently changing the underlying model.

If you need to change several variables simultaneously or create more complex scenarios, other Excel What-If Analysis tools may be more appropriate.

14. Data Table vs Scenario Manager

Although both are part of What-If Analysis, they work differently.

Data Table is useful for systematically examining many combinations of one or two variables.

Scenario Manager is useful for storing and comparing predefined groups of input values.

For example, a business could use scenarios such as:

Best Case
Normal Case
Worst Case

A Data Table, on the other hand, could show profit for dozens of different combinations of price and sales volume.

15. Practical Applications

Data Tables can be used in many areas.

Business

Businesses can analyze how changes in price and sales volume affect revenue.

Finance

Loan payments can be compared for different interest rates and repayment periods.

Investment

Potential returns can be analyzed under different rates of return and investment amounts.

Marketing

Companies can examine how different advertising budgets and conversion rates affect expected revenue.

Education

Students can use Data Tables to understand how changes in variables affect mathematical and financial formulas.

Budgeting

Organizations can examine how changes in expenses and income affect their overall budget.

16. Important Points to Remember

A Data Table is part of Excel's What-If Analysis functionality.
It is used to study the effect of changing input values.
A one-variable Data Table changes one input.
A two-variable Data Table changes two inputs.
The original formula must be available for the Data Table to calculate results.
The Row input cell is used for values arranged horizontally.
The Column input cell is used for values arranged vertically.
Data Tables are useful for sensitivity analysis.
They are particularly useful for financial, business, sales, and budgeting calculations.
Data Tables should not be confused with ordinary Excel tables used for organizing data.

Conclusion

Excel Data Tables provide a convenient way to perform What-If Analysis by showing how changes in one or two input values affect a formula's result. Instead of repeatedly modifying a worksheet and recording the output, users can construct a Data Table that automatically evaluates multiple possibilities.

For example, a business can determine how different combinations of selling price and sales volume affect revenue, while a borrower can examine how interest rate and loan duration affect monthly payments. This makes Data Tables a valuable tool for comparison, sensitivity analysis, planning, and informed decision-making.