How to Create One Variable Data Table Using What If Analysis

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.

Create Layout of Data Table to Create One Variable Data Table Using What If Analysis

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

Using Formula to Create One Variable Data Table Using What If Analysis

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

Using PMT Function to Create One Variable Data Table Using What If Analysis

  • Furthermore, select cell C13 and type the following formula:
=C12*C7
  • Next, press Enter and it will calculate the Total Payments.

Using Arithmatic Formula to Create One Variable Data Table Using What If Analysis

  • Moreover, select cell C14 and type the following formula:
=C13-C11
  • Hence, press Enter and it will calculate the Total Interest.

Using Arithmatic Formula to Create One Variable Data Table Using What If Analysis

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

Creating Layout of Data Table to Create One Variable Data Table Using What If Analysis

  • To reference values in cells (G5:J5), select cell G5 and type the following formula.
=C11
  • Now, press Enter.

Referencing Value to Create One Variable Data Table Using What If Analysis

  • After that, select cell H5 and type the following formula.
=C12
  • Next, press Enter.

Referencing Value to Create One Variable Data Table Using What If Analysis

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

Applying What If Analysis Feature to Create One Variable Data Table Using What If Analysis

Read More: What-If Analysis in Excel with Example


Similar Readings


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.

Specifying Worksheet Cell that Contains input Value to Create One Variable Data Table Using What If Analysis

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.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo