What is a Data Table in Excel?

May 9, 2024 | Excel Hints and Tips

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.

  1. Write the formula you want to test.
  2. List down the variable that is to be changed.
  3. Create a link by referring to the targeted output for each variable in the corresponding column.
  4. Select the Inputs table (the variable and the corresponding column for targeted output).
  5. On the Data Tab select What-If Analysis then Data Table. This will take you to the Data Table dialog box.
  6. 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:

  1. Click in cell A1, write down the initial investment (£10,000).
  2. In cell B1, write down the interest rate (for example 5%).
  3. 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.
  4. In column A starting from A3, write down different interest rates that you want to test (for example, 1%, 2%, 3%, …, 10%).
  5. In cell B2, refer to the cell C1 by typing ‘=C1’.
  6. Select the range A2:B12 (if you have listed 10 different interest rates)
  7. From the Data tab select What-If Analysis then Data Table.
What if analysis icon image

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

Data table dialog box image

9. Click OK.

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

 

Result

1 input data table image

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:

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

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

 

Result

2 input data table image

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.