In this project, you will use Excel to analyze and summarize a subset of data from the Olympic Games. To complete the project, you will use text functions, conditional formatting, conditional aggregate functions, and more. This project covers the basic skills required for the Microsoft Office Specialist Exam 77-727 for Microsoft Excel 2016. Steps to Perform:
Step
Instructions
Points Possible
1 Start Excel 2016. Open the downloaded file named OlympicAnalysis_start. xlsx and save the file as OlympicAnalysis_LastFirst. xlsx, using your first and last names. 0
2 Apply a Berlin Theme to the workbook. 3
3 On the Data worksheet, in cell J1, type a new column heading of FirstName. In cell K1, type a new column heading of LastName. 2
4 In cell J2, use the LEFT and FIND functions to extract the first name from cell A2 and then autofill the function down to J55. Adjust the width of column J to 10.
=LEFT(A2,FIND(" ",A2)-1) 8
5 In cell K2, use the RIGHT, LEN, and FIND functions to extract the last name from cell A2 and then autofill the function down to K55. Adjust the width of column K to 15.
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITU TE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","" 8
6 Apply Conditional Formatting on the Total Medals data in cells I2:I55, so that values more than 1 appear with a White, Bold font, and a Light Blue fill. 6
7 On the Summary worksheet, in cell A1, enter the title Medals Earned and then apply the Bold and Italic font styles to the text. AutoFit the column width so that all text is visible. 5
8 In cell B2 of the Summary worksheet, use the SUMIF function to calculate the number of Gold medals earned by the United States using appropriate cell references to copy the formula down to row 5. In cell C2 of the Summary worksheet, use the SUMIF function to calculate the number of Silver medals earned by the United States using appropriate cell references to copy the formula down to row 5. Continue in this manner to create formulas in D2 and E2 to calculate the Bronze and Total medals using appropriate cell references to copy the formula down through row 5. AutoFill the formula down through row 5 so that all medal data is complete for all countries. 20
9 Convert the range A1:E5 on the Summary worksheet to a table with headers using Table Style Medium 5. 7
10 Add a Total Row to the table and sort the Total medals with the highest amount on top. 6
11 Create a Clustered Column chart that compares the gold, silver, and bronze medals earned by the United States, Great Britain, and China. The countries should make up the Legend Entries (Series) and the medals should make up the Horizontal (Category) Axis Labels. 11
12 Apply Chart Style 5 to the chart and change the color to the fourth option under Monochromatic. 4
13 Add a chart tile of Medals Earned and move the chart so that the top-left corner is in the top-left corner of cell G1. 4
14 Merge & Center cells A8:B8 on the Summary worksheet. 5
15 In cell B10, use the AVERAGEIF function to calculate the average age of Olympians from the United States. Write the formula with the appropriate cell referencing so that it can be copied down through row 12 using AutoFill. AutoFill the formula down through row 13. 8
16 Move the Summary worksheet to the left of the Data worksheet. 3
17 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Summary, Data. Save the file, close the Excel window, and then submit the file as directed. 0
Total Points 100