Using a One or Two Input Data Table
In Excel, you can create a single input data table or a two input data table. A single input table will substitute a range of values as a single variable in as many formulas as you like. With a two input data table, you can specify ranges for two input variables, but these input variables can only be applied to one formula.
The following example involves a hypothetical consulting firm. Our first goal is to examine the effect of the number of clients on the total profit, expenses, and income.
Currently, the firm has 10 clients. The values for Wages, Total Costs, Total Expenses, and Profit are all calculated by formulas dependent on the number of clients the firm can retain. For example, you might like to see what the profit formula and other formulas in the worksheet would produce for a varying number of clients. To manage this, we can use a single input data table.
Keep in mind that there are some strict rules you should follow when building your data table to help ensure that it works correctly.
- First, list the input values that you want to try in a row or column of adjacent cells. For this example, we’ll use a column of input values. The column will be named Hypothetical Clients and will hold different values for our variable (the number of clients).
- In the row just above your input data (row 4 in this example), enter the cell references to the formulas that you want to evaluate. Make sure you enter the references starting one cell to the right of the column of input values.
- In this case, we want references to cells C5, C13, and C15 placed in the row just above our input values (row 4) and starting one cell over to the right from the input column (in column F).
- The resulting worksheet looks like the following :
- The input variables are in the cell range E5: E19. Cell F4 contains the reference =C5, cell G4 contains the reference =C13, and cell H4 contains the reference =C15. These references point to the Total Income, Total Expenses, and Profit formulas respectively.
- It is a good idea to label your new columns appropriately, so you can clearly understand the data table results. In the preceding example, the new columns are labeled Total Income, Total Expenses, and Total Profit. Now, let’s have a closer look at the structure.
- Notice that the formula references (in cells F4:H4) are in a row just above, and one cell to the right of the first input variable (in E5). The data table is now ready. The input variables are listed in the “Hypothetical Clients” column and the formula references are one row above and one cell to the right with respect to the first input value. Also, all of the elements in the data table are clearly identified.
- The next step is to select the full range of cells from the data table formed by the input variables and the formula references. In this example, the range is E4:H19:
- After you have the correct cells selected, choose Data → What-If Analysis → Data Table:
- This action will display the Data Table dialog box:
Looking back to the section of the worksheet that contains the formulas and original data, we see that the number of clients is stored in cell C3.
- The next step is to enter this cell reference into the Data Table dialog. Because the values that we are substituting for the number of clients are arranged in a column, the cell reference C3 (the original location of the number of clients) should be entered into the “Column input cell” box in the dialog.
- Then, click OK in the dialog to see your values:
If you change the values in the Hypothetical Clients column, the data table will automatically recalculate in accordance with the new values. You can also apply formatting styles, borders, shading, and other enhancements to the data table in the same way that you would any other area of your worksheet:
Now, what if you want to consider the effects of two input variables on a single formula? For example, what would be the effect of both the number of clients and the fee per client on profit? To find out, you can use a two input data table.
- To start, choose an empty cell, and then enter a reference to the formula you want to examine. Using the same consulting firm example as before, we will choose cell E2 and enter the reference =C15 to refer to the cell containing the profit formula:
- The next step is to create two variable ranges. One range will be a column starting in the cell immediately beneath the cell that contains the formula reference. The other range will be a row starting on the immediate right of the cell that contains the formula reference. This means that the formula reference cell (E2) will be at the upper left corner of the two input data table. For the column of input variables, we will again use the number of clients. For the row of input variables, we will use the fee per client. (The formula for the Profit values is indirectly dependent on both of these variables.)
- After entering some hypothetical values for the number of clients in cells E3: E15 and some different client fee values in cells F2: J2, we will end up with a worksheet that looks something like the following:
(In the preceding picture, shading and labels have been added to the data table for organization and clarity.)
- Once the table is set up, the next step is to select the range of cells covered by the new data table and invoke the Data Table dialog (Data → What-If Analysis → Data Table):
This time, we have two input variables into the Data Table dialog; one corresponding to the row of client fees, and one corresponding to the Number of Clients column:
After the correct cell references are entered, it is simply a matter of clicking OK in the dialog box to complete the table. This time, the formula referenced in E2 will be evaluated once for every combination of Clients and Client Fees that exists in the table:
