$10$:$46$

Google

Chisholm$-$Grant Academy

IMPORT DATA AND WORK WITH SMARTART AND IMAGESClare Seeley is the chair of the Class Reunion committee for the $2012$ class of ChisholmGrant 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 importingdata and adding other content to the workbook.Go to the Reunion Organizers worksheet. Change the formatting of cell D$4$ and use the tools on the Shape Format tab to modify the WordArt containing the worksheet titie, "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 C$4$ to cell D$4.$The worksheet should list information about the reunion committee members, which is contained in a webpage.

Import data from the webpage as follows:

a$.$ From the Data tab, use 'From HTML$\text{'}$ to open the Support$\_$EX$19\_7$b$\_$Mac$\_$Committees.html webpage as a table in a new workbook.

b$.$ Copy the table, with column headers included, from the new workbook. In the Reunion Organizers worksheet, paste the table in cell G$12.$ Close the new workbook without saving.

c$.$ Format the pasted data in the range G$12$:L$19$ as a table using White, Table Style Light $8.($Hint: Make sure $"$My table has headers" is checked.$)$

$3.$ In the new 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 A$5$:E$10.$ The webpage table

List the first and last names of each committee member in a single cell as follows:

a$.$ In cell A$5,$ enter a formula using the CONCAT function that displays the first name shown in cell G$13$ followed by a space $("")$ and then the last name shown in cell H$13.$

b$.$ Fill the range A$6$:A$10$ 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 $113$:$118$ and paste only the values in the range $B5$:$B10.$

b$.$ In cell C$5,$ enter a formula using the PROPER function to capitalize the first letter in each word in the Role text in cell J$13.$

c$.$ Fill the range C$6$:C$18$

committee member $,$ Yula in cell C$5$ to list the roles of the remaining

d$.$ In cell D$5,$ enter a LEFT function to insert the first $3$ characters on the left of cell K nula in cell D$5$ to the range D$6$:D$10.$

e$.$ In cell E$5,$ enter a A RIGHT function to insert the last $2$ characters $-$

f$.$ Resize columns A:E to their best fit, resize column G to $19\mathrm{.}00,$ and resize column H to $14\mathrm{.}00.$

g$.$ Hide rows $12-18$ so that the worksheet does not display duplicated data.

Ask a follow$-$up

$?$

$$

${}_{}^{+}+$