How to Create a Two-Variable Data Table in Excel?

Two-Variable Data Table is a very significant tool for what-if data analysis. With the help of two-variable data tables, we can find all possible trends that can arrive by changing different values. For example, if we know the annual sales of a company, its percentage of expenses and growth. So, by keeping annual sales constant we can find the projected sales of a company by varying expenses and growth. Let's learn how to create a two-variable data table.

Two-Variable Data Table

A data table is a table in which we can find multiple values by adding some fields and formulas. A two-variable data table is a table in which value changes exactly according to two parameters and the rest of the parameters are kept constant.  

Structure of two-variable data table

This could better be understood with an example, given the data set of a line, its x co-ordinate, the slope of a line, and its intercept. Find the different values of y co-ordinate keeping intercept as constant.

Following are the steps to create a two-variable data table:

Step 1: In cell B7, write the formula for finding the y co-ordinate of a line i.e. y = m*x + c. 

Step 2: Copy the same formula in cell D3. 

Step 3: Write X Coordinates row-wise i.e. D4:D8. Write slope values column-wise i.e. E3:H3.

Step 4: Select the range in which you want to make a data table i.e. D3:H8. 

Step 5: Go to the Data tab, and click on What-If analysis. A list appears. Click on the data table.

Step 6: A dialogue box appears. Select the cells used in the formula in cell D3. For the row input cell select cell B4 and column input cell select cell B5. Click Ok. 

Step 7: The two-variable data table is created. All the cells ranging from E4: H8 have different values of y coordinates for different x coordinates and slopes.