GETTING STARTED
 Open the file SC_EX19_7b_FirstLastName_1.xlsx, available for
download from the SAM website.
 Save the file as SC_EX19_7b_FirstLastName_2.xlsx by changing
the “1” to a “2”.
 If you do not see the .xlsx file extension in the Save As dialog box, do
not type it. The program will add the file extension for you
automatically.
 To complete this SAM Project, you will also need to download and save
the following data files from the SAM website onto your computer:
 Support_EX19_7b_Banner.pptx
 Support_EX19_7b_Committees.html
 Support_EX19_7b_Registered.xlsx
 Support_EX19_7b_Reunion.jpg
 Support_EX19_7b_2012.jpg
 With the file SC_EX19_7b_FirstLastName_2.xlsx still open, ensure
that your first and last name is displayed in cell B6 of the
Documentation sheet.
 If cell B6 does not display your name, delete the file and download a
new copy from the SAM website.
 If a dialog box appears about updating links, don’t update the links
and click Continue.
PROJECT STEPS

1. Clare Seeley is the chair of the Class Reunion committee for the 2012
   class of Chisholm-Grant Academy in Hanover, New Hampshire. She is
   preparing a draft of a workbook to send to classmates involved in
   planning the reunion and asks for your help in importing data and
   adding other content to the workbook.
   Go to the Reunion Organizers worksheet. Change the formatting of
   cell D4 and use the tools on the Drawing Tools Format tab to modify
   the WordArt containing the worksheet title, “Chisholm-Grant
   Academy”, as follows to coordinate these elements with the rest of
   the worksheet:
   a. Change the text fill color of the WordArt to Green, Accent 1.
   b. Change the text outline color of the WordArt to Dark Green,
   Accent 2.
   c. Copy the formatting from cell C4 to cell D4.
2. The worksheet should list information about the reunion committee
   members, which is contained in a webpage.
   Import data from the webpage as follows:
   a. In the Reunion Organizers worksheet, get data from the
   Support_EX19_7b_Committees.html webpage. (Hint: Use
   Windows Explorer to navigate to the
   Support_EX19_7b_Committees.html webpage, click in the
   Address bar, and then copy the path. In the From Web dialog
   box, paste the path in the URL box.) Import only the 2022
   Reunion Committee Members webpage data as a table to
   cell G12 in the existing worksheet.
   b. Format the imported data in the range G12:L19 using White,
   Table Style Light 8.
3. In the imported table, change some data to reflect updates in the
   committees:
   a. Delete the table row for Kayla Chen because she can no longer
   serve on the Food and Beverage committee.
   b. Find the text “Registration” and replace it with Welcome to use
   the correct committee name.
4. Clare wants to list the committee information in the range A5:E10.
   The webpage table separated the first and last names, but Clare
   wants to list the full name on the Reunion Organizers worksheet.
   List the first and last names of each committee member in a single
   cell as follows:
   a. In cell A5, enter a formula using the CONCAT function that
   displays the first name shown in cell G13 followed by a space (“
   “) and then the last name shown in cell H13.
   b. Fill the range A6:A10 with the formula in cell A5 to list the full
   names of the remaining committee members.
5. Incorporate the imported data in the range B5:E10 as follows:
   a. Copy the Committee data from the range I13:I18 and paste only
   the values in the range B5:B10.
   b. In cell C5, enter a formula using the PROPER function to
   capitalize the first letter in each word in the Role text in cell J13.
   c. Fill the range C6:C10 with the formula in cell C5 to list the roles
   of the remaining committee members.
   d. In cell D5, enter a formula using the LEFT function to insert the
   first 3 characters on the left of cell K13. Copy the formula in cell
   D5 to the range D6:D10.
   e. In cell E5, enter a formula using the RIGHT function to insert
   the last 2 characters on the right of cell L13. Copy the formula
   in cell E5 to the range E6:E10.
   f. Resize columns A:E to their best fit, resize column G to 19.00,
   and resize column H to 14.00.
   g. Hide rows 12–18 so that the worksheet does not display
   duplicated data.
6. lly Cashman Excel 2019 | Module 7: SAM Project 1b 6. Clare already imported a budget summary on the Budget worksheet
7. but wants to display the data in the range G5:H8 of the Reunion
8. Organizers worksheet. She asks you to switch the rows and columns
9. when you insert the data to fit in the range G5:H8.
10. Go to the Budget worksheet. Copy the data in the range A1:D2, and
11. then transpose the rows and columns as you paste the data on the
12. Reunion Organizers worksheet starting in cell G5.
13. In the Reunion Organizers worksheet, insert and format a picture of
    the organizers for the fifth class reunion as follows as motivation for
    the current organizers:
    a. Insert the picture Support_EX19_7b_Reunion.jpg.
    b. Move and resize the picture proportionally so that the upper-left
    corner is in cell G20 and the lower-right corner is in cell J34.
    c. Add a border to the picture using Dark Green, Accent 2 as the
    border color to coordinate the picture with the rest of the
    worksheet.
    d. Apply the Offset: Bottom Right picture effect from the Outer
    section of the Shadow gallery.
14. Add a caption to identify the picture as follows:
    a. In cell H35, insert a Text Box from the Basic Shapes section of
    the Shapes gallery and move the text box so that it is centered
    below the picture in rows 35 and 36.
    b. Enter 5th reunion organizers in the text box.
    c. Resize the text box to a height of 0.3″ and a width of 2″.
15. Clare also wants to provide a diagram of major tasks the committees
    need to complete in the coming weeks. Insert SmartArt as follows:
    a. Insert the Continuous Block Process SmartArt from the
    Process section of the SmartArt gallery.
    b. Move and resize the SmartArt so that the upper-left corner is in
    cell A20 and the lower-right corner is in cell E34.
16. Add text to the SmartArt as follows, using Figure 1 as a guide:
    a. Enter Planning meetings in the first shape on the left.
    b. Enter Schedule in the second shape and then add a shape after
    so that it appears to the next to the “Schedule” shape.
    c. Enter Invite list in the new shape.
    d. Enter Organize events in the last shape.
    Figure 1: SmartArt Text
17. Add a caption to identify the SmartArt as follows:
    a. In cell B35, insert a Text Box from the Basic Shapes section of
    the Shapes gallery. Move the text box so that it is centered
    below the SmartArt in rows 35 and 36. Then align the text box
    with the top of the “5th reunion organizers” text box.
    b. Enter Major tasks this summer in the text box.
    c. Resize the text box to a height of 0.3″ and a width of 2″.
18. Hide the gridlines on the worksheet to increase its visual appeal.
19. Clare wants to include a banner showing the social media websites
    where the reunion committee posts information. She has the banner
    stored in a PowerPoint presentation. Include the banner as follows:
    a. Use PowerPoint to open the presentation
    Support_EX19_7b_Banner.pptx.
    b. In Excel, use the Screen Clipping tool to paste a screenshot of
    only the banner into the Reunion Organizers worksheet.
    c. Position the upper-left corner of the screenshot image in cell
    B38.
20. Go to the Registrations worksheet, which Clare asks you to finish. The
    worksheet compares the number of registrations for the 5th and 10th
    reunions.
    Clare inserted the data in the range A19:C26 as a link to another
    worksheet. Complete this part of the worksheet and break the link as
    follows:
    a. In cell D27, use the Quick Analysis tools to insert the total
    number of registrations to date from the range D19:D26. (Hint:
    Ignore the errors if any appear.)
    b. Use the Quick Analysis tool to create a Conditional Formatting
    rule that adds Solid Fill Blue Data Bars to the range F19:F26
    to help Clare visualize the data.
    c. Break the link to the Support_EX19_7b_Registered.xlsx
    workbook because Clare no longer needs to update the data.
21. Clare wants to show the total registrations so far for the 10th reunion
    compared to the 5th reunion on the same date.
    Insert a chart in the Registrations worksheet as follows to show this
    information:
    a. Based on the nonadjacent data in the Date (range A18:A26),
    Total 10th Reunion (range D18:D26), and Total 5th Reunion
    (range E18:E26) columns, insert the first type of chart that Excel
    recommends, which is a Line chart.
    b. Move and resize the chart so that its upper-left corner is in cell
    A3 and its lower-right corner is in cell F17.
    c. Remove the chart title because the worksheet title identifies the
    data clearly.
22. elly Cashman Excel 2019 | Module 7: SAM Project 1b 16. Clare wants to make sure that people reviewing the worksheet
23. understand it displays registrations only up to June 15.
24. Add a shape to the worksheet as follows to provide this information:
25. a. In cell B28, insert a Callout: Line shape from the Callouts
26. section of the Shapes gallery.
27. b. Move the callout line so that it points to the bottom of the
28. “Date” column.
29. c. Type Data up to June 15 in the callout shape.
30. d. Apply the Subtle Effect—Green, Accent 1 shape style to the
31. callout shape.
32. Clare wants to format the column chart in the range G18:L30 to
    increase its appeal. She also wants to change the layout of the chart
    so that it provides another way to compare the total registrations by
    date.
    Modify the column chart as follows:
    a. Switch the rows and columns so that the chart compares the
    totals for the 10th and 5th reunions for the two-week periods 1–
    8.
    b. Apply the Green, Accent 1, Lighter 80% shape fill color to the
    plot area of the chart.
    c. Apply the Offset: Center shape effect from the Outer section of
    the Shadow gallery to the legend.
33. Clare wants to add one more element of visual interest on the
    worksheet.
    a. Insert a picture using the support file
    Support_EX19_7b_2012.jpg.
    b. Reposition and resize the picture so that its upper-left corner is
    within cell G3 and the lower-right corner is within cell L12.