Illustrated Excel 2016 Module 6: SAM Project 1a Changhao Long 1

Subject:MS ExcelPrice:14.99 Bought3

Share With

Illustrated Excel 2016 Module 6: SAM Project 1a

Changhao Long

1. 

You are working as an intern during the summer in the accounting department of Wellness Spa. The company is using

an Excel workbook to analyze its revenue for the first half of the year. Your manager, Lexie, has asked you to protect

the workbook and provide documentation to make the revenue data more secure and easier to understand.

Switch to the January worksheet and unprotect the worksheet.

2. The senior masseuse wants to review the January revenue for her department. Lexie asks you to make it easy to

display only the massage revenue.

Hide rows 8:24, then select cell A1. Create a custom view using Massage_Revenue as the name.

View the January worksheet using the custom view All_Services_Revenue.

3. 

Unhide the May worksheet.

4. 

The spa's owner recently decided to change the company name to Wellness Spa & Salon. The spa name appears in

the footers of each worksheet and needs to be removed. Lexie asks you to inspect the workbook and remove the

footer information.

Use the Document Inspector to remove all footer information from the workbook. [Mac Hint: The Document Inspector

is not available on Excel for Mac, so manually remove all footers from the workbook.] Do not remove anything else in

the Document Inspector content list. (Hint: Do not save changes when the dialog box appears.)

5. 

Move the May worksheet so that it appears between the April and June worksheets.

6. 

Lexie asks you to change the entries in the first cell of all of the worksheets to reflect the spa's new name.

Group the January, February, March, April, May, and June worksheets, and then change the text in cell A1 to Wellness

Spa & Salon. (Hint: Do not type the period.)

7. With the worksheets still grouped, add the Sheet Name to the center header section. Switch back to Normal View if

necessary.

8. With the worksheets still grouped, format cell A1 using the Green, Accent 1, Lighter 60% (5th column, 3rd row of the

Theme Colors palette) fill color.

9. 

With the worksheets still grouped, set a 1" custom margin on the Top of the worksheets. Ungroup the worksheets.

10. 

Lexie is considering adding a hyperlink to each worksheet to assist with navigation in the workbook. She asks you to

experiment with this task.

Switch to the January worksheet. Add a hyperlink to the text in cell A1 that links to cell A1 of the February worksheet.

The ScreenTip should read Next Month and the text in the cell should remain "Wellness Spa & Salon".

11. 

Format the hyperlink in cell A1 in the January worksheet using the Calibri font, 18 pt. font size, and Green, Accent 1,

Darker 50% (5th column, 6th row of the Theme Colors palette) font color. Test the link to move to cell A1 of the

February worksheet.

12. 

Lexie informs you that the May revenue figures are final except for the promotions data. She asks you to secure the

revenue figures in the May worksheet while allowing the May promotions data to be changed in the future.

Switch to the May worksheet, and then unlock the range B23:D23.

13. 

Protect the May worksheet so that users do not need a password and they can select both locked and unlocked cells.

14. 

The credit card data for the month of June isn't available yet. Lexie plans to use the workbook in a presentation and

asks you to temporarily hide that worksheet.

Switch to the June worksheet, then hide the June worksheet.

15. Lexie asks for your help in making the workbook easier to identify when searching her computer. You will use

document properties for this purpose.

Add Spa Revenue as a category in the workbook properties.

16. Lexie asks you to protect the workbook structure so that sheets can not be added or deleted.

Protect the workbook structure without using a password.