How to Make a Two Variable Data Table in Excel
A two-variable data table is your window into understanding the dynamic relationship between two different set of inputs and their collaborative effect on a formula’s output. It’s like watching a carefully choreographed performance, where each change in these inputs pairs results in a unique transformation of the final result.
Thes steps to create a two-variable data table in Excel are basically the same as in the above example, except that you enter two ranges of possible input values, one in a row and another in a column.
Step 1: Top Left Cell Contains Formulas
The top left cell is the cell that contains the formula for simple interest and it is linked with the cell containing the value of the principal amount. The formula is =B3*B4*B5.
Step 2: Select Data Table and Fill Details Followed by Pressing OK
To use the data table tool, we need to select the table with all the inputs and the cell containing the formula. Go to what-if analysis, and select Data Table. A Data-Table dialogue box appears. Add, Column Input cell value as $B$5, and Row input cell value as $B$4. Click Ok.
Step 3: Interchange Values
We can interchange the values in the rows and columns but accordingly we have to change the row and column input values in the data table. Below, are shown the values of interest for the various number of years.
Data Table In Excel : One Variable and Two Variable (In Easy Steps )
Data Table is a tool present in Microsoft Excel as one of the three What-If Analysis tools namely Scenario Manager, Goal Seek, and Data Table. It is a tool, that allows one to try out various input values for the formulas present in their sheet and see how changes in those values affect the output in the cells. To apply a data table tool anywhere in the Excel sheet, there must be a table and the formula whose values will be replaced by the row and column values must be kept in the top-left corner or the top-right corner of the table according to the variables used to replace the formula values. In this article, we will learn about data tables in Excel.