Microsoft Office Tutorials and References
In Depth Information
Excel Chapter 2 Formulas, Functions, Formatting, and Web Queries
In the Lab continued
Instructions Part 1: Create a worksheet similar to the one shown in Figure 2–89. Include the ﬁ ve columns
of customer data in Table 2–9 in the report, plus two additional columns to compute a service charge
and a new balance for each customer. Assume no negative unpaid monthly balances.
Perform the following tasks:
1. Enter and format the worksheet title Jackson's Bright Ideas and worksheet subtitle
Monthly Balance Due Report in cells A1 and A2. Change the theme of the worksheet to
the Technic theme. Apply the Title cell style to cells A1 and A2. Change the font size in cell A1
to 28 points. One at a time, merge and center the worksheet title and subtitle across columns A
through G. Change the background color of cells A1 and A2 to yellow (column 4 in the Standard
Colors area in the Font Color palette). Draw a thick box border around the range A1:A2.
2. Change the width of column A to 20.00 characters. Change the widths of columns B through G to
12.00. Change the heights of row 3 to 36.00 and row 12 to 30.00 points.
3. Enter the column titles in row 3 and row titles in the range A11:A14 as shown in Figure 2–89.
Center the column titles in the range A3:G3. Apply the Heading 3 cell style to the range A3:G3.
Bold the titles in the range A11:A14. Apply the Total cell style to the range A11:G11. Change the
font size of the cells in the range A3:G14 to 12 points.
4. Enter the data in Table 2–9 in the range A4:E10.
5. Use the following formulas to determine the service charge in column F and the new balance in
column G for the ﬁ rst customer. Copy the two formulas down through the remaining customers.
a. Service Charge (cell F4) = 2.75% * (Beginning Balance – Payments – Credits)
or = 0.0275 * (B4 – C4 – D4)
b. New Balance (G4) = Beginning Balance + Purchases – Credits – Payments + Service Charge
or =B4 + E4 – C4 – D4 + F4
6. Determine the totals in row 11.
3/6/07 3:30:19 PM
3/6/07 3:30:19 PM