New Perspectives Excel 2019 | Module 5: SAM Project 1b Min Jee Woo Artwork GENERATING REPORTS FROM MULTIPLE WORKBOOKS GETTING STARTED ? Open the file NP_EX19_5b_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2019 | Module 5: SAM Project 1b

Min Jee Woo Artwork

GENERATING REPORTS FROM MULTIPLE WORKBOOKS

GETTING STARTED

?

Open the file

NP_EX19_5b_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

NP_EX19_5b_

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_5b_Combined_2020.xlsx

?

Support_EX19_5b_Descriptions.docx

?

With the file

NP_EX19_5b_

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.

Min Jee Woo is an artist who makes her living selling her artwork at art fairs around the

Midwest. Min Jee is using an Excel workbook with multiple worksheets to summarize the

sales of her artwork by fair location. She asks for your help in completing the sales

information for this year. Break the external link in the worksheet, so that the formulas in

the range B3:D6 of the

Art Fairs

worksheet are replaced with static values. Then switch

to the

Art Fairs

worksheet.

2.

In cell B5, remove the hyperlink, leaving the unlinked text "Eva Martinez" in the cell.

3.

In cell D4, create a hyperlink to an email address as follows:

a.

Link to the following email address:

info@gateway.example.com

b.

Use

info@gateway.example.com

as the text to display.

c.

Use

Contact the Gateway Art Fair organizers

as the ScreenTip text.

4.

In cell B10, create a hyperlink to a document describing the four art fairs as follows:

a.

Link to the file

Support_NP_EX19_5b_Descriptions.docx

.

b.

Use

Art fair descriptions

as the text to display.

c.

Use

Descriptions of the four art fairs

as the ScreenTip text.

5.

Edit the hyperlink in cell B9 as follows:

a.

Use

Online art fair calendar

as the display text.

b.

Use

Display the calendar of art fairs in the U.S.

as the ScreenTip text.

6.

Min Jee wants to apply consistent formatting to the worksheets she collected from

separate workbooks. Group the

Chicago

,

St. Louis

, and

Minneapolis

worksheets together

and then make the following formatting updates:

a.

Change the font size in the merged range A1:F1 to

16 point

.

b.

Apply the

20% - Accent 1

cell style to the merged range A2:F2.

c.

Bold

the values in the range A5:A8.

d.

Apply the

Accounting

number format with two decimal places and

$

as the

symbol to the range B5:F9.

e.

Resize the column width of columns B:F to

14

. Do not ungroup the worksheets.

7.

With the

Chicago

,

St. Louis

, and

Minneapolis

worksheets still grouped, update the

worksheet as follows:

a.

In cell A8, change the text "Marble" to read:

Stone

b.

In cell A9, change the text "Total" to read:

Total sales

Do not ungroup the worksheets.

8.

With the

Chicago

,

St. Louis

, and

Minneapolis

worksheets still grouped, create a formula

as follows:

a.

Enter a formula in cell B9 using the

SUM

function that totals the sales for Q1.

b.

Copy the formula to the range C9:E9.

Ungroup the worksheets and then check to confirm that the formatting and

formulas from Steps 6-8 are present in all three worksheets.

9.

Min Jee wants to create a copy of the formatted

Minneapolis

worksheet to use for sales

data from the upcoming Madison art fair. Create a copy of the

Minneapolis

worksheet

between the

Minneapolis

worksheet and the

Combined Sales

worksheet, and then

update the worksheet as follows:

a.

Change the worksheet name to

Madison

for the copied worksheet.

b.

Edit the text to read

Madison 2021

in the merged range A2:F2.

c.

Clear the contents of the range B5:E8.

10.

Min Jee wants to combine the sales data from each of the art fairs. Switch to the

Combined Sales

worksheet, and then update the worksheet as follows:

a.

In cell A5, enter a formula without using a function that references cell A5 in the

Madison

worksheet.

b.

Copy the formula from cell A5 to the range A6:A8 without copying the formatting.

c.

In cell B5, enter a formula using the

SUM

function, 3-D references, and grouped

worksheets that totals the values from cell

B5

in the

Chicago:Madison

worksheets.

d.

Copy the formula from cell B5 to the range B6:B8 without copying the formatting.

e.

Copy the formulas and the formatting from the range B5:B8 to the range C5:E8.

(

Hint

: You can ignore the error about empty cells because Min Jee will enter the

Madison sales data later.)

11.

Min Jee started to create named ranges in the worksheet and has asked you to complete

the work. Create a defined name for the range B5:E5 using

Ceramics

as the range

name.

12.

Create names from the range A6:E8 using the values shown in the left column.

13.

Apply the defined names

Q1_Sales

,

Q2_Sales

,

Q3_Sales

, and

Q4_Sales

to the

formulas in the range B9:E9.

14.

Change the defined name to

Total_Sales_2021

for the range F5:F8.

[Mac Hint: Delete

the existing defined name "Totals" and add the new defined name.]

15.

Min Jee wants to compare 2021 sales totals to the sales totals for 2020 and needs to add

the 2020 data to the

Combined Sales

worksheet. Open the file

Support_EX19_5b_Combined_2020.xlsx

. Switch back to the original workbook and go

to the

Combined Sales

worksheet. Create external references as follows:

a.

Link cell G5 in the

Combined Sales

worksheet to cell F5 in the

Combined Sales

worksheet in the

Support_EX19_5b_Combined_2020.xlsx

workbook.

b.

Link cell G6 in the

Combined Sales

worksheet to cell F6 in the

Combined Sales

worksheet in the

Support_EX19_5b_Combined_2020.xlsx

workbook.

c.

Link cell G7 in the

Combined Sales

worksheet to cell F7 in the

Combined Sales

worksheet in the

Support_EX19_5b_Combined_2020.xlsx

workbook.

d.

Link cell G8 in the

Combined Sales

worksheet to cell F8 in the

Combined Sales

worksheet in the

Support_EX19_5b_Combined_2020.xlsx

workbook.

e.

Do not break the links. Close the

Support_EX19_5b_Combined_2020.xlsx

workbook.

16.

In cell G9, enter a formula to total the values in the defined range

Totals_2020

, using

the

SUM

function and the defined range name.

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: Art Fairs Worksheet

Final Figure 2: Chicago Worksheet

Final Figure 3: St. Louis Worksheet

Final Figure 4: Minneapolis Worksheet

Final Figure 5: Madison Worksheet

Final Figure 6: Combined Sales Worksheet