Part II: Working with Formulas and Functions
340
- Click OK. Excel inserts an array formula that uses the TABLE function with a single
argument. - (Optional) Format the data table. For example, you may want to apply shading to the
row and column headers.
Note that the array formula is not entered into the entire range that you selected in Step 4. The first
column and first row of your selection are not changed.Tip
When you create a data table, the leftmost column of the data table (the column that contains the references
entered in Step 3) contains the calculated values for the input cell. In this example, those values are repeated
in column D. You may want to hide the values in column B by making the font color the same color as the
background. n
Creating a two-way data table ........................................................................
A two-way data table shows the results of a single calculation for different values of two input cells.
Figure 15.8 shows a two-way data table (in B10:I16) that displays a calculation (payment amount)
for a loan, using seven interest rates and six loan amounts.To create this two-way data table, follow these steps:- Enter a formula that returns the results that will be used in the data table. In this
example, the formula is in cell B6. The formulas in B7:B8 are not used. - Enter various values for the first input in successive columns. In this example, the
first input value is interest rate, and the values for various interest rates appear in
C10:I10.
FIGURE 15.8
Using a two-way data table to display payment amounts for various loan amounts and
interest rates.