Md. Asaduzzaman
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.
