Excel Expert
3/27/2021 Integrated Report
https://mil-api.prd-prsn.com/myitlab-business-service/project/launch 1/2
Score Card Detail
Total Score: 77%
Earned Points: 77pts
Possible Points: 100pts
Step Instruction Earned Possible
1 0 0
2 0 4
3 5 8
4 7 7
5 6 6
6 6 6
7 0 6
8 8 8
9 5 5
10 12 12
11 6 6
Start Excel. Open the downloaded file named e01ch02_grader_h1_Portfolio.xlsx. Save it as e01ch02_grader_h1_Portfolio_LastFirst using your last and first name.
On the DividendPortfolio worksheet, center and bold cell range B2:F3.
Merge & Center cell range A1:N1. In cell A1, apply the Title cell Style and bold the title.
Select cell range N12:N16 and N20:N24, and then calculate row totals using the SUM function.
In cell G8, calculate the average of Total Dividends Received.
Apply cell style Heading 3 to cells G7, B10, and B18.
Apply the Currency format to cell ranges B4:F4, B6:F6, B8:G8, B12:N16, and B20:N25.
In B7, calculate the yield of Prime Steel - the annual dividends per share divided by the price per share. Copy the formulas to cell range C7:F7. Format the yield figures as Percent Style with two decimal places.
Hide rows 10:17.
Format cell range A19:N24 as a table with headers. Apply Table Style Light 11. Sort by STOCK from A-Z, and then turn off Filters. Note, depending upon the version of Office being used, the table style name may be White, Table Style Light 11.
Add a Total Row to the table, and calculate the sum of columns B:N.
3/27/2021 Integrated Report
https://mil-api.prd-prsn.com/myitlab-business-service/project/launch 2/2
12 4 4
13 4 4
14 0 6
15 4 4
16 4 4
17 0 4
18 6 6
19 0 0
Apply a top border to cell range A25:N25.
Apply cell style 40% - Accent3 to cell ranges A20:A25, B25:N25, and N20:N24.
Apply Conditional Formatting to cell range B20:M24. Display the Top 10 items as Green Fill with Dark Green Text.
Turn off gridlines in the DividendPortfolio worksheet.
In the Documentation worksheet, enter 12/15/2018 into cell A8. In cell B8, type your name in the Firstname Lastname format. In cell C8, type Completed Mr. Malley's monthly dividend income worksheet and then make cell A1 the active cell.
Spell check the entire workbook and correct the one misspelling. Insert the file name on the left page footer on all worksheets.
For the DividendPortfolio and Documentation worksheets, set the Orientation to Landscape, and then set the Width to 1 page. Change the Print Setting to Print Entire Workbook.
Save the workbook, exit Excel, and then submit your file as directed by your instructor.