Data Tables (What-If) in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Data Table in Excel's What-If Analysis?

A Data Table lets you see how changing one or two input values affects the result of a formula. It helps you explore different scenarios quickly.

beginner

How many input variables can a one-variable Data Table analyze?

A one-variable Data Table analyzes how changing one input value affects the formula result.

intermediate

What is the difference between a one-variable and a two-variable Data Table?

A one-variable Data Table changes one input to see its effect. A two-variable Data Table changes two inputs at the same time to see how both affect the result.

beginner

Where do you place the formula when creating a Data Table?

You place the formula in the top-left cell of the Data Table range, just above or beside the input values.

beginner

Why should you use Data Tables instead of manually changing inputs one by one?

Data Tables automatically calculate and show many results at once, saving time and reducing errors compared to changing inputs manually.

What does a one-variable Data Table in Excel analyze?

A Effect of changing one input on a formula

B Effect of changing two inputs on a formula

C Effect of changing all inputs on a formula

D Effect of changing no inputs

A one-variable Data Table changes one input value to see how it affects the formula result.

Where should the formula be placed when creating a Data Table?

A In the bottom-right cell of the Data Table range

B Anywhere in the worksheet

C In the top-left cell of the Data Table range

D Outside the Data Table range

The formula must be in the top-left cell of the Data Table range to link inputs and outputs correctly.

Which Excel feature do you use to create a Data Table for What-If Analysis?

A Pivot Table

B Data Validation

C Conditional Formatting

D What-If Analysis > Data Table

Data Tables are created using the What-If Analysis menu under Data > What-If Analysis > Data Table.

What is the main benefit of using a two-variable Data Table?

A It shows how two inputs affect a formula result together

B It changes one input value

C It formats data automatically

D It creates charts

A two-variable Data Table shows how changing two inputs at the same time affects the formula result.

If you want to see how different interest rates affect loan payments, which tool should you use?

A Sort

B Data Table

C Filter

D Goal Seek

Data Tables let you quickly see how changing interest rates affects loan payments by showing many results at once.

Explain how to set up a one-variable Data Table in Excel step-by-step.

Think about where the formula and inputs go and how Excel knows which input to change.

Describe the difference between one-variable and two-variable Data Tables and when to use each.

Consider how many inputs you want to test at once.

Practice

1. What is the main purpose of a Data Table in Excel's What-If Analysis?

easy

A. To quickly see how changing input values affects the result

B. To create charts from data automatically

C. To sort data in ascending or descending order

D. To protect cells from editing

Solution

1. Step 1: Understand What-If Analysis

   What-If Analysis helps explore different input values to see their effect on results.

2. Step 2: Identify Data Table's role

   Data Tables let you test many input values quickly to observe changes in output.

3. Final Answer:

   To quickly see how changing input values affects the result -> Option A

4. Quick Check:

   Data Tables show output changes for input variations [OK]

Hint: Data Tables show results for many inputs fast [OK]

Common Mistakes:

• Confusing Data Tables with chart creation
• Thinking Data Tables sort data
• Believing Data Tables protect cells

2. Which of the following is the correct way to create a one-variable Data Table in Excel?

easy

A. Apply conditional formatting to the input cells

B. Type multiple formulas manually for each input value

C. Use the Sort feature under Data tab

D. Select the output cell and input values, then use Data > What-If Analysis > Data Table

Solution

1. Step 1: Recall Data Table creation steps

   Data Tables are created via Data > What-If Analysis > Data Table, not by typing formulas manually.

2. Step 2: Identify correct method

   Select the output formula cell and input values, then use the Data Table option to generate results.

3. Final Answer:

   Select the output cell and input values, then use Data > What-If Analysis > Data Table -> Option D

4. Quick Check:

   Data Table creation uses menu, not manual formulas [OK]

Hint: Use Data > What-If Analysis > Data Table to create tables [OK]

Common Mistakes:

• Typing formulas manually for each input
• Using Sort instead of Data Table
• Confusing conditional formatting with Data Tables

3. Given a one-variable Data Table where the formula in cell B2 is =A2*10 and the input values in column A are 1, 2, 3, what will be the output values in the Data Table?

medium

A. [0, 0, 0]

B. [10, 20, 30]

C. [100, 200, 300]

D. [1, 2, 3]

Solution

1. Step 1: Understand the formula and inputs

   The formula multiplies the input value in A2 by 10.

2. Step 2: Calculate outputs for each input

   For inputs 1, 2, 3: outputs are 1*10=10, 2*10=20, 3*10=30.

3. Final Answer:

   [10, 20, 30] -> Option B

4. Quick Check:

   Multiply inputs by 10 to get outputs [OK]

Hint: Multiply each input by 10 to find outputs [OK]

Common Mistakes:

• Confusing inputs with outputs
• Multiplying by 100 instead of 10
• Assuming outputs are zero

4. You created a two-variable Data Table but all output cells show #REF! error. What is the most likely cause?

medium

A. The formula cell reference is missing or incorrect in the Data Table setup

B. You forgot to enter input values

C. The worksheet is protected

D. You used a one-variable Data Table instead of two-variable

Solution

1. Step 1: Understand #REF! error in Data Tables

   #REF! usually means a broken or invalid cell reference in the formula.

2. Step 2: Check Data Table setup

   If the formula cell reference is missing or incorrect, the Data Table cannot calculate results, causing #REF! errors.

3. Final Answer:

   The formula cell reference is missing or incorrect in the Data Table setup -> Option A

4. Quick Check:

   Broken formula references cause #REF! errors [OK]

Hint: Check formula cell reference in Data Table setup [OK]

Common Mistakes:

• Assuming missing inputs cause #REF!
• Blaming worksheet protection without checking references
• Confusing one-variable and two-variable tables

5. You want to analyze how changing both price and quantity affects total revenue using a two-variable Data Table. Price values are in row 2 (cells B2:E2) and quantity values in column A (cells A3:A6). The total revenue formula is =B1*C1 where B1 is price and C1 is quantity. How should you set up the Data Table to get correct results?

hard

A. Place the formula in cell A1, select range A1:E5, then use Data Table with Row input cell = C1 and Column input cell = B1

B. Place the formula in cell B1, select range B1:E6, then use Data Table with Row input cell = C1 and Column input cell = B1

C. Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = B1 and Column input cell = C1

D. Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = C1 and Column input cell = B1

Solution

1. Step 1: Position formula correctly

   Formula must be at the intersection of input values: cell A2 (top-left corner of data table range).

2. Step 2: Select correct range and input cells

   Select A2:E6 to cover all price (row) and quantity (column) values. Set Row input cell = B1 (price) and Column input cell = C1 (quantity) matching formula references.

3. Final Answer:

   Place the formula in cell A2, select range A2:E6, then use Data Table with Row input cell = B1 and Column input cell = C1 -> Option C

4. Quick Check:

   Formula at top-left, correct input cells for row and column [OK]

Hint: Formula top-left; row input = price; column input = quantity [OK]

Common Mistakes:

• Placing formula outside data table range
• Swapping row and column input cells
• Selecting wrong range for Data Table