# How to create a one variable data table in Excel

In this article, I am going to explain how to create a one-variable data table in Excel. Data tables are important parts of the Excel What-If Analysis feature to perform the sensitivity analysis of a business model. So, try to get the concept well.

Let’s start…

## Overview of 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 of 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 a 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 a standard table by choosing this command: Insert ➪ Tables ➪ Table. These two tables are completely different. 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, the 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 that 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.

Read More: How to Find Correlation between Two Variables in Excel

## Create easily a one variable data table in Excel (step by step)

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

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 questions on how to create a One-Variable Data Table in Excel 2013, put it in the comment section.
Happy Excelling 🙂

Sensitivity Analysis in Excel Using One or Two Variables Data Table

One and Two Ways (Variables) Sensitivity Analysis in Excel!

How to Calculate/Find Mean and Standard Deviation in Excel #### Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1. Reply 2. Reply  