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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF