Microsoft Office Tutorials and References
In Depth Information
To Copy the Formulas to Fill the Amortization Schedule
The Magical Fill Handle
If there is a column with
entries adjacent to the
range you plan to drag
the fi ll handle down
through, then you can
double-click the fi ll handle
instead of dragging. For
example, in Step 3 on
page EX 302, you could
have double-clicked the
fi ll handle instead of
dragging the fi ll handle
down through column 20
to copy the formula in
cell H4 to the range
H5:H20, because of the
numbers in column G.
This feature also applies
to copying a range using
the fi ll handle.
1 Select cell H3. Click the Format Painter button on the Home tab on the Ribbon. Drag
through the range I3:K3 to assign the Currency style format to the cells.
2 Select the range H4:K20 and then right–click. Click Format Cells on the shortcut menu.
When Excel displays the Format Cells dialog box, click the Number tab. Click Currency in
the Category list, click the Symbol box arrow, click None, and then click the second format,
1,234.10, in the Negative numbers list. Click the OK button.
3 Select cell H21 to deselect the range H4:K20 to display the numbers in the amortization
schedule as shown in Figure 4–44.
values displayed
using Currency
style with two
decimal places
Round-Off Errors
If you manually add the
numbers in column K
(range K3:K20) and
compare it to the sum in
cell K21, you will notice
that the total interest
paid is $0.01 off. This
round-off error is due
to the fact that some of
the numbers involved in
the computations have
additional decimal places
that do not appear in
the cells. You can use
the ROUND function
on the formula entered
into cell K3 to ensure
that the total is exactly
correct. For information
on the ROUND function,
click the Insert Function
button in the formula
bar, click Math & Trig in
the ‘Or select a category’
list, scroll down in the
‘Select a function’ list,
and then click ROUND.
values displayed
using Comma
style with two
decimal places
values displayed
using Currency
style with two
decimal places
Figure 4–44
To Add Borders and a Background to the Amortization Schedule
The following steps add the borders and a background to the amortization schedule.
1 Select the range G2:K23. Right–click the selected range and then click Format Cells on the
shortcut menu. When Excel displays the Format Cells dialog box, click the Border tab.
2 Click the medium line style in the Style area (column 2, row 5). Click the Outline button in
the Presets area.
3 Click the light line style in the Style area (column 1, row 7). Click the vertical line button in
the Border area.
4 Click the Fill tab and then click light blue (column 5, row 2). Click the OK button.
 
 
Search JabSto ::




Custom Search