Modifying formula variables suffers from a serious drawback: You see only a single result at one time. If you want to study the effect that a variety of values has on the formula, you need to construct a data table. In the loan payment worksheet, for example, suppose that you want to see the payments with the principal varying between $100,000 and $150,000. You can just enter these values into a row or column, and then create the appropriate formulas. Setting up a data table, however, is much easier, as the following procedure shows:

1. Add to the worksheet the values you want to input into the formula. You have two choices for the placement of these values:
   • If you want to enter the values in a row, start the row one cell up and one cell to the right of the formula.
   • If you want to enter the values in a column start the column one cell down and one cell to the left of the cell containing the formula, as shown in Figure 7.3.
2. Select the range that includes the input values and the formula. (In Figure 7.3, this is B8:C14.)
3. Choose Data, What-If Analysis, Data Table. Excel displays the Data Table dialog box.
4. How you fill in this dialog box depends on how you set up your data table:
   • If you entered the input values in a row, use the Row Input Cell text box to enter the cell address of the input cell.
   • If the input values are in a column, enter the input cell’s address in the Column Input Cell text box. In the future value example, you enter C5 in the Column Input Cell, as shown in Figure 7.4.
     
5. Click OK. Excel places each of the input values in the input cell; Excel then displays the results in the data table, as shown in Figure 7.5.
   

You also can set up data tables that take two input variables. For example, this enables you to see the effect on a loan payment’s value when you enter different values for the principal and the interest rate. The following steps show you how to set up a two-input data table:

1. Enter one set of values in a column below the formula and the second set of values to the right of the formula in the same row, as shown in Figure 7.6.
   
2. Select the range that includes the input values and the formula (B8:G14 in Figure 7.6).
3. Choose Data, What-If Analysis, Data Table to display the Data Table dialog box.
4. In the Row Input Cell text box, enter the cell address of the input cell that corresponds to the row values you entered (C3 in Figure 7.6—the Annual Interest variable).
5. In the Column Input Cell text box, enter the cell address of the input cell you want to use for the column values (C5 in Figure 7.6—the Principal variable).
6. Click OK. Excel runs through the various input combinations and then displays the results in the data table, as shown in Figure 7.7.

If you want to make changes to the data table, you can edit the formula (or formulas) as well as the input value. However, the data table results are a different matter. When you run the Data Table command, Excel enters an array formula in the interior of the data table.

This formula is a TABLE() function (a special function available only by using the Data Table command) with the following syntax:

{=TABLE(row_input_ref, column_input_ref)}

Here, row_input_ref and column_input_ref are the cell references you entered in the Table dialog box. The braces ({ }) indicate that this is an array, which means that you can’t change or delete individual elements of the array. If you want to change the results, you need to select the entire data table and then run the Data Table command again.

If you just want to delete the results, you must first select the entire array and then delete it.

• 6 Tennis Tips and Tricks Tennis tips and tricks are like small tweaks that you can make to your game in order to improve your...
• Anti Aging Makeup Tricks and Tips pt 1 There are all kinds of makeup tricks and tips out there, but choosing the right ones for your own personal...
• Tips for Serving Here are some more useful tennis tips for improving your serving game. If you want to make improvements in your...
• Tips for Glade Skiing Safely Skiers that ski in the northeast may be limited by elevation when it comes to skiing, but that does not...
• Google Fusion Tables Could be a Game Changer Google has been very busy lately with many new products recently. Many of them, such as Google Public DNS, Google...