Use the following data file for this Exam: hockey data.xlsx

Download hockey data.xlsx

Part 1. Create a macro that creates a new column to the right of column E with the players' names in "Last, First" format. Name the macro appropriately, and assign a shortcut key of Cntrl+Shift+C.

Part 2. Create pivot tables to address each of the following. Label each tab you create appropriately ("Part 2a", "Part 2b", etc). Use formatting as needed (e.g., you don't need averages to 8 decimal places!)

a. How many players are there by country, by gender?

b. How many players are there by Province by position?

c. What is the average, minimum and maximum height and weight by gender, by position

d. Create a chart showing the number of players by province. Use your judgement as to what kind of chart shows this information most clearly.

e. In the data worksheet, use conditional formatting to highlight all players over age 40 in light red.

Save your Excel file - be SURE to save it as a macro-enabled file. Upload it to Canvas.

Part 3. Download a fresh copy of the data file. Create an Access database that uses the data file as an external source. Create a parameter query that asks for a Hometown, and returns all players who are from that hometown. Save the Access file and upload both the Access file AND the Excel file it uses as a source to Canvas.

When you are done, you should have submitted THREE files to Canvas.