Shelly Cashman Excel 2019 | Module 7: End of Module Project 2 Brand Deluxe IMPORT DATA AND WORK WITH SMARTART AND IMAGES GETTING STARTED ? Open the file SC_EX19_EOM7-2_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought4

Share With

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

Brand Deluxe

IMPORT DATA AND WORK WITH SMARTART AND IMAGES

GETTING STARTED

?

Open the file

SC_EX19_EOM7-2_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

SC_EX19_EOM7-2_

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_EOM7-2_Department.html

?

Support_EX19_EOM7-2_Group.jpg

?

With the file

SC_EX19_EOM7-2_

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.

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.

2.

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

headings.

3.

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.]

4.

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.

5.

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.

6.

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.

7.

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.

8.

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.

9.

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.

10.

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.

11.

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.

12.

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.

13.

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.

14.

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.

15.

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.

16.

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.]

17.

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.

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: Department Worksheet

Final Figure 2: Project Summary Worksheet

Final Figure 3: Resources Worksheet