How to Create One Variable Data Table in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


Overview of Excel Data Table

In this article we are going to analyze one of the most lucrative 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.

Layout for One Way/Variable Data Table in Excel


How to Create One Variable Data Table in Excel: 2 Examples

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

Applying Function to Create 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)

Calculating Monthly Payment with Function to Create One Variable Data Table in Excel

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.

Setting Up One Variable Data Table with Function in Excel

  • Next, select cell F5.
  • After that, insert the following formula.
=C9

Here, we inserted the value of cell C9 which represents the value of the Monthly Payment for a 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 range E5:F16.
  • Then, go to the Data tab >> Click on Forecast >> Click on What-If Analysis >> Select Data Table.

Using What-If Analysis Feature to Create One Variable Data Table with Function in Excel

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

Opening Data Table Box to Create One Variable Data Table in Excel

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

Creating One Variable Data Table with Function in Excel

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

Read More: How to Create One Variable Data Table Using What-If Analysis


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

Using Generic Formula to Create One Variable Data Table in Excel


Step-01: Calculating Revenue

Firstly, we will show you how you can calculate Revenue in Excel. Please 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)

Calculating Revenue to Create One Variable Data Table in Excel

Here, by using the SUM function, we added the Sales values of the 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

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 how to 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.

Setting Up One Variable Data Table with Generic Formula in Excel

  • 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 an 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 range E5:F14.
  • Then, go to the Data tab >> Click on Forecast >> Click on What-If Analysis >> Select Data Table.

Using What-If Analysis Feature to Create One Variable Data Table with Generic Formula in Excel

  • The Data Table box will appear.
  • After that, insert cell C13 as the Column input cell and press OK.

  • Then, you will see that the revenue has 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.

Practice Section


Download Practice Workbook


Conclusion

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 our website for many more articles like this. Thank you!

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

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 a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

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

    • Reply Avatar photo
      Rubayed Razib Suprov Sep 4, 2022 at 1:03 PM

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo