Data Tables (What-If) in Excel - Step-by-Step Guide

Introduction

Data Tables in Excel help you see how changing one or two numbers affects your results. This is useful when you want to test different scenarios without changing your formulas manually.

When you want to check how different loan interest rates affect monthly payments.

When you want to see how changing the price and quantity sold affects total revenue.

When you want to analyze how different investment returns impact your savings over time.

When you want to compare outcomes for different sales commission rates.

When you want to quickly test multiple input values to find the best option.

Steps

Step 1: Set up your formula that depends on input values

- Worksheet cells

You have a formula ready that uses one or two input cells for calculation

Step 2: List the input values you want to test

- Cells in a row or column next to your formula

You have a list of different input numbers arranged horizontally or vertically

Step 3: Select the range including the formula cell and the input values

- Worksheet cells covering formula and input values

The range is highlighted for creating the data table

Step 4: Click the Data tab, then click What-If Analysis, and select Data Table

- Excel ribbon on the Data tab

The Data Table dialog box opens

Step 5: Enter the input cell reference for Row input cell or Column input cell depending on your input layout

- Data Table dialog box

Excel knows which cell to change for each input value to recalculate the formula

Step 6: Click OK

- Data Table dialog box

Excel fills the table with results for each input value, showing how the formula output changes

Before vs After

Before

A formula calculates monthly payment based on interest rate in cell B1. Interest rates 3%, 4%, 5% are listed in cells C2, D2, E2.

After

Data Table fills cells C3, D3, E3 with monthly payment results for each interest rate, showing how payment changes.

Settings Reference

Row input cell

📍 Data Table dialog box

Specifies the cell whose value changes across the row input values

Default: None

Column input cell

📍 Data Table dialog box

Specifies the cell whose value changes down the column input values

Default: None

Common Mistakes

Not selecting the entire range including formula and input values before creating the Data Table

Excel needs the full range to fill results correctly; missing cells cause errors or incomplete tables

Always highlight the formula cell plus all input value cells before starting the Data Table

Entering wrong cell reference in Row input cell or Column input cell

If the input cell does not match the formula input, results will be incorrect or unchanged

Make sure to select the exact cell that the formula uses as input for the row or column input

Trying to use Data Tables with formulas that have multiple input cells without setting both row and column input cells

Data Tables can only handle one or two input cells; more inputs require other tools

Use one-variable Data Table for one input cell or two-variable Data Table for two input cells only

Summary

Data Tables let you test how changing inputs affects your formula results quickly.

You set up input values in rows or columns and link them to the formula input cells.

Remember to select the full range and specify correct input cells for accurate results.

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