One Variable Data Table in Excel

What is One-Variable Data Table in Excel?

One variable Data Table in excel means by changing the one variable along with multiple options and get the results for multiple scenarios.

How to Create a One-Variable Data Table in Excel? (with Examples)

You can download this One-Variable Data Table Excel Template here – One-Variable Data Table Excel Template

Example #1

You are taking a loan of Rs. 2, 50,000 for 2 years. You are in discussion with the loan officer concerning the interest rates.

You need to analyze at different interest rates what is the monthly payment you need to pay to clear the loan. For the calculation purpose, take the base rate of 12% per anum.

Loan Amount250,000
Payment Years2
Month Per Year12
Interest Rate12%
Monthly EMI?

Below are the steps for calculation of monthly EMI using PMT function –

  1. Calculate the monthly EMI using the PMT functionEMI Using The PMT FunctionPMT function is an advanced financial function to calculate the monthly payment against the simple loan amount. You have to provide basic information, including loan amount, interest rate, and duration of payment, and the function will calculate the payment as a more.

    one variable data in excel step 1

  2. Now create a scenario table as shown in the below image.

    one variable data in excel step 2

  3. Select the cell E9 and give a link to the cell B6 (EMI Amount). Now the cell E9 showing the monthly EMI.

    one variable data in excel step 3

  4. Select the range from D9 to E22.

    one variable data in excel step 4

  5. Click on Data Tab, then What-if-Analysis and Data Table.

    What If Analysis - Data Table

  6. Data Table dialogue box will come up. In the Column Input Cell, select the cell B5 (which contains basic loan interest rate).

    One Variable Data Table in Excel, we always ignore either ROW input cell or Column input cell. It depends on our table structure. If our scenario table different interest rates are vertical, then we ignore the row input cell, and if our scenario table interest rates are horizontal, then we ignore the column input cell. In this example, I have ignored the Row input cell because the scenario table different interest rates are in a vertical way.

    Data table - Column input cell

  7. Now click on the OK button to create different scenarios.

    monthly EMI

    Now the range E10:E22 showing some new values. From the table, it is very clear that @12.5% interest rate monthly EMI will be 11,827 INR, and @13.5% interest rate monthly EMI would be 11,944 INR and so on.

    This is how works one variable data table in excel. You can show this in a chart as well.

    Monthly EMI @ different interest rates

Example #2

Assume you are a sales manager of a company. From the management, you have received a monthly sales target of 1, 70, 00 USD from your team. The below table shows the sales target of 6 members. You need to analyze what should be their efficiency level to hit the target of 1.7 lakh USD in a month.

one variable data in excel example 2

Your teams’ overall target is 2.04 lakhs. You are not sure at what percentage of efficiency they need to bring in to the table to achieve the target given by the management.

Your team can give a maximum of 90% efficiency level, and you have calculated the total revenue at a 90% efficiency level.

one variable data in excel example 2-1

At a 90% efficiency level, your team can achieve total revenue of 1.83 lakh USD in a month. You need to what should be the efficiency level to achieve the revenue target given by the management.

Create a scenario table, as shown in the below image.

  • Step 1: Below the table, you need to create an excel file.
data table

This table shows at different efficiency levels what will be revenue?

  • Step 2: Select the cell H3 and give a link to the cell B11 (at 90% efficiency level revenue cell). Now the cell H3 cell showing the 90% efficiency level revenue.
One Variable Data Table in Excel step 2
  • Step 3: Select the range from G3 to H12.
select range
  • Step 4: Now find the Data Table under the What if Analysis section.
what if analysis
  • Step 5: Once you click on Data Table, we need to give a link in the Column Input Cell, select the cell B10 (that contains efficiency percentage).

For the Column input cell, I have given the link to the cell B10 because, based on the different efficiency levels, we are going to create the scenarios. Now, the data table understands at 90%; revenue will be 1.83 lakh USD. Similarly, it will create scenarios for 100%, 95%, 90%, 85%, 80% and so on.

column input cell
  • Step 6: Click on OK to create different scenarios.
different scenarios - Click OK

Now the range G3:H12 showing scenarios. Management has given the target of 1.70 lakh USD for this month. In order to achieve that much revenue, your team needs to perform at an efficiency level of at least 85%.

In this way, you can use Data Table to create different analysis and chose a suitable scenario to achieve the goals.

Things to Remember

Recommended Articles

This has been a step by step guide to One-Variable Data Table Excel. Here we discuss how to create a One-Variable Data Table in Excel using examples and downloadable excel templates. You may also look at these useful excel tools –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion