How to Create a 4-Variable Data Table in Excel (with Easy Steps)

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.

Sample Dataset

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.

Create a data Table with 4 Variables in Excel

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.


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.

Calculate the Payment (PMT) to create 4 variable data table in Excel

  • Eventually, press ENTER and drag down the Fill Handle tool for other cells.

Fill Handle

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.

Insert the Pivot Table to create 4 variable data table in Excel

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

PivotTable from table or range window

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

Completing Pivot table fields for creating 4 variable data table in Excel

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.

Modify the Data Table to make 4 variable data table in Excel

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

Formatting 4 variable data table in excel

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

Format Cells window

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

Practice Section

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


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.

Related Articles

<< Go Back to Data Table in Excel | What-If Analysis in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF