IL_EX19_6a_MackPaine_Report_1

Illustrated Excel 2019 | Module 6: SAM Project 1a Mack Paine GE ver. 16.0.0-rc0000 1. 5/5 Unprotect a worksheet. 2. 5/5 Create a custom worksheet view. 3. 5/5 Apply a custom view to a worksheet. 4. 5/5 Unfreeze rows and columns. 5. 5/5 Enter text in a cell. 6. 5/5 Change the fill color of cells. 7. 5/5 Set custom margins for a worksheet. 8. 4/5 Enter text in a cell. Format text as a hyperlink to a worksheet cell. Set the ScreenTip for a hyperlink. Format text as a hyperlink. Hyperlink to an email address. 9. 5/5 Remove a hyperlink. 10. 3/5 Unlock a range of cells. In the Quarter 2 worksheet, the range B22:D25 should be unlocked. Protect a worksheet. 11. 5/5 Insert a page break in the worksheet. 12. 5/5 Enter text in a cell. Create a link to an external workbook. 13. 5/5 Hide a worksheet. 14. 5/5 View data in an outline. 15. 0/6 Insert a subtotal row in a range. The Current Month worksheet should display subtotals for each sales type. Set options for subtotaling data. The Current Month worksheet should display subtotals for each sales type below the data. 16. 6/6 Set the margins of a worksheet. 17. 6/6 Modify a document property. 18. 6/6 Create a compatibility report. 19. 6/6 Protect a workbook. Mark a workbook as final. SUBMISSION #1 | SCORE IS: 91 OUT OF 100 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. 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. Display the Quarter 1 worksheet using the custom view Q1_All_Categories , which Kendra already created, to redisplay all of the data. 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. 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. 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. 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. 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. In the Quarter 1 worksheet, the hyperlink in cell A28 should link to the web address www.musicbot.example.com . Use the hyperlink in cell A27 to go to the Quarter 2 worksheet. In cell A3, remove the hyperlink, which Kendra no longer needs. 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. 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. 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. Hide the Quarter 3 worksheet so that no one can view the unverified data. 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. 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. To prepare for printing the Current Month worksheet, change the right margin to 2.25" . Add Quarterly Revenue in the Categories document property so that Kendra can find the workbook easily when she is searching for files. 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. 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.)

MusicBot MANAGE WORKBOOK DATA Author: Mack Paine Illustrated Excel 2019 | Module 6: SAM Project 1a Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy the file from the SAM website.

y of