Master Data Table mechanics: One-variable and two-variable sensitivity.
A Data Table is Excel's way of automating sensitivity analysis. It takes your existing formula (like the CVP profit calculation from Lesson 5) and recalculates it for as many input values as you want—all at once.
One-Variable Data Table
Use when you want to test ONE input (like price) against many values:
Location: Data → What-If Analysis → Data Table → Column Input Cell = Price cell
Two-Variable Data Table
Use when you want to test TWO inputs (like price AND volume) at once:
Row Input Cell = Volume | Column Input Cell = Price
Error #1: Formula Not in the Right Spot
The formula must be positioned above the column values (for column input) or to the left of the row values (for row input). If it's not adjacent, Excel can't find the calculation base.
Error #2: Wrong Input Cell Reference
When you select the Data Table dialog, you must point to the original input cell in your CVP model (not a new cell). If you reference the wrong cell, the table calculates nothing or wrong values.
Error #3: Array Formula Confusion
Data Tables create array results. You cannot edit or delete individual cells in a Data Table result range. To clear, select the entire table and delete.
1. In a one-variable Data Table, the formula cell must be located:
2. When setting up a two-variable Data Table, which cell is the 'Row Input Cell'?
Ready to Practice?
In Guided Practice, you'll use our simulator to build a Data Table step-by-step before touching the real Excel workbook.