Data tables are important parts of the **Excel What-If Analysis** feature to perform the sensitivity analysis of a business model. It is one of the most underutilized features in **Excel**. Are you having trouble creating a data table in Excel? In this article, I am going to explain **how to create one variable data table using What If Analysis**. Let’s get started!

## Download Practice Workbook

You can download the** Excel workbook** from here.

## What Is a Data Table in Excel?

We can think of a data table as a dynamic range of cells that summarizes formula cells for varying input cells. Creating a data table is easy. Data tables have some limitations. In particular, a data table can deal with only** one** or **two** input cells at a time. We shall make clear the limitations of data tables in this article. You may be confused about a data table with a standard tale. We can 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.

## Step-by-Step Procedures to Create One Variable Data Table Using What If Analysis

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. We have to create a layout by ourselves, manually. **Excel** does not provide anything that will create the layout automatically. After that, we will apply the **What If Analysis** feature and specify a worksheet cell that contains the input value to complete our data table.

### Step 1: Create Layout of Data Table

In this example, we have used the **mortgage loan worksheet** that we have used in “**A What-If Analysis Example**” article. Our goal of this article is to create a data table so that it will display the values of the four formula cells (**loan amount**, **monthly payment**, **total payments**, and **total interest**) for different interest rates ranging from **6.5%** to **8.5%**, in **0.25%** increments. In order to do so, follow the steps below.

- First, we need to calculate the
**Result Cells**by using simple**Excel**arithmetic formulas and functions. But these formulas are not part of our**One variable Data Table**.

- To calculate the result cells, select cell
**C11**and type the following formula:

`=C5*(1-C6)`

- Then, press
**Enter**and it will calculate the**Loan Amount**.

- After that, select cell
**C12**and type the following formula:

`=PMT(C8/12,C7,-C11)`

- Now, press
**Enter**and it will calculate the**Monthly Payment**.

- Furthermore, select cell
**C13**and type the following formula:

`=C12*C7`

- Next, press
**Enter**and it will calculate the**Total Payments**.

- Moreover, select cell
**C14**and type the following formula:

`=C13-C11`

- Hence, press
**Enter**and it will calculate the**Total Interest**.

- Second, we need to create a layout like the following image where the values of the single input cell will be the
**Interest rate**in cells (**F6:F14**). The**F5**cell will be empty, (**G5:J5**) cells will be the reference cells, and (**G6:J14**) cells will be used for the results of the one variable data table given by**Excel**. - Now, type the interest rates starting from
**50%**in increments of**.25%**in the cells (**F6:F14**).

- To reference values in cells (
**G5:J5**), select cell**G5**and type the following formula.

`=C11`

- Now, press
**Enter**.

- After that, select cell
**H5**and type the following formula.

`=C12`

- Next, press
**Enter**.

- Furthermore, select cell
**I5**and type the following formula.

`=C13`

- Then, press
**Enter**.

- Lastly, select cell
**J5**and type the following formula.

`=C14`

- Then, press
**Enter**.

- Finally, our layout is ready to create the
**One Variable Data Table**using the**What If Analysis**.

**Read More:** **How to Perform the What If Analysis with Data Table in Excel**

### Step 2: Apply What If Analysis Feature

Our next task is to apply the **What If Analysis** feature to our layout.

- Firstly, select cells (
**F5:J14**) >> go to the**Data**tab >> select the**What If Analysis**feature >> choose**Data Table**from the options.

**Read More: What-If Analysis in Excel with Example**

**Similar Readings**

**How to Perform What If Analysis in Excel (3 Examples)****Delete What If Analysis in Excel (2 Simple Methods)****How to Do What-If Analysis Using Scenario Manager in Excel**

### Step 3: Specify Worksheet Cell That Contains Input Value

Our final task is to specify the worksheet cell that contains the input value.

- After selecting the
**Data Table**from the options, a**Data Table**dialog box will open. Select cell**$C$8**in the**Column input cell**and click**OK**like the below one.**Row input cell**will be blank.

**Read More: ****How to Use What If Analysis in Excel (with All 3 Features)**

### Final Output

Finally, we will see an output like the following figure where **Excel **will give the **One Variable Data Table** for our example.

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

## Things to Remember

- You can create a one-variable table vertically (what we’ve done in this example) or horizontally. After placing the values of the input cell in a row, we have to enter the input cell reference in the
**Row Input Cell**field of the**Data Table**dialog box. - Creating the layout manually is the most important part of
**One Variable Data Table**. If you make a mistake in this step, you will find errors in your result.

## Conclusion

Hence, follow the above-described steps. Thus, you can easily learn **how to create one variable data table using What If Analysis**. Hope this will be helpful. Follow the **ExcelDemy** website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.