You can use Excel data tables to see how your formula results change when the data that the formula is based on changes. You can specify a series of hypothetical values for Excel to evaluate the formulas with, and then view the results of the evaluations. For example, you could examine how changes in the number of clients for a business will affect the income or profit. Data tables save you the trouble of entering several values into the worksheet, and recording each recalculation of the worksheet results for later comparison. When you use a data table, Excel will substitute a range of values into the worksheet formulas for you, and tabulate the results so they can be viewed easily.

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 for the firm, on the total profit, total expenses, and total income.

Currently, the firm has 10 clients. The values for wages, total client costs, total expenses, and profit are all calculated by formulas dependent on the number of clients the firm can retain. To see what results the profit formula and other formulas would produce for a range of hypothetical client numbers, we can use a single input data table.

There are some rules you should follow when building your data table to help ensure that it works correctly. First, list the values that you want to input into the formulas in a row or column of adjacent cells. For this example, a column of input values is used. In the row just above your input column, enter 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 the Example spreadsheet that follows, the input variables are in the cell range E5:E30. Cell F4 contains the reference =C5, cell G4 contains the reference =C13, and cell H4 contains the reference =C15, for the total income, total expenses, and total profit formulas respectively.

It is a good idea to label your columns appropriately, so you can clearly understand the data table results. In this example, the same labels that appear in the source data are used for the single input data table.

Notice that the formula references (F4:H4) are in a row just above, and one cell to the right of the first input variable (E5). The data table is now ready. The input variables are listed in the Clients column and the formula references are one row above and one cell to the right. All the elements in the data table are clearly identified.

The next step is to select the range of cells from the data table containing the input variables and the formula references. In this example, the range is E4:H30.

Next, choose Data->Table from the menu bar to display the table dialogue box.

The range of input variables and formula references has been selected, and the Table dialogue box is ready for input.

Because the input variables are arranged in a column, we will use the Column input cell text field, in the table dialogue box rather than the Row input cell field.

In the Column input cell text field, enter C3, which is the cell from original data area that contains the number of clients.

(Remember that the number of clients is also our chosen input variable.)

Clicking the OK button in the Table dialogue box will complete the data table with the formula results.

You can now see at a glance how the 26 different values in the Clients column influence the income, total expenses, and total profit results.

If you change the values in the Client column, the data table will recalculate in accordance with the new values automatically. You can apply formatting styles, borders, shading, and other enhancements to the data table in the same way as you would any other area of your worksheet.

The original data layout and the single input data table with formatting and borders.

If you want to examine the effects of two input variables on a single formula, you can use a two input data table.

To build a two input data table, first choose an empty cell, and 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 for 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 formula cell, and the other range will be a row starting on the immediate right of the formula cell. This means that the formula 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 dependant 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.

Note: Shading, labels, and currency formatting have been added to the data table for clarity.

The next step is to invoke the table dialogue. This time, the formula referenced in E2 will be evaluated once for every combination of # of clients, and Client fees, that exists in the table.

First we select the range of data cells E2:J14, and then we choose Data->Table from the menu bar.

This time, we have two input variables; one corresponding to the row of Client fees, and one corresponding to the column containing the # of clients data.

In the Row input cell text area type C4, because the row of client fees corresponds to cell C4 in the original data.

Similarly, in the column input cell, type C3.

The table dialogue box should look like this.

It is now just a matter of clicking the OK button to complete the table.

Now the data table contains speculative profit values based on the number of clients and the fee per client.