Last updated on January 10th, 2018

*In this article, I have taught how you can create a one-variable data table in Excel 2013. Data tables are important parts of Excel What-If Analysis feature to perform the sensitivity analysis of a business model.*

This article is part of my series: What-If Analysis in Excel – A Step by Step Complete Guide.

Table of Contents

## Overview on Excel Data Table

In this article we are going to analyze one of the most underutilized features in Excel: it is the data table. We can think a data table as a dynamic range of cells. This range of cells summarizes formula cells for varying input cells. Creating a data table is easy. Data tables have some limitations. In particular, a data table can handle only one or two input cells at a time. We shall make clear the limitations of data tables in this article and in our next article with practical examples.

Note

We can produce report that summarizes any number of input cells and result cells using Scenario Manager.

You may be confused data table with a standard tale. We create standard table choosing this command: **Insert ➪ Tables ➪ Table**. These two tables are completely difference. There is no relationship between these two tables.

Read more: Sensitivity Analysis in Excel Using One or Two Variables Data Table

## Creating a One Way/Variable Data Table

In a one-variable data table, we use a single cell as the input in the data table. The values of the input may change and for different values of the input, data table will display different results. The following figure shows the general layout for a one-variable data table. We have to create this layout by ourselves, manually. Excel does not provide anything that will create this layout automatically.

## Creating a One Way/Variable Data Table

In a one-variable data table, we use a single cell as the input in the data table. The values of the input may change and for different values of the input, data table will display different results. The following figure shows the general layout for a one-variable data table. We have to create this layout by ourselves, manually. Excel does not provide anything that will create this layout automatically.

In our following figure, we have shown the complete data table what we shall create at the end of this article. The data table can be created anywhere in a worksheet. The left column contains different values for the single input cell. Our single input cell is C7. The top row contains references to formulas located elsewhere in the worksheet. In our image below, for example, F3= C10, G3=C11. C10 contains a formula: =C4*(1-C5), C11 contains also a formula: =PMT(C7/12,C6,-C10).

This example uses the mortgage loan worksheet that we have used in A What-If Example” article. Our goal of this article is to create a data table that will show the values of the four formula cells (loan amount, monthly payment, total payments, and total interest) for various interest rates ranging from 6.5% to 8.5%, in 0.25% increments.

## Using the following steps create easily a one-variable data table

### Step 1

The following figure shows the setup how the data table will look like. The top row contains references to formulas located elsewhere in the worksheet. For example F3= C10, G3=C11. C10 contains a formula: =C4*(1-C5), C11 contains also a formula: =PMT(C7/12,C6,-C10). Row 2 contains optional descriptive labels, and these are not actually part of the data table. Column E contains the various interest rates. These interest rates will create our data table using What-If analysis tool.

### Step 2

To create the table, you’ve to select the data table range (in our example, E3:I12) and then choose **Data ➪ Data Tools ➪ What-If Analysis ➪ Data Table**. The **Data Table** dialog box will appear.

### Step 3

We must specify the worksheet cell that contains the input value. Because variables for the input cell appear in the left column in the data table, we’ll place this cell reference in the **Column Input Cell** field. Enter C7 or point to the cell in the worksheet. Leave the **Row Input Cell** field blank. Click **OK**, and Excel will fill in the table with the calculated results.

Using this data table, we can now see the calculated loan values for different interest rates. Notice that the **Loan Amt** column (column F) doesn’t vary. That’s because the formula in cell C10 doesn’t depend on the interest rate.

**Read More: One and Two Ways (Variables) Sensitivity Analysis in Excel!**

If you examine the contents of the cells of the data table, you’ll find that the data is generated with a multi-cell array formula: **{=TABLE(,C7)}**.

Note

You can arrange a one-variable table vertically (what we’ve done in this example) or horizontally. If you place the values of the input cell in a row, you have to enter the input cell reference in theRow Input Cellfield of theData Tabledialog box.

## Wrapping Up

If you have any question on how to create a **One-Variable Data Table in Excel 2013**, put it in the comment section.

Happy Excelling 🙂

Thanks for sharing, this is a fantastic blog post.Really looking forward to read more. Great.

HOW ABOUT”PROBABILITY” FOR DOIN RACES (DOGS, HORSES,JAI ALAI) ANY INSIGHT AS HOW TO DO THIS WOULD BE GREATLY APPRECIATED. I AM NO COMPUTER GURU