A data table in Excel enables you to experiment with various formula input values and observe 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.
How to Create a 4-Variable Data Table in Excel: 4 Easy Steps
Four variables mean the dataset needs to have 4 independent variables. From those, we will calculate the final output, which is a dependent variable. To do this, we have taken a dataset of Bank statements, where we enter the Principle, Interest Rate, Month, and 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 Office 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 the below image. We took a column of PMT (Payment). In that column, we calculate payment based on the below data.
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.
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 window appears.
- The Table/Range is auto-selected. Choose the New worksheet in the Choose where you want the PivotTable to be placed. Hit OK.
- Then, put the Principle in the Columns group, Interest Rate, Month, and Future Value (FV) in the Rows section, and the PMT in the ∑ Values field.
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.
- Move to 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 columns E, F, and GÂ and right-click on it to choose the Format Cells.
- The Format Cells window appears.
- Set 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).
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Conclusion
That’s all about today’s session. 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, a one-stop Excel solution provider, to learn about diverse Excel methods. Thanks for your patience in reading this article.
