Microsoft Office Tutorials and References
In Depth Information
In the Lab
Instructions: Perform the following tasks:
1. Start Excel. Enter the worksheet titles Sock-It-Away Stock Club in cell A1 and Summary of Investments
in cell A2.
2. Enter the column titles and data in Table 2 – 8 beginning in row 3.
3. Change the column widths and row heights as follows: column A — 11.78; column C — 10.00;
columns E and G — 7.44; columns F, H, and I — 13.00; column J — 8.22; row 3 — 56.25 points;
row 14 — 27.00 points.
4. Enter the following formulas in row 4 and then copy them down through row 12:
a. Enter Formula A in cell F4: Initial Cost = Shares × Initial Price per Share
b. Enter Formula B in cell H4: Current Value = Shares × Current Price Per Share
c. Enter Formula C in cell I4: Gain/Loss = Current Value – Initial Cost
d. Enter Formula D in cell J4: Percent Gain/Loss = Gain/Loss / Initial Cost
5. Compute the totals for initial cost, current value, gain/loss, and percent gain loss. For the percent
gain/loss in cell J13, copy cell J12 to J13 using the i ll handle.
6. In cells D14, D15, and D16, enter Formulas E, F, and G using the AVERAGE, MAX, and MIN
functions. Copy the three functions across through the range J14: J16. Delete the invalid formula
in cell J14.
7. Format the worksheet as follows:
a. Apply the Trek theme to the worksheet.
b. Format the worksheet title with Title cell style. Merge and center across columns A through J.
c. Format the worksheet subtitle with Franklin Gothic Book font, 16 point font size, Black, Text 1
theme font color. Merge and Center across columns A through J.
d. Format the worksheet title and subtitle background with Orange, Accent 1, Lighter 60% theme
color and a thick box border.
e. Format row 3 with the Heading 3 cell style and row 13 with the Total cell style.
f. Format the data in rows 4 through 12: center data in column B; format dates in column C to
the mm/dd/yy date format; range E4:I4 — Accounting number format style with i xed dollar
sign; range E5:I12 — Comma style; range J4:J13 — Percent style with two decimal places;
cells F13, H13, and I13 — Accounting Number format with i xed dollar sign.
g. Format E14:I16 — Currency format with l oating decimal places; J15:J16 — Percent style with
two decimal places.
h. Format J4:J12 — apply conditional formatting so that if a cell in range is less than 0, then cell
appears with a pink background color.
8. Spell check the worksheet. Change the name of the sheet tab to Summary of Investments and
apply the Orange, Accent 1, Darker 25% theme color to the sheet tab. Update the document
properties, and save the workbook using the i le name, Lab 2-3 Sock-It-Away Stock Club
Summary of Investments. Print the worksheet in landscape orientation. Print the formulas version
on one page. Close the workbook without saving changes. Submit the assignment as specii ed by