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

In Excel 2016 a two-variable data table substitutes a series of possible values for two input values in a single formula. To create a two-variable data table, you enter two ranges of possible input values for a formula in the Data Table dialog box.

Enter a range of values for the Row Input Cell across the first row of the table and a range of values for the Column Input Cell down the first column of the table. You then can enter the formula (or a copy of it) in the cell located at the intersection of this row and column of input values.

The following figure illustrates this type of situation. This version of the projected sales spreadsheet uses two variables to calculate the projected sales for year 2017: a growth rate as a percentage of increase over last year's sales (in cell B3 named Growth_2017) and expenses calculated as a percentage of last year's sales (in cell B4 named Expenses_2017). In this example, the original formula created in cell B5 is a bit more complex:

Sales projection spreadsheet with a series of possible growth and expense percentages to plug in to a two-variable data table.

=Sales_2016+(Sales_2016*Growth_2017) - (Sales_2016*Expenses_2017)

To set up the two-variable data table, add a row of possible Expenses_2017 percentages from 10% to 35% in the range C7:H7 to a column of possible Growth_2017 percentages from 1.00% to 5.00% in the range B8:B24. Then copy the original formula named Projected_Sales_2017 from cell B5 to cell B7, the cell at the intersection of this row of Expenses_2017 percentages and column of Growth_2017 percentages with the formula:

=Projected_Sales_2017

With these few steps, create the two-variable data table you see in the next figure:

1. Select the cell range B7:H24.

   This cell range incorporates the copy of the original formula along with the row of possible expenses and growth-rate percentages.

2. Click Data→What-If Analysis→Data Table on the Ribbon.

   Excel opens the Data Table dialog box with the insertion point in the Row Input Cell text box.

3. Click cell B4 to enter the absolute cell address, $B$4, in the Row Input Cell text box.

4. Click the Column Input Cell text box and then click cell B3 to enter the absolute cell address, $B$3, in this text box.

5. Click OK to close the Data Table dialog box.

   Excel fills the blank cells of the data table with a TABLE formula using B4 as the Row Input Cell and B3 as the Column Input Cell.

6. Click cell B7, then click the Format Painter command button in the Clipboard group on the Home tab and drag through the cell range C8:H24 to copy the Accounting number format with no decimal places to this range.

   This Accounting number format is too long to display given the current width of columns C through F — indicated by the ###### symbols. With the range C8:H24 still selected from using the Format Painter, Step 7 fixes this problem.

7. Click the Format command button in the Cells group of the Home tab and then click AutoFit Column Width on its drop-down menu.

   

   Sales projection spreadsheet after creating the two-variable data table in the range C8:H24.

The array formula {=TABLE(B4,B3)} that Excel creates for the two-variable data table in this example specifies both a Row Input Cell argument (B4) and a Column Input Cell argument (B3). Because this single array formula is entered into the entire data table range of C8:H24, any editing (in terms of moving or deleting) is restricted to this range.