## Data Table is a feature under the ‘What-If Analysis’ tools used for sensitivity analysis. It allows you to see how changing input values of a formula will change the output value.

Excel offers a one-variable and two-variable data table. This means you can choose any two variable values (at max) from any formula to test. For example, if you have a formula in Excel where the output depends on several variables, and you are keen to compare the results for different inputs to the formula, you can use a data table.

Here’s a step-by-step guide on how to create a one-variable data table in Excel.

- Write the formula you want to test.
- List down the variable that is to be changed.
- Create a link by referring to the targeted output for each variable in the corresponding column.
- Select the Inputs table (the variable and the corresponding column for targeted output).
- On the
**Data**Tab select**What-If Analysis**then**Data Table**. This will take you to the Data Table dialog box. - In the
**Column Input****Cell**box, create a reference to the variable from the original table.

The examples below will explain both 1 and 2 input data tables in more detail.

### One-Variable Data Table Example

Let’s say you have invested £10,000 in a savings account and you want to see how the future value of the investment changes with the interest rate. Here’s how you can set up a one-variable data table:

- Click in cell A1, write down the initial investment (£10,000).
- In cell B1, write down the interest rate (for example 5%).
- In cell C1, write the formula for future value: =A1*(1+B1)^10. This formula calculates the future value of the investment after 10 years.
- In column A starting from A3, write down different interest rates that you want to test (for example, 1%, 2%, 3%, …, 10%).
- In cell B2, refer to the cell C1 by typing ‘=C1’.
- Select the range A2:B12 (if you have listed 10 different interest rates)
- From the
**Data**tab select**What-If Analysis**then**Data Table**.

8. In **Column input cell** enter B1 (the cell where you have the original interest rate).

9. Click **OK**.

Excel will now fill the data table with the future value of the investment for each interest rate.

### Result

### Two-Variable Data Table Example

Suppose you have a business and you want to see how the profit changes with both the price of the product and the quantity sold. Here’s how you can set up a two-variable data table:

- In cell B1, type a sample price (for example £50).
- In cell C1, type a sample quantity (for example 100).
- In cell D1, write down the formula for profit. Type ‘=B1*C1’.
- In row 3 starting in B3, write down different prices that you want to test (for example, £40, £45, £50, …, £100).
- In column A starting from A4, write down different quantities that you want to test (for example, 50, 100, 150, …, 500).
- In cell A3, refer to the cell D1 by typing ‘=D1’.
- Select the range A3:N13 (if you have listed 10 different prices and 10 different quantities).
- From the
**Data**tab select**What-If Analysis**then**Data Table**. - In
**Row input cell**enter B1 (the cell where you have the original price) - In under
**Column input cell**enter C1 (the cell where you have the original quantity). - Click
**OK**.

Excel will now fill the data table with the profit for each combination of price and quantity.

### Result

**NOTE**: When using a data table an individual cell cannot be changed. All the cells that have been calculated need to be selected to make the change. The Data Table icon actually creates a calculation in each cell using the TABLE function. The {} denotes that this is an array.

### Further Reading

If you’ve enjoyed reading about what a Data Table is in Excel, there are some other blogs below that you might find useful:

**Want to learn more about Microsoft Excel?** Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.