Fahim Shahriyar Dipto
A data table in Excel enables you to experiment with various formula input values and observe just how these changes impact the formula’s output. Whenever you work with a financial statement and want to create a relationship with it, you must first create a data table in Excel. There are several variables used in the data table. Among them, the 2-variable data table is the most familiar. But you may need to create a data table with 3/4 or 5 variables. In this article, we’re going to show you the simple steps to create a 4-variable data table in Excel. Let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
4 Steps to Create a 4-Variable Data Table in Excel
Four variables mean the dataset needs to have 4 independent variables, and from those, we will calculate the final output, which is a dependent variable. For doing this, we have taken a dataset of “Bank Statement” where we enter the Principle, the Interest Rate, the Month, and the Future Value (FV). With this sort of data, we will create a 4-variable data table and find the Payment dealing with the above four variables.
Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.
Step 1: Create Outline of Table
- Firstly, you have to create a dataset. You can see in the below image; we took a column of PMT (Payment). In that column, we calculate payment based on the below data.
Read More: How to Create Data Table with 3 Variables (2 Quick Tricks)
Step 2: Calculate the Payment Using PMT Function
- In the next step, we calculate the payment using the PMT function. It is a financial function with whom we can get the periodic payment for a loan.
- Initially, you need to move to cell F5 and insert the following formula.
=PMT(C5,D5,B5,E5)Here,
C5 is the Interest rate, D5 is the Month, B5 represents the Principle (Present Value), and E5 is the Future Value (FV). The PMT function returns the payment for the inserted data.
- Eventually, press ENTER and drag down the Fill Handle tool for other cells.
Finally, you get all the output for the remaining cells.
Read More: How to Create a Two Variable Data Table in Excel (3 Examples)
Similar Readings
- Change Chart Data Range in Excel (5 Quick Methods)
- How to Add Data Table in an Excel Chart (4 Quick Methods)
- Data Table Not Working in Excel (7 Issues & Solutions)
- Scenario Analysis in Excel: A Guide with 2 Sample Cases + Template
- How to Perform the What If Analysis with Data Table in Excel
Step 3: Insert the Pivot Table
- At this moment, we insert a pivot table with our data for creating a 4-variable data table.
- To do this, select the entire range B5:F16 and navigate to the Insert tab >> choose PivotTable.
- The PivotTable from table or range appears. The Table/Range is auto-selected. Choose the New worksheet in the Choose where you want the PivotTable to be placed. Hit OK.
- Consequently, put the Principle in the Columns group. The Interest Rate, Month, and Future Value (FV) in the Rows section, and the PMT in the ∑ Values field.
Read More: How to Make a Data Table in Excel (Easiest 5 Methods)
Step 4: Modify the Data Table
- This time, we will modify our data table.
- Firstly, hover over the Design tab >> choose Report Layout in the Layouts group >>pick Show in Tabular Form.
- Apparently, again move to the Design tab >> under the Layouts section choose Grand Totals >> Off for Rows and Columns.
- Sequentially, move the Design tab again and select the Subtotals >> pick Do Not Show Subtotals.
Finally, the data table is almost ready.
Moreover, select the output of E, F, and G columns and right-click on it to choose the Format Cells.
Subsequently, the Format Cells window appears. Make the Category as Currency. Choose suitable Negative numbers. And input the Decimal places as 0. Lastly, hit OK.
Finally, you have created a 4-variable data table like ours (see the image below).
Read More: How to Create One Variable Data Table in Excel (2 Examples)
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.
Conclusion
That’s all about today’s session. And these are some easy steps to create a 4-variable data table in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to learn about diverse Excel methods. Thanks for your patience in reading this article.
