Microsoft Office Tutorials and References
In Depth Information
Using the Auto Fill Options Button
3. In cell C38, enter =C32 to retrieve the January expenses. The relative reference
will change when you copy the formula to other months. You’ll leave cell C39
blank because, at this point, you won’t assume that any money will be transferred
from the main savings account to the home savings account.
4. In cell C40, enter =C36+C37−C38−C39 . This formula calculates the ending
balance for the main savings account, which is equal to the starting balance plus any
deposits minus the withdrawals and transfers. Cell C40 displays 1,940, which is
the projected balance in the main savings account at the end of January.
5. Format the range C36:C39 to add borders around all of the cells.
6. Use the Format Painter to copy the formatting from cell C32 to cell C40 , and then
change the fill color to Blue, Accent 1, Lighter 60% (the fifth color in the third
row of the Theme Colors section) to change the background color of the cell.
7. Click cell A35 . Figure 3-25 shows the formatted January savings values.
balance at the start
dep osits from January income
withdrawals to cover January expenses
balance at the end of
transfers to the down payment savings account (none)
At this point, the couple’s projected savings at the end of January will be $1,940,
which is $2,060 less than their starting balance of $4,000 at the beginning of the year.
The savings formulas for the remaining months are the same as for January except that
their starting balances are based on the ending balance of the previous month. You will
calculate the activity in the couple’s main savings account for the remaining months of
To calculate the remaining balances in the main savings account:
1. Select the range C36:C40 and drag the fill handle over the February savings in
the range D36:D40 .
2. Change the formula in cell D36 to =C40 so that the February starting balance for
the main savings account is based on the January ending balance.
3. Select the range D36:D40 , and then drag the fill handle over the range E36:N40 .
The formulas and formatting from February are copied into the remaining months
of the year.