How do you create a data table with two variables in Excel?

In This Article

1. Copy the original formula entered in cell B5 into cell B7 by typing = (equal to) and then clicking cell B5.
2. Select the cell range B7:F17.
3. Choose What-If Analysis→Data Table in the Data Tools group on the Data tab.
4. Click cell B4 to enter the absolute cell address, $B$4, in the Row Input Cell text box.

How do I create a one variable data table in Excel?

To create a one variable data table, execute the following steps.

1. Select cell B12 and type =D10 (refer to the total profit cell).
2. Type the different percentages in column A.
3. Select the range A12:B17.
4. On the Data tab, in the Forecast group, click What-If Analysis.
5. Click Data Table.

What is difference between single & two variable data table?

In one-variable data table, one of the arguments is omitted, depending on the layout (column-oriented or row-oriented). For example, in our horizontal one-variable data table, the formula is =TABLE(, B3) where B3 is the column input cell. In two-variable data table, both arguments are in place.

How do you create a one variable data table in Excel 2013?

Click Data→What-If Analysis→Data Table on the Ribbon. Excel opens the Data Table dialog box. Click the Column Input Cell text box in the Data Table dialog box and then click cell B3, the Growth_2014 cell with the original percentage, in the worksheet.

What is one variable data table Excel?

A one-variable data table contain its input values either in a single column (column-oriented), or across a row (row-oriented). Any formula in a one-variable data table must refer to only one input cell.

Where would you setup the two substitution values in a two variable data table?

Set up the substitution values for two variables: Use the top row for one variable’s substitution values and the first column for the second variable’s substitution values. Add a formula to the data table: Enter the required formula in the top-left corner of the data table.

What is a two-way data table in Excel?

Two-Variable Data Table in Excel allows users to test two variables or values at one time or simultaneously in a data table for created formula. It is only useful when the formula depends on several values that can be used for two variables.

Can you have more than one variable in a data table?

A data table cannot accommodate more than two variables. If you want to analyze more than two variables, you should instead use scenarios. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include as many different variable values as you want.

How do you write a one-variable formula in a table?

Leave a few empty rows and columns on either side of the values. If the data table is column-oriented (your variable values are in a column), type the formula in the cell one row above and one cell to the right of the column of values. This one-variable data table is column-oriented, and the formula is contained in cell D2.

How do I create a two-input table in Excel 2007?

When you create a two-input table, you specify input cells in the Row Input Cell and Column Input Cell boxes in the Table dialog box. In Microsoft Office Excel 2007, the Table dialog box is called the Data Table dialog box. To create a simple two-input table, follow these steps: Create a new workbook.

How do you substitute values in a one variable data table?

Any formula in a one-variable data table must refer to only one input cell. Type the list of values that you want to substitute in the input cell—either down one column or across one row. Leave a few empty rows and columns on either side of the values.