Reference no: EM131054326

Sports Programs

Project Description:

In this project, you will create a worksheet for the Assistant Director of Athletics at Laurel College to analyze the available sports programs. To complete the project, you will sort and filter data, subtotal and group data, and apply themes to multiple worksheets.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step Instructions

1 Start Excel. Download, save, and open the Excel workbook named GO_e05_Grader_h3.xlsx.

2 Sort the values in the Campus column using a custom sort order list. Use the following order: Valley, Park, and then West.

3 Sort the Sport Group column in ascending order as a second level sort in the table, and then sort the Program Name column in ascending order as a third level sort in the table.

4 Convert the table to a range.

5 Display the Sports Season Comparison worksheet. Name the range A2:F3 using the nameCriteria. Name the range A6:F15 using the name Database, and then name the range A18:F18 using the name Extract.

6 In cell D3, type Fall and in cell E3, type Summer. Create an advanced filter that will copy all records from the Database range with Fall as their primary season and Summer as their secondary season to the range A18:F18.

7 Display the Stipends by Group worksheet. Sort the data in ascending order first by Group and then by Coach Stipend.

8 Subtotal the data on the Stipends by Group worksheet at each change in Group using the Sum function. Add the subtotals to the Coach Stipend column.

9 Collapse the outline so that the Level 2 summary information is displayed, and then autofit columns C:D.

10 Select all three worksheets and modify the page setup so that each worksheet fits on one page and is centered horizontally.

11 With all worksheets still selected, change the theme to Slice.

12 With all worksheets still selected, change the font theme to Corbel.

13 With all worksheets still selected, add the Sheet Name element to the center section of the footer, using the PAGE LAYOUT tab.

14 Ungroup the worksheets and display the Valley-Park-West worksheet. In cell J1, insert a hyperlink to the downloaded e05G_Coach_Information.xlsx workbook. Enter the text Click here for contact information as the ScreenTip for the hyperlink.

15 Change the font color for cell J1 to Orange, Accent 5, Darker 25%.

16 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Valley-Park-West, Sports Season Comparison, Stipends by Group. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Attachment:- e_ch05_gov2_h3.zip