Illustrated Excel 2019 | Module 6: SAM Project 1a MusicBot MANAGE WORKBOOK DATA GETTING STARTED ? Open the file IL_EX19_6a_ FirstLastName _1

Subject:MS ExcelPrice:20.01 Bought8

Share With

Illustrated Excel 2019 | Module 6: SAM Project 1a

MusicBot

MANAGE WORKBOOK DATA

GETTING STARTED

?

Open the file

IL_EX19_6a_

FirstLastName

_1.xlsx

, available for download from the SAM

website.

?

Save the file as

IL_EX19_6a_

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

?

With the file

IL_EX19_6a_

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.

Kendra Liang is a senior producer at MusicBot, an online source for all types of music.

Kendra is using an Excel workbook to analyze sales for the first year of the online

service. She asks you to update the workbook and make the data more secure and

easier to understand.

Go to the

Quarter 1

worksheet and then unprotect the worksheet so you can update it.

2.

Kendra wants to focus on the Quarter 1 revenue from CDs and vinyl sales, which are the

lowest category of sales.

Create a custom view as follows:

a.

Hide rows 3:20 to display only the CDs and Vinyl category of sales.

b.

Select cell G1 and then create a custom view using

CDs_Vinyl

as the name.

3.

Display the

Quarter 1

worksheet using the custom view

Q1_All_Categories

, which

Kendra already created, to redisplay all of the data.

4.

Kendra hid the

Quarter 3

worksheet because she still needs to verify the sales figures.

However, she wants to change an unnecessary setting in the worksheet.

Update the worksheet as follows:

a.

Unhide the

Quarter 3

worksheet so you can edit it.

b.

Unfreeze the first column because the worksheet is not wide enough to require

horizontal scrolling.

5.

The company recently changed its name from MusicBot Online to MusicBot. Kendra asks

you to change the name on all worksheets.

Group the

Quarter 1

,

Quarter 2

,

Quarter 3

,

Quarter 4

, and

Current Month

worksheets,

and then change the company name to

MusicBot

in merged cell A1.

6.

With the worksheets still grouped, format cell

A1 using the

Orange, Accent 1, Lighter

80%

fill color to provide more visual interest to all five worksheets.

Illustrated Excel 2019 | Module 6: SAM Project 1a

 

Illustrated Excel 2019 | Module 6: SAM Project 1a

7.

With the worksheets still grouped, set a

1"

custom margin at the Top of the worksheets

to prepare for printing the data. Ungroup the worksheets.

8.

Kendra wants to add a hyperlink to the worksheets to make it easier to navigate. She

asks you to add the first hyperlink to show her how it works. She also wants to add a

hyperlink to the company website and another to her email address so people reviewing

the workbook data can contact her.

Add hyperlinks to the

Quarter 1

worksheet as follows:

a.

On the

Quarter 1

worksheet, type

Quarter 2

in cell A27 to enter the text to use as

a hyperlink.

b.

In cell A27, add a hyperlink that links to cell A1 of the

Quarter 2

worksheet in the

current workbook.

c.

Add a ScreenTip using

Next Quarter

as the ScreenTip text.

d.

In cell A28, add a hyperlink that links the "MusicBot website" text to the

www.musicbot.example.com

website.

e.

In cell A29, add a hyperlink that links the "Contact Kendra Liang" text to the

kl@musicbot.example.com

email address.

9.

Use the hyperlink in cell A27 to go to the

Quarter 2

worksheet. In cell A3, remove the

hyperlink, which Kendra no longer needs.

10.

Kendra has verified all of the Quarter 2 revenue data except for the data in the CDs and

Vinyl category. She asks you to secure the rest of the

Quarter 2

worksheet data so that

no one can change it.

Protect the worksheet as follows:

a.

Unlock the CDs and Vinyl data (range B22:D25) so that Kendra can change it later.

b.

Protect the

Quarter 2

worksheet so that users can unprotect it without a password.

Also allow users to select locked and unlocked cells.

11.

Kendra plans to print the

Quarter 2

worksheet, but wants to keep the CDs and Vinyl data

separate from the Streaming, Songs, and Albums data. Insert a page break so that row

21 prints on a new page.

12.

Kendra will verify the data on the

Quarter 3

worksheet by comparing it to the original

data in another workbook. She wants to use a hyperlink to access the other workbook

quickly.

Add a hyperlink to the

Quarter 3

worksheet as follows:

a.

Go to the

Quarter 3

worksheet, and then type

Original data

in cell A26 to enter

the text to use as a hyperlink.

b.

In cell A26, add a hyperlink that links to the

Support_EX19_6a_Q3.xlsx

workbook.

13.

Hide the

Quarter 3

worksheet so that no one can view the unverified data.

14.

Quarter 4 had the highest sales in most categories. Kendra wants to be able to focus on

the totals and then switch to a detailed of the data in each category.

Go to the

Quarter 4

worksheet and create an outline as follows:

a.

Group rows 4:6 so you can hide and display Streaming revenue details.

b.

Collapse the outline to display only the name and totals for each category.

 

Illustrated Excel 2019 | Module 6: SAM Project 1a

15.

Go to the

Current Month

worksheet, which lists sales for the month of January sorted by

sales type. Kendra wants to see the sales totals for each sales type. Display the subtotals

as follows:

a.

Add subtotals to the

Sales Amount

column at each change in the Sales Type.

b.

Use the Sum function and display the summary below the data.

16.

To prepare for printing the

Current Month

worksheet, change the right margin to

2.25"

.

17.

Add

Quarterly Revenue

in the Categories document property so that Kendra can find

the workbook easily when she is searching for files.

18.

Kendra plans to share the workbook with other people at MusicBot, though some of them

use earlier versions of Excel. Check the compatibility of the workbook with earlier

versions of Excel, and then copy the Compatibility Report to a new worksheet.

19.

Because the workbook contains sensitive data, protect the workbook as follows:

a.

Protect the workbook structure using

MusicBot

as the password.

b.

Mark the workbook as final. (

Hint

: Click OK when prompted to confirm.)

Your workbook should look like the Final Figures on the following pages. The Compatibility Report

worksheet has intentionally not been shown. Save your changes, close the workbook, and then

exit Excel. Follow the directions on the SAM website to submit your completed project.

 

Illustrated Excel 2019 | Module 6: SAM Project 1a

Final Figure 1: Quarter 1 Worksheet

Final Figure 2: Quarter 2 Worksheet

Final Figure 3: Quarter 4 Worksheet

Final Figure 4: Current Month Worksheet