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

Get FREE Advanced Excel Exercises with Solutions!

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.

writing combinations of parameters to create a data table with 3 variables

  • Then, click on the E5 cell and type the following,
=PMT(D5,C5,B5)
  • Then, press Enter.
  • As a result, we will get the payment value for the particular principle, month, and rate.

writing PMT formula to create a data table with 3 variables

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

inserting pivot table to create a data table with 3 variables

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

opening new worksheet with pivot table to create a data table with 3 variables

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

adding field to columns area to create a data table with 3 variables

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

adding fields to rows area to create a data table with 3 variables

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

adding field to values area to create a data table with 3 variables

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

showing pivottable in tabular form to create a data table with 3 variables

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

removing subtotals to create a data table with 3 variables

  • 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
=SUMPRODUCT(C12,C13,C14)
  • Then, hit Enter.
  • As a result, we will get the gross revenue for that particular unit sold, unit price and number of sellers.

inserting sumproduct function to create a data table with 3 variables

  • Now, click on the D5 cell and type,
=C12
  • Then, hit Enter.

  • After that, select the E5 cell and enter,
=D5+5
  • Next, press Enter.

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

  • Next, choose the C6 cell and enter the following,
=C13
  • Then, press Enter.

  • After that, choose the C7 cell and type,
=C6+0.5
  • Next, press the Enter button.

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

  • After that, choose the C5 cell and write,
=C15
  • 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.

using data table command to create a data table with 3 variables

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

inserting data validation command to create a data table with 3 variables

  • 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,
=E13
  • 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

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo