How to Create Data Table with 3 Variables (2 Quick Tricks)

In this article, we will show how to create a data table with 3 variables. Users often have to deal with a data table with 2 variables. However, there are many instances when we will have to resort to a 3 variable data table. Excel does not offer any direct method to create a data table with 3 variables. In this article, we will show 2 different ways to do that in Excel.

Download Practice Workbook

You can download the practice workbook here.

2 Ways to Create Data Table with 3 Variables

In this article, we will discuss 2 ways to create a data table with 3 variables. Firstly, we will use the PivotTable to do the task. Then, we will use the Data Table command to create the 3 variables data table.

1. Using PivotTable to Create Data Table with 3 Variables

The PivotTable is a powerful tool to store and manipulate data according to the users’ desire. In this method, we will use this tool to create a data table with 3 variables. Here, we will try to find out the monthly payment for changing principle, terms and interest rate by using the PMT function.

Steps:

• Firstly, write down all the combinations of Principle, Months and Rate possible in the B5:D12 range.

• Then, click on the E5 cell and type the following,

• Then, press Enter.
• As a result, we will get the payment value for the particular principle, month, and rate.

• Now, lower the cursor down to the last cell to autofill.

• After that, select the B5:E12 range.
• Thereafter, go to the Insert tab.
• From the Insert tab, select PivotTable.
• As a result, a prompt will be on the screen.

• From the prompt, select the B5:E12 range as the Range/Table.
• Then, click on the oval beside the New Worksheet oval.
• Finally, click OK.
• As a result, a new sheet will be opened.

• In the new worksheet, first, drag the Principle field under the Columns area.

• Then, drag the Months and Rate fields under the Rows area.

• Finally, drag the PMT field under the Values area.
• As a result, we will have a pivot table.

• Now, click on any of the cells on the pivot table.
• Go to the Design tab.
• Then, select the Report Layout tab.
• From the drop-down list, select Show in Tabular Form.

• After that, again click on any cell of the pivot table.
• Then, go to the Design tab.
• Select the Off for Rows and Columns command under the Grand Totals tab.

• Thereafter, go to the design tab again.
• Then, under Subtotal, select Do Not Show Subtotals.
• As a result, all the subtotals will be removed.

• Finally, we will have a data table with 3 variables.

In this way, we will get a data table with 3 variables by using the PivotTable.

Read More: How to Create One Variable Data Table in Excel (2 Examples)

 2. Using Data Table Command

In this instance, we will use the Data Table command to make a data table with 3 variables. Here, we will first create a regular 2-way data table. Then, we will connect the data table with the 3rd variable. Here, we will calculate the gross revenue for changing unit sold, unit price, and number of sellers using the SUMPRODUCT function.

Steps:

• Firstly, select the C15 cell and write

• Then, hit Enter.
• As a result, we will get the gross revenue for that particular unit sold, unit price and number of sellers.

• Now, click on the D5 cell and type,

• Then, hit Enter.

• After that, select the E5 cell and enter,

• Next, press Enter.

• Finally, move the cursor to the H5 cell to autofill.

• Next, choose the C6 cell and enter the following,

• Then, press Enter.

• After that, choose the C7 cell and type,

• Next, press the Enter button.

• Finally, lower the cursor to the C10 cell to autofill.

• After that, choose the C5 cell and write,

• Hit Enter.

• Next, select the cell range C5:H10.
• Then, go to the Data
• From the Data tab, select the Data Table from the What-If Analysis drop-down options.
• As a result, a prompt will appear on the screen.

• In the prompt, select the C13 range as the input row and C12 as the input column.
• Finally, click OK.

• Then, write down the list of the number of sellers in the G13:G18 range.

• Afterward, select the E13 cell and go to the Data tab.
• Then, from the Data Tools group, select Data Validation.
• Consequently, a prompt will be on the screen.

• From the prompt, first select List under the Allow option.
• Then, select the G13:G18 range as the Source.
• Finally, click OK.

• Thereafter, select the C14 cell and enter,

• Then, press Enter.

• As a result, as you change the value of the E13 cell from the drop-down list, the values within the data table will be changed accordingly.

This is how we will create a data table with 3 variables by using the Data Table command.

Read More: How to Perform the What If Analysis with Data Table in Excel

Conclusion

In this article, we have discussed 2 handy methods to create a data table with 3 variables. These methods will allow users to create tables to analyze complicated calculations easily. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.

Related Articles

• How to Edit Data Table in Excel Chart (2 Simple Ways)
• Format Data Table in Excel Chart (4 Easy Methods)
• How to Edit a Data Table in Excel (With Easy Steps)
• Delete a Data Table in Excel (4 Easy Ways)
• How to Add Data Table in an Excel Chart (4 Quick Methods)
• Data Table Not Working in Excel (7 Issues & Solutions)
• How to Create a Data Table in Excel (7 Ways)