How to Create a Two-Variable Data Table in Excel?
Last Updated :
07 Mar, 2022
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.
Similar Reads
How to Create a Power PivotTable in Excel? When we have to compare the data (such as name/product/items, etc.) between any of the columns in excel then we can easily do with the help of Pivot table and pivot charts. But it fails when it comes to comparing those data which are in two different datasets, at that time Power Pivot comes into rol
4 min read
How to Plot Bivariate Data in Excel? Bivariate data is the most used type of data representation for the plotting of scatter plots . The data depends on two variables as its name suggests, and it is analyzed using different machine-learning algorithms, using different charts, etc. Bivariate can be performed using different methods. Amo
8 min read
How to Set Variable to Cell Value in Excel VBA? Adding data to the worksheet is often done and it is essential to learn how to set variable to cell value in excel VBA. There can be instances when you want to add the same data to your worksheet. This task could easily be achieved with the help of Excel VBA variables. You can assign your cell value
5 min read
How to Create a Dashboard in Excel Creating a dashboard in Excel transforms raw data into actionable insights, making it easier to track performance and make informed decisions. The dashboard helps users to understand and analyze the complex data in an easy way. Hence, nowadays most of the business professionals and analysts want to
6 min read
How to Create a Contingency Table in Excel A contingency table, also known as a crosstab is used to show the relationship between two categorical variables. In Excel, we can make a contingency table using the pivot table function. They are best for summarizing the relationship between categorical variables. A contingency table is just like a
4 min read
How to Create a Contingency Table in Excel A contingency table, also known as a crosstab is used to show the relationship between two categorical variables. In Excel, we can make a contingency table using the pivot table function. They are best for summarizing the relationship between categorical variables. A contingency table is just like a
4 min read