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

Subject:MS ExcelPrice:20.01 Bought8

Share With

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