Excel_5E_Sports_Schedule

Step

Instructions

Points Possible

1

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

0

2

Go To cell M82, and then insert a COUNTIF function to count the number of Staff referees using the range M1:M80 in the formula. In cell K82, type Events with Unassigned Referees.

16

3

Move to cell A1, and then from the files you downloaded for this project, Open the file e05E_Referee_Requests. Switch windows, and then make your 5E_Sports_Schedule the active window. Arrange the two files horizontally. Mac users, do not open the file, or if you have opened the e05E_Referee_Requests workbook, close the file. In your 5E_Sports_Schedule file, click cell M48, type Danny Litowitz and then make cell A1 the active cell. Skip Steps 4 and 5, and then continue with Step 6.

1

4

In your 5E_Sports_Schedule workbook, Go To cell A82, Split the window, and then click in any cell above the split in column C. Use Find to locate the first Event # request from the e05E_Referee_Requests worksheet, and then in cell M48 in your 5E_Sports_Schedule, type Danny Litowitz to assign him as the Referee.

10

5

Close the Find and Replace dialog box, and then Close the e05E_Referee_Requests workbook. Maximize your 5E_Sports_Schedule worksheet, and then remove the Split.

1

6

Insert a new blank row 1. In cell A1, type Schedule of Sports Events with Unassigned Referees and then Merge & Center the title across the range A1:M1. Apply the Title cell style.

18

7

Create a New Table Style named Sports Schedule and format the First Row Stripe with a Fill—in the last column, the third color. Set the style you created as the default table style for this workbook.

10

8

Select the range A2:M81 and apply the Custom table style—Sports Schedule.

12

9

AutoFit columns A:M. Set the Orientation to Landscape, set the Width to 1 page, and center the worksheet Horizontally. Insert a footer in the left section that includes the file name, and in the right section, insert a page number.

12

10

In Page Break Preview, break Page 1 after row 49 to end Page 1 with TENNIS and begin Page 2 with TRACK. Return to Normal view. Set Print Titles to repeat row 2 at the top of each page.

10

11

In cell J2, Insert a Hyperlink to your student data file e05E_Sports_Coaches. Create the ScreenTip Click here for contact information and then test your hyperlink. Close the e05E_Sports_Coaches file.

10

12

Save your workbook and then close Excel. Submit the workbook as directed.

0

Total Points

100