Ruben Hernandez is the head of the Web Development Department at Brand Deluxe, a web design agency in Brooklyn, New York. He is preparing a draft of a workbook to send to new hires who are joining the department and asks for your help in importing data and adding other content to the workbook. Go to the Department worksheet. Change the style of the "Brand Deluxe" WordArt text to Fill: Aqua, Accent color 1; Shadow to match the style of the company name on other worksheets. Change the text outline color of the WordArt to Aqua, Accent 1.

Copy the formatting from cell E3 to cell D3 to use consistent formatting for the column headings.

The worksheet should list information about the department members, which is contained in a webpage. Get data from the Support_EX19_EOM7-2_Department.html webpage. (Hint: Use Windows Explorer to navigate to the Support_EX19_EOM7-2_Department.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 Web Development Department Members webpage data as a table to cell G11 in the existing worksheet. [Mac hint - Import the file from HTML, copy the data starting in row 2 and paste it to cell G11 in the Department worksheet. Format the imported data as a table with headers and apply the Brown, Table Style Medium 7 table style.]

In the imported table, delete the table row for Brett Warrick because he has moved to a different department. Find the text "David Energy" and replace it with Davis Energy to use the correct company name.

1

Shelly Cashman Excel 2019 | Module 7: End of Module Project 2

Ruben wants to list the department information in the range A4:E9. The webpage table separated the first and last names, but Ruben wants to list the full name on the Department worksheet. In cell A4, enter a formula using the CONCAT function that displays the first name shown in cell G12 followed by a space (" ") and then the last name shown in cell H12. Fill the range A5:A9 with the formula in cell A4 to list the full names of the remaining department members.

Begin to incorporate the imported data in the range B4:E9 by copying the Position data from the range I12:I17 and paste only the values in the range B4:B9.

In cell C4, enter a formula using the PROPER function to capitalize the first letter in each word in the Current Project text in cell J12. Fill the range C5:C9 with the formula in cell C4 to list the names of the remaining projects.

In cell D4, enter a formula using the LEFT function to insert the first 2 characters on the left of cell K12. Copy the formula in cell D4 to the range D5:D9.

In cell E4, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell L12. Copy the formula in cell E4 to the range E5:E9.

Resize columns A:E to their best fit, resize column G to 18.25, and resize column H to 13.00. Hide rows 11 to 17 so that the worksheet does not display duplicated data.

Ruben already imported project summary data on the Project Summary worksheet but wants to display the data in the range G4:H7 of the Department worksheet. Go to the Project Summary worksheet. Copy the data in the range A1:D2, and then transpose the rows and columns as you paste the data on the Department worksheet starting in cell G4.

In the Department worksheet, insert and format a picture of Web Development Department members by inserting the picture Support_EX19_EOM7-2_Group.jpg. Position the picture so that the upper-left corner is in cell G18 and the lower-right corner is in cell J33.

Add a border to the picture using Brown, Accent 6 as the border color to coordinate with other borders on the worksheet. Apply the Offset: Center picture effect from the Outer section of the Shadow gallery.

Ruben also wants to provide a diagram of the major planning steps the department takes for any web development project. Insert the Vertical Curved List SmartArt from the List section of the SmartArt gallery. Move and resize the SmartArt so that the upper-left corner is in cell A18 and the lower-right corner is in cell E33.

Enter Gather information in the top shape. Enter Plan site in the second shape and then add a shape after so that it appears below the "Plan Site" shape. Enter Design webpages in the new shape. Enter Write content in the bottom shape.

Go to the Resources worksheet, which Ruben asks you to finish. In cell D12, use the Quick Analysis tools to insert the total number of resources from the range D4:D11. (Hint: Ignore the errors if any appear.) [Mac hint - Use the AutoSum button.]

Ruben also wants to format the column chart in the range G3:L14 to make it easier to understand. Switch the rows and columns so that the chart compares total and estimated resources.