Data Tables (One and Two Variable Sensitivity Analysis)

While Scenario Manager compares distinct qualitative cases, Data Tables perform rigorous Sensitivity Analysis. In seconds, a Data Table calculates 50, 100, or 500 combinations of input variables and displays the outcomes in a structured matrix.

1. One-Variable Data Tables

A One-Variable Data Table tests multiple values for a single input variable (such as testing 10 different interest rates on loan repayments) and can evaluate multiple output formulas simultaneously.

Step-by-Step Setup:

1. 1
   List your input variations down a column (e.g., Interest Rates: 5%, 6%, 7%, 8%, 9% in 'D3:D7').
2. 2
   In the cell one row above and one column to the right ('E2'), reference your output formula:

(Where B5 contains '=PMT(Interest/12, Term, -Loan)').

1. 1
   Highlight the entire matrix range: D2:E7.
2. 2
   Go to Data > What-If Analysis > Data Table....
3. 3
   Because your interest rates are listed vertically down a column:

• Leave Row input cell blank.
• In Column input cell, select the original interest rate input cell: $B$2.

1. 1
   Click OK.

Excel populates the monthly payment for every interest rate instantly using the '=TABLE(, B2)' array formula!

Click to Zoom

2. Two-Variable Data Tables

A Two-Variable Data Table tests combinations of two independent variables simultaneously (e.g., varying Interest Rate across rows and Loan Term across columns):

Execution Steps:

1. 1
   In the top-left corner of the grid (cell C2), enter '=B5' (the payment formula).
2. 2
   Enter Interest Rates down Column C ('C3:C7').
3. 3
   Enter Loan Terms across Row 2 ('D2:G2').
4. 4
   Select the full rectangular block: C2:G7.
5. 5
   Open Data > What-If Analysis > Data Table...:

• Row input cell: Select the original Term input cell ($B$3) because terms run across the row.
• Column input cell: Select the original Rate input cell ($B$2) because rates run down the column.

1. 1
   Click OK.

The entire sensitivity matrix calculates in a fraction of a second!

3. Formatting the Top-Left Corner Cell

In a two-variable data table, the top-left corner cell displays the formula result (e.g., '$566.14'), which looks awkward in an executive table.

• Pro Formatting Trick: Right-click cell C2 > Format Cells > Number tab > Custom.
• Enter type: "Interest Term" (or ;;; to hide the text completely).
• The underlying formula continues to work, but the display shows an elegant header!

Multiple Choice Questions

1. What analytical task are Excel Data Tables primarily used for?

A. Spell-checking text B. Sensitivity analysis (calculating how varying inputs impact formula outcomes) C. Connecting to internet databases D. Deleting duplicate rows Answer: B Explanation: Data Tables automate sensitivity analysis by systematically evaluating formula outcomes across multiple input variations.

2. In a Two-Variable Data Table, where must the reference to the output formula be placed?

A. In the bottom-right corner B. In the top-left corner intersection of the row and column headers C. Anywhere on the sheet D. In cell A1 Answer: B Explanation: The corner cell at the intersection of the row headers and column headers must contain the reference to the primary formula being evaluated.

3. Which formula is generated by Excel inside the calculation cells of a Data Table?

A. =VLOOKUP() B. {=TABLE(row_input, col_input)} C. =SENSITIVITY() D. =MATRIX() Answer: B Explanation: Excel populates Data Tables using the special internal dynamic array formula {=TABLE(row_input, col_input)}.

4. Can you manually delete or edit a single individual cell inside an existing Data Table array?

A. Yes, just click and press Delete B. No, Excel displays "Cannot change part of a data table" C. Only with administrator permissions D. Yes, if you press Shift + Delete Answer: B Explanation: Because Data Tables exist as a single unified array calculation, individual cells cannot be edited or deleted independently.

5. If your input variables are arranged vertically down a single column, which field in the Data Table dialog must you populate?

A. Row input cell B. Column input cell C. Corner input cell D. Iteration cell Answer: B Explanation: When test variables run vertically down a column, they must be linked to the 'Column input cell' parameter.