In this article, we are 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â€¦

## Download Practice Workbook

## 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 confuse a **data table **with a **standard table**. 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**.

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

## 2 Examples to Create One Variable Data Table in Excel

Here, we will show **2 **different **examples **of **creating one variable data table** in Excel one with a **function **and another with a **generic formula**.

### Example 1: Applying Function to Create One Variable Data Table in Excel

In the first example, you will find a step-by-step way to **create **a **one variable data table** in Excel with **Function**.

Here, we have a dataset containing the values of **Loan Amount**, **Loan Term, **and **Interest Rate(Months)**. Now, we will show you how to **calculate **the **Monthly Payment **using a **function **and then create a **one variable data table** in Excel.

__Step-01:__ Calculating Monthly Payment

Firstly, we will show you how you can **calculate Monthly Payment **in Excel using **the PMT Function**. **The PMT function** is used to **calculate **the **periodic payment **with aÂ specific **interest rate**. Go through the steps given below to do it on your own dataset.

- In the beginning, select Cell
**C9**. - Then, insert the following formula.

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

Here, in **the PMT function**, we inserted Cell **C8 **as the rate and divided it by **12 (1 year = 12 months) **as the equation is for **monthly payment**. Then, we inserted Cell **C7 **as **nper **and the **negative **value of Cell **C6 **as **pv**.

- After that, press
**ENTER**to get the value of the**Monthly Payment**.

__Step-02:__ Setting Up One Variable Data Table in Excel

Now, we will show you how we set up **one variable data table** for our **dataset **in Excel. The **data table** can be created anywhere in a worksheet.

- Â Firstly, we created the
**Interest Rate**column for the**data table**in Column**E**with interest rates from**5.50%**to**8.00%**. These**interest rates**will**create**our**data table**using the**What-If analysis Feature**. - Then, we added the
**Monthly Payment**column for the**data table**in Column**F**.

- Next, select Cell
**F5**. - After that, insert the following formula.

`=C9`

Here, we inserted the value of Cell **C9 **which represents the **value **of **Monthly Payment **for **5.25% interest rate** in Cell **F5**.

- Now, press
**ENTER**.

__Step-03:__ Using What-If Analysis Feature

In the final step, we will use the **What-If Analysis** feature to **create **a **one-variable data table** in Excel.

- In the beginning, select Cell range
**E5:F16**. - Then, go to the
**Data tab**>> click on**Forecast**>> click on**What-If Analysis**>> select**Data Table**.

- Now, the
**Data Table**box will appear. - After that, insert Cell
**C8**as the**Column input cell**. - Next, press
**OK**.

- Then, you will see that the
**Monthly Payments**have been automatically calculated using the**function**in Cell**C9**with respect to the**interest rate**provided in Column**E**.

- Thus, you can
**create**a**one-variable data table**with a**function**in Excel.

### Example 2: Using Generic Formula to Create One Variable Data Table in Excel

In the second example, you will find a step-by-step way to **create **a **one variable data table** in Excel with a **generic formula**.

Here, we have a dataset containing the **Sales **values of some **Product **and the value of **Efficiency level**. Now, we will show you how to **calculate **the **Revenue **using a **generic formula **and then create a **one variable data table** in Excel.

__Step-01:__ Calculating Revenue

Firstly, we will show you how you can **calculate Revenue **in Excel. Go through the steps given below to do it on your own dataset.

- In the beginning, select Cell
**C12**. - Then, insert the following formula.

`=SUM(C6:C10)`

Here, by usingÂ **the SUM function**, we added the **Sales **values of Cell range **C6:C10**.

- After that, press
**ENTER**to get the value of**Total Sales**.

- Next, select Cell
**C14**. - Then, insert the following formula.

`=C12*C13/100`

Here, in the formula, we **multiplied **the value of Cell **C12 **with the value of Cell **C13**. Then, we **divided **it by **100**.

- Finally, press
**ENTER**to get the value of**Revenue**.

__Step-02:__ Setting Up One Variable Data Table

Next, we will show you how we set up one variable data table for our dataset in Excel.

- Â Firstly, we created the
**Efficiency level**column for the**data table**in Column**E**with an Efficiency level from**100%**to**60%**. These**Efficiency levels**will**create**our**data table**using the**What-If analysis Feature**. - Then, we added the
**Revenue**column for the**data table**in Column**F**.

- Next, select Cell
**F5**. - After that, insert the following formula.

`=C14`

Here, we inserted the value of Cell **C14 **which represents the **value **of **Revenue **for **80% Efficiency level** in Cell **F5**.

- Now, press
**ENTER**.

__Step-03:__ Using What-If Analysis Feature

In the final step, we will use the **What-If Analysis** feature to **create **a **one-variable data table** in Excel.

- In the beginning, select Cell range
**E5:F14**. - Then, go to the
**Data tab**>> click on**Forecast**>> click on**What-If Analysis**>> select**Data Table**.

- Now, the
**Data Table**box will appear. - After that, insert Cell
**C13**as the**Column input cell**. - Next, press
**OK**.

- Then, you will see that the
**Revenues**have been automatically calculated using the**generic formula**in Cell**C14**with respect to the**efficiency levels**provided in Column**E**.

- Thus, you can
**create**a**one-variable data table**with**generic formulas**in Excel.

## Practice Section

In this section, we are giving you the dataset to practice on your own.

## Conclusion

So, in this article, you will find **2 examples** to **create **a **one-variable data table** in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit **ExcelDemy** for many more articles like this. Thank you!

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

Thanks a lot for your compliments, Raul. Glad to hear that the article came in handy for you

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

Hi Gerald,

You can create certain data tables consisting of probability. Provide us with more details, we will be able to help with the detailed procedures.