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

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.

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.

How to create a one-variable data table in Excel 2013

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

How to create a one-variable data table in Excel 2013

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.

How to create a one-variable data table in Excel 2013

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.

How to create a one-variable data table in Excel 2013

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

How to create a one-variable data table in Excel 2013

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

Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube
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!

You may also like...

2 Responses

  1. Portillo@yahoo.com' Raul Duman says:

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

  2. check9255@yahoo.com' gerald bettis says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.