Min Jee Woo Artwork | Excel SAM Project 1b - Module 5 | New Perspectives 2021/365 | SC EX365 2021 5b

Contact Us
Whatsapp No : +994-401583314

New Perspectives Excel 365/2021 | Module 5: SAM Project 1b

Min Jee Woo Artwork
GENERATING REPORTS FROM MULTIPLE WORKBOOKS

GETTING STARTED
• Open the file NP_EX365_2021_5b_FirstLastName_1.xlsx, available for download.
• Save the file NP_EX365_2021_5b_FirstLastName_1.xlsx as NP_EX365_2021_5b_FirstLastName_2.xlsx
o Edit the file name by changing “1” to “2”.
o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
• To complete this Project, you will also need the following files:
o Support_EX365_2021_5b_Combined_2023.xlsx
o Support_EX365_2021_5b_Descriptions.docx
• With the file NP_EX365_2021_5b_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
o If cell B6 does not display your name, delete the file and download a new copy.
• When opening your start file or the Graded Summary Report, you may be prompted to update the links. Select Don't Update in the dialog box.

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_EX365_2021_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 2024 in the merged range A2:F2.
c. Clear the contents of the range B5:E8.