How To Create A Data Table In Excel 2013

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…

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

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 to create a one variable data table in Excel

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

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.

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.

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

Download Working File