Shelly Cashman Excel 2019 | Module 7: SAM Project 1b Chisholm-Grant Academy IMPORT DATA AND WORK WITH SMARTART AND IMAGES 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. 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. Clare already imported a budget summary on the Budget worksheet but wants to display the data in the range G5:H8 of the Reunion Organizers worksheet. She asks you to switch the rows and columns when you insert the data to fit in the range G5:H8. Go to the Budget worksheet. Copy the data in the range A1:D2, and then transpose the rows and columns as you paste the data on the Reunion Organizers worksheet starting in cell G5. 7. 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. 8. 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" . 9. 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. 10. 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 enter 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 11. 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" . 12. Hide the gridlines on the worksheet to increase its visual appeal. Shelly Cashman Excel 2019 | Module 7: SAM Project 1b 13. 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. 14. 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. 15. 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. 16. Clare wants to make sure that people reviewing the worksheet understand it displays registrations only up to June 15. Add a shape to the worksheet as follows to provide this information: a. In cell B28, insert a Callout: Line shape from the Callouts section of the Shapes gallery. b. Move the callout line so that it points to the bottom of the "Date" column. c. Type Data up to June 15 in the callout shape. d. Apply the Subtle Effect—Green, Accent 1 shape style to the callout shape. 17. 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. 18. 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. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Reunion Organizers Worksheet Final Figure 2: Registrations Worksheet