Course: Advanced Excel
Topic: Creating One-Variable and Two-Variable Data Tables
Course Link: Advanced Excel Course – JustAcademy
Demo Link: Register for a Course Demo
Website: JustAcademy Official Website
Microsoft Excel provides What-If Analysis tools that help users understand how changing input values affects calculated results. One of these tools is the Data Table feature, which automatically calculates multiple possible outcomes by substituting different values into a formula.
Data Tables are especially useful when working with financial models, sales forecasts, loan calculations, budgets, profit analysis, investment planning, and business reports. Instead of changing an input value repeatedly and recording each result manually, you can create a table that displays several scenarios at once.
Excel supports two main types of Data Tables:
After studying this topic, you will be able to:
What-If Analysis is a group of Excel tools that helps users evaluate how different assumptions affect calculated results. It is useful when the final answer depends on variables that may change.
For example, a business may want to know how its profit changes when the selling price, number of units sold, or production cost changes. Instead of manually editing the formula inputs for every possible scenario, What-If Analysis can compare multiple possibilities efficiently.
Common What-If Analysis tools include:
| Tool | Purpose | Example |
|---|---|---|
| Data Table | Calculates outcomes for one or two changing inputs. | Comparing profit at different prices. |
| Scenario Manager | Stores and compares groups of input assumptions. | Best-case, expected-case, and worst-case budgets. |
| Goal Seek | Finds the input value needed to achieve a target result. | Finding the sales quantity required for a target profit. |
| Solver | Finds an optimized solution subject to constraints. | Maximizing profit within a production limit. |
A One-Variable Data Table calculates the result of a formula for multiple values of a single input cell. Excel temporarily substitutes each value into the selected input cell and records the formula result.
For example, if a company wants to calculate profit at selling prices of ₹100, ₹110, ₹120, ₹130, and ₹140, it can use a one-variable Data Table to calculate all five outcomes.
Suppose a company sells a product. Its profit depends on the selling price, number of units sold, and variable cost per unit.
| Cell | Description | Value or Formula |
|---|---|---|
| B2 | Selling Price per Unit | ₹150 |
| B3 | Units Sold | 500 |
| B4 | Variable Cost per Unit | ₹90 |
| B5 | Fixed Costs | ₹10,000 |
| B6 | Total Revenue | =B2*B3 |
| B7 | Total Variable Cost | =B3*B4 |
| B8 | Total Profit | =B6-B7-B5 |
With the initial values, the profit is ₹20,000. The objective is to determine how profit changes when the selling price changes while the other assumptions remain constant.
Arrange the possible selling prices vertically. Place a reference to the profit formula in the cell above and to the right of the first input value.
| D | E |
|---|---|
| Profit Formula | =B8 |
| ₹100 | |
| ₹110 | |
| ₹120 | |
| ₹130 | |
| ₹140 |
For this example, assume the formula reference is entered in E2 and the selling price scenarios are entered in D3:D7. The Data Table output will appear in E3:E7.
=B8 in cell E2 to reference the original profit calculation.$B$2 in the Column input cell field.Excel substitutes each value from D3:D7 into cell B2, recalculates the profit formula, and displays the results in E3:E7.
| Selling Price | Units Sold | Variable Cost per Unit | Fixed Costs | Calculated Profit |
|---|---|---|---|---|
| ₹100 | 500 | ₹90 | ₹10,000 | ₹0 |
| ₹110 | 500 | ₹90 | ₹10,000 | ₹5,000 |
| ₹120 | 500 | ₹90 | ₹10,000 | ₹10,000 |
| ₹130 | 500 | ₹90 | ₹10,000 | ₹15,000 |
| ₹140 | 500 | ₹90 | ₹10,000 | ₹20,000 |
Interpretation: When the number of units sold, variable cost per unit, and fixed costs remain constant, increasing the selling price increases profit. The Data Table makes the relationship easy to compare without manually changing the original selling price.
The correct input cell depends on where the scenario values are arranged in the Data Table.
| Arrangement | Input Cell to Use | Example |
|---|---|---|
| Scenario values listed vertically in a column | Column input cell | Different interest rates listed down a column. |
| Scenario values listed horizontally in a row | Row input cell | Different prices listed across a row. |
The terms Row input cell and Column input cell refer to the direction of the scenario values in the Data Table, not necessarily the orientation of the original input cell in the worksheet.
If the possible selling prices are arranged horizontally, place the formula reference in the cell immediately above and to the left of the first scenario value. Select the entire table, open Data > What-If Analysis > Data Table, and enter the original selling price input cell in the Row input cell field.
For example, if the selling prices are arranged across E2:I2 and the formula reference is in D2, use the original selling price cell B2 as the Row input cell. Leave the Column input cell blank.
A Two-Variable Data Table calculates the results of a formula by changing two input cells. One set of possible values is arranged horizontally across the top row, while the other set is arranged vertically down the first column.
For example, a company may want to compare profit at different selling prices and different sales quantities. A two-variable Data Table shows the profit for every combination of those two inputs.
Use the same profit model, but this time vary both the selling price and the number of units sold. Keep the variable cost per unit at ₹90 and fixed costs at ₹10,000.
Enter the selling prices across the top row and the number of units sold down the first column. In the upper-left corner of the table, enter a reference to the original profit formula.
| Units / Price | ₹100 | ₹110 | ₹120 | ₹130 |
|---|---|---|---|---|
| 400 | ||||
| 500 | ||||
| 600 | ||||
| 700 |
For this layout, assume the formula reference is in D2, selling prices are in E2:H2, and units sold are in D3:D6. The results will fill E3:H6.
=B8 in D2.$B$2 in the Row input cell field because the selling prices are arranged horizontally.$B$3 in the Column input cell field because the sales quantities are arranged vertically.| Units Sold / Price | ₹100 | ₹110 | ₹120 | ₹130 |
|---|---|---|---|---|
| 400 | -₹6,000 | -₹2,000 | ₹2,000 | ₹6,000 |
| 500 | ₹0 | ₹5,000 | ₹10,000 | ₹15,000 |
| 600 | ₹6,000 | ₹12,000 | ₹18,000 | ₹24,000 |
| 700 | ₹12,000 | ₹19,000 | ₹26,000 | ₹33,000 |
Interpretation: The table shows how profit changes when both selling price and sales quantity change. For example, selling 600 units at ₹120 produces a profit of ₹18,000 under the assumptions in this model. Selling 700 units at ₹130 produces a profit of ₹33,000.
| Feature | One-Variable Data Table | Two-Variable Data Table |
|---|---|---|
| Number of changing inputs | One | Two |
| Scenario layout | One row or one column of input values | One row and one column of input values |
| Input cells required | One | Two |
| Output | One result per input value | One result per combination of two input values |
| Complexity | Relatively simple | More detailed comparison |
| Example | Profit at different selling prices | Profit at different prices and sales quantities |
After creating the Data Table, format the results to make the information easier to understand.
A financial analyst can compare monthly loan payments at different interest rates or loan periods. For a two-variable Data Table, the interest rate can be placed across the top and the loan term down the first column, with a payment formula in the upper-left corner.
For example, the Excel PMT function can calculate a periodic payment:
=PMT(Annual_Interest_Rate/12,Loan_Years*12,Loan_Amount)Use consistent units and ensure the interest rate is entered as a percentage. The PMT result normally appears as a negative cash flow when the loan amount is positive; use =-PMT(...) if you want to display the payment as a positive number.
Sales teams can compare revenue for different prices and expected sales quantities. This helps identify which combinations may meet a revenue target.
Revenue formula:
=Selling_Price*Units_SoldFinance teams can test the impact of changes in salaries, operating costs, marketing expenditure, or expected revenue. Data Tables help show how sensitive a budget is to changes in key assumptions.
Investors can evaluate possible future values under different return rates and time periods. The results are scenario estimates, not guarantees of actual investment performance.
| Problem | Possible Cause | Solution |
|---|---|---|
| All results are identical | Wrong input cell selected or formula does not depend on the input. | Verify the input cell and confirm that the result formula uses it. |
| Results are incorrect | Incorrect row or column input cell assignment. | Match the input-cell field to the orientation of the scenario values. |
| Table does not calculate | Incorrect range selected or formula reference missing. | Select the complete table and check the formula in the upper-left corner. |
| Unexpected zero values | Formula references blank or incorrect cells. | Check the original model and all formula references. |
| Data Table is slow | Large table or complex workbook formulas. | Reduce unnecessary scenarios and review workbook calculation settings. |
| Formula appears instead of result | Cell formatted as Text or Show Formulas enabled. | Change the format to General, re-enter the formula if needed, and check Show Formulas. |
| Results do not update as expected | Calculation mode or workbook dependencies may affect recalculation. | Check Formulas > Calculation Options and use Automatic calculation when appropriate. |
Create a profit model with a selling price of ₹250, 300 units sold, a variable cost of ₹150 per unit, and fixed costs of ₹12,000. Create a one-variable Data Table using selling prices of ₹200, ₹220, ₹240, ₹260, and ₹280.
Tasks:
Use the same profit model and compare selling prices of ₹200, ₹220, ₹240, and ₹260 with sales quantities of 250, 300, 350, and 400 units.
Tasks:
Build a loan model using a principal of ₹500,000. Compare annual interest rates of 7%, 8%, 9%, and 10%, and loan periods of 5, 10, 15, and 20 years. Use the PMT function to calculate monthly payments.
Tasks:
Project Objective: Create a worksheet that helps a business understand how selling price and sales volume affect profit.
Answer: A Data Table is a What-If Analysis tool that calculates formula results for multiple values of one or two input cells.
Answer: It evaluates a formula by changing one input cell across a list or row of possible values.
Answer: It evaluates a formula for combinations of values from two input cells, with one set arranged horizontally and the other vertically.
Answer: The Row input cell is used when scenario values are arranged horizontally. The Column input cell is used when scenario values are arranged vertically.
Answer: No. A one-variable Data Table uses either the Row input cell or the Column input cell.
Answer: It tells Excel which formula result must be recalculated for each input value or combination of input values.
Answer: A Data Table compares results across a structured set of one or two input variables. Scenario Manager stores and compares groups of assumptions, which may involve several changing cells.
Answer: The input cell may be incorrect, or the formula may not depend on the cell being changed. Verify the model and input-cell selection.
Answer: Yes. They are useful for comparing loan payments, pricing strategies, budgets, profit margins, and investment scenarios.
Answer: No. They calculate outcomes from the formulas and assumptions provided. Actual results may differ if the assumptions change or real-world conditions are different.
One-Variable and Two-Variable Data Tables are valuable Advanced Excel tools for scenario analysis and decision-making. A one-variable Data Table shows how changing a single input affects a formula, while a two-variable Data Table compares the combined effect of two changing inputs.
By applying these tools to profit models, loan payments, budgets, and sales forecasts, Excel users can compare alternatives efficiently, identify important assumptions, and make more informed business decisions. Always verify the original formula, select the correct input cells, and interpret the results within the limits of the model.
Explore the Advanced Excel course to strengthen your skills in formulas, What-If Analysis, reporting, dashboards, and business data analysis.
Course: Explore the Advanced Excel Course
Demo Registration: Register for a Course Demo
Official Website: Visit JustAcademy
