Data Tables (One and Two Variable Sensitivity Analysis)0%

Removing Duplicates and Cleaning Invisible Spaces

Data Tables (One and Two Variable Sensitivity Analysis)

Beginner12 min read•Updated: Jul 11, 2026

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

1   =B5

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

1
Highlight the entire matrix range: D2:E7.
2
Go to Data > What-If Analysis > Data Table....
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
Click OK.

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

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):

1Two-Variable Matrix Layout:
2Corner Cell (C2): =B5 (The formula to evaluate)
3               | Term 36  | Term 48  | Term 60  | Term 72  |
4+--------------+----------+----------+----------+----------+
5| Rate 5.0%    | $899     | $691     | $566     | $483     |
6| Rate 6.5%    | $921     | $713     | $589     | $506     |
7| Rate 8.0%    | $942     | $736     | $612     | $530     |
8+--------------+----------+----------+----------+----------+

Execution Steps:

1
In the top-left corner of the grid (cell C2), enter '=B5' (the payment formula).
2
Enter Interest Rates down Column C ('C3:C7').
3
Enter Loan Terms across Row 2 ('D2:G2').
4
Select the full rectangular block: C2:G7.
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
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!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What analytical task are Excel Data Tables primarily used for?

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Removing Duplicates and Cleaning Invisible Spaces

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Scenario Manager for Multi-Model Budgeting	Removing Duplicates and Cleaning Invisible Spaces

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Removing Duplicates and Cleaning Invisible Spaces

Data Tables (One and Two Variable Sensitivity Analysis)

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Data Tables (One and Two Variable Sensitivity Analysis)

1. One-Variable Data Tables

Step-by-Step Setup:

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

1   =B5

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

1
Highlight the entire matrix range: D2:E7.
2
Go to Data > What-If Analysis > Data Table....
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
Click OK.

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

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):

1Two-Variable Matrix Layout:
2Corner Cell (C2): =B5 (The formula to evaluate)
3               | Term 36  | Term 48  | Term 60  | Term 72  |
4+--------------+----------+----------+----------+----------+
5| Rate 5.0%    | $899     | $691     | $566     | $483     |
6| Rate 6.5%    | $921     | $713     | $589     | $506     |
7| Rate 8.0%    | $942     | $736     | $612     | $530     |
8+--------------+----------+----------+----------+----------+

Execution Steps:

1
In the top-left corner of the grid (cell C2), enter '=B5' (the payment formula).
2
Enter Interest Rates down Column C ('C3:C7').
3
Enter Loan Terms across Row 2 ('D2:G2').
4
Select the full rectangular block: C2:G7.
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
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!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What analytical task are Excel Data Tables primarily used for?

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Removing Duplicates and Cleaning Invisible Spaces

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Scenario Manager for Multi-Model Budgeting	Removing Duplicates and Cleaning Invisible Spaces

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next