Microsoft Office Tutorials and References

In Depth Information

**To Create a Percent Series Using the Fill Handle**

To Enter the Formulas in the Data Table

Formulas in Data

Tables

Any experienced Excel

user will tell you that to

enter the formulas at the

top of the data table,

you should enter the cell

reference or name of

the cell preceded by an

equal sign (Figure 4–24).

This ensures that if you

change the original

formula in the worksheet,

Excel automatically

will change the

corresponding formula in

the data table. If you use

a cell reference, Excel also

copies the format to the

cell. If you use a name,

Excel does not copy the

format to the cell.

The next step in creating the data table is to enter the three formulas at the top of

the table in cells C9, D9, and E9. The three formulas are the same as the monthly payment

formula in cell E4, the total interest formula in cell E5, and the total cost formula in cell

E6. The number of formulas you place at the top of a one–input data table depends on the

application. Some one–input data tables will have only one formula, while others might have

several. In this case, three formulas are affected when the interest rate changes.

Excel provides four ways to enter these formulas in the data table: (1) retype the

formulas in cells C9, D9, and E9; (2) copy cells E4, E5, and E6 to cells C9, D9, and E9,

respectively; (3) enter the formulas =monthly_payment in cell C9, =total_interest in cell

D9, and =total_cost in cell E9; or (4) enter the formulas =e4 in cell C9, =e5 in cell D9, and

=e6 in cell E9.

The best alternative to deﬁ ne the formulas in the data table is the fourth one, which

involves using the cell references preceded by an equal sign. This is the best method

because: (1) it is easier to enter the cell references; (2) if you change any of the formulas

in the range E4:E6, the formulas at the top of the data table are updated automatically;

and (3) Excel automatically assigns the format of the cell reference (Currency style format)

to the cell. Using the names of the cells in formulas is nearly as good an alternative, but if

you use cell names, Excel will not assign the format to the cells. The following steps enter

the formulas of the data table in row 9.

1
With cell C9 active, type
=e4
and then press the
RIGHT
ARROW
key.

2
Type
=e5
in cell D9 and then press the
RIGHT
ARROW
key.

3
Type
=e6
in cell E9 and then click the Enter box to complete the assignment of the

formulas and Currency style format in the range C9:E9 (Figure 4–24).

Why are these cells assigned the values of cells in the Loan Payment Calculator area of the

worksheet?

It is important to understand that the entries in the top row of the data table (row 9) refer

to the formulas that the loan department wants to evaluate using the series of percentages

in column B. Furthermore, recall that when you assign a formula to a cell, Excel applies the

format of the ﬁ rst cell reference in the formula to the cell. Thus, Excel applies the Currency

style format to cells C9, D9, and E9 because that is the format of cells E4, E5, and E6.

formulas are

placed in

top row of

data table

varying interest rates

are input values to be

substituted in cell E2