GO16_XL_CH05_GRADER_5E_HW – Sports Schedule 1.1

Step

Instructions

Points Possible

1

Start Excel. Download, save, and open the workbook named go16_xl_ch05_grader_5e_hw.xlsx. Save it in your Excel Chapter 5 folder as Lastname_Firstname_5E_Sports_Schedule.

0

2

Go to cell M82, and then insert a COUNTIF function to count the number of Staff referees. In cell K82, type Events with Unassigned Referees.

10

3

Move to cell A1, and then open downloaded the file e05E_Referee_Requests.xlsx. Switch windows, and then make your Lastname_Firstname_5E_Sports_Schedule.xlsx file the active window. Arrange the two files horizontally.

0

4

In the Lastname_Firstname_5E_Sports_Schedule.xlsx workbook, go to cell A82, split the window horizontally, 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.xlsx worksheet, and then type Danny Litowitz in the appropriate cell in the Lastname_Firstname_5E_Sports_Schedule.xlsx workbook to assign him as the Referee.

10

5

Use the Find command to locate the remaining three Event #s listed in the e05E_Referee_Requests.xlsx worksheet, and then enter the appropriate referee for each sports event in column M of your Lastname_Firstname_5E_Sports_Schedule.xlsx worksheet. Close the Find and Replace dialog box, and then close the e05E_Referee_Requests.xlsx workbook. Maximize your Lastname_Firstname_5E_Sports_Schedule.xlsx worksheet, and then remove the Split.

10

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.

10

7

Create a New Table Style named Sports Schedule and format the First Row Stripe with a Fill—in the fifth column, the third color. For the Header Row, in the first column of colors click the second 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.

10

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.

10

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

Insert a Hyperlink in cell J2 to the downloaded e05E_Sports_Coaches.xlsx workbook.

10

12

Edit the ScreenTip to read Click here for contact information and then test your hyperlink. Close the e05E_Sports_Coaches.xlsx workbook.

10

13

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

0

Total Points

100