# How to Create One Variable Data Table in Excel 2013 – [What If Analysis]

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.

## 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.

## 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.

How a one-variable data table should be set up.

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).

One-variable data table

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.

Preparing to create a one-variable data table.

### 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.

The Data Table dialog box.

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)}.

The content of the data table uses an array formula.

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 the Row Input Cell field of the Data Table dialog 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 🙂

## Download Working File

mortgage-loan-data-table.xlsx

Hello!

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

2 Comments
1. Reply
July 25, 2015 at 3:11 PM

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

2. Reply
gerald bettis January 23, 2016 at 3:55 AM

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