New Perspectives Excel 365 | Module 5: SAM Project A Cierva Systems
GENERATING REPORTS FROM MULTIPLE WORKBOOKS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_5A_FirstLastName_1.xlsx as NP_EX365_5A_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. To complete this Project, you will also need the following files:
Support_EX365_5A_Budget.xlsx
Support_EX365_5A_Details.docx
3. With the file NP_EX365_5A_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. Santiago Andres is an administrative coordinator for Cierva Systems, a leading manufacturer of aerospace products. He is using an Excel workbook to plan the company's exhibits at industry trade shows and asks you to complete the plan. Go to the Trade Shows worksheet. Santiago asks you to correct one hyperlink and insert others to provide access to external information. In cell D4, edit the hyperlink to use Bahru Exhibition Center as the display text to show the full name of the location.
2. In cell F6, create a hyperlink to an email address as follows:
a. Insert a link to the following email address: lucsorel@example.cengage.com
b. Use lucsorel@example.cengage.com as the text to display.
3. In cell B10, create a hyperlink to a Word document containing descriptions of the trade shows as follows:
a. Link to the file Support_EX365_5A_Details.docx.
b. Use Detailed show descriptions as the text to display.
c. Use Review trade show details as the ScreenTip text.
4. In cell B11, create a hyperlink to another worksheet as follows:
a. Link to cell A1 in the U.S. Shows worksheet of the current workbook.
b. Use Projected expenses as the display text.
5. Go to the Houston worksheet. Santiago wants you to make the following changes in the three trade show expense worksheets, which have the same structure:
a. Group the Houston, Las Vegas, and Orlando worksheets so you can format them at the same time.
b. In cell B2, enter Projected Expenses as the worksheet title.
c. In cell B2, apply the shading color Aqua, Accent 5, Lighter 80% (9th column, 2nd row in the Theme Colors palette).
d. In the range C15:E15, apply the Currency number format with zero decimal places and $ as the symbol.
6. With the Houston, Las Vegas, and Orlando worksheets still grouped, add formulas to the worksheets as follows:
a. In cell C11, enter a formula using the SUM function to calculate the total expenses for Booth 1 (the range C5:C10).
b. Fill the range D11:F11 with the formula in cell C11 to calculate total expenses for Booths 2 and 3 and the total for the location.
c. In cell C15, enter a formula without using a function to divide the total expenses for Booth 1 (cell C11) by the square footage (cell C14).
d. Fill the range D15:E15 with the formula in cell C15 to calculate the cost per square foot for Booths 2 and 3. Ungroup the worksheets and then check to confirm that all three worksheets contain the formatting and formulas from Steps 5 and 6.
7. Santiago asks you to create a copy of the formatted Orlando worksheet as follows to use for the projected expenses of attending the Paris trade show:
a. Create a copy of the Orlando worksheet and use Paris as the name of the new worksheet.
b. Move the Paris worksheet so it appears between the Orlando and U.S. Shows worksheets.
c. In cell B3, edit the text to read Paris as the trade show location.
d. Clear the contents of the range C5:E10.
8. Go to the U.S. Shows worksheet. Santiago wants you to consolidate the data from each of the U.S. trade shows as follows:
a. In cell B5, enter a formula without using a function that references cell B5 in the Houston worksheet.
b. Fill the range B6:B10 with the formula in cell B5, filling without formatting.
c. In cell C5, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C5 in the Houston:Orlando worksheets.
d. Fill the range C6:C10 with the formula in cell C5, filling without formatting.
e. Fill the range D5:E10 with the formulas and the formatting in the range C5:C10.
9. Santiago has a workbook containing total budget amounts for each trade show expense. He asks you to add the budget data to the range G5:G10 by creating external references as follows:
a. Open the file Support_EX365_5A_Budget.xlsx, and then go to the U.S. Shows worksheet in the project workbook.
b. Link cell G5 in the U.S. Shows worksheet to cell G5 in the Trade Show Budget worksheet in the Support_EX365_5A_Budget.xlsx workbook.
c. Link each cell in the range G6:G10 in the U.S. Shows worksheet to the corresponding cell in the Trade Show Budget worksheet in the Support_EX365_5A_Budget.xlsx workbook.
d. Close the Support_EX365_5A_Budget.xlsx workbook.
10. Santiago started to create named ranges in the worksheet and asks you to complete the work. Create a defined name for the range C5:E5 using Exhibits as the range name.
11. Based on the range B6:E10, create names for the range C6:E10 using the values shown in the left column.
12. Apply the defined names Booth_1_Total, Booth_2_Total, and Booth_3_Total to the formulas in the range C11:E11.
13. In cell F11, edit the defined name to use Total_Expenses as the range name, which is a more descriptive name.
14. In cell F15, enter a formula without using a function that divides the total expenses (the defined range Total_Expenses) by the total square footage (cell F14). (Hint: If an error symbol appears with the message "The formula in this cell differs from the formulas in this area of the spreadsheet.", ignore it.)
15. Break the external link to the Support_EX365_5A_Budget.xlsx workbook to replace the formulas in the range G5:G10 of the U.S. Shows worksheet with static values.
Your workbook should look like the Final Figures on the following pages. When opening your Graded Summary Report, you may be prompted to update the links. Select Don't Update in the dialog box. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
Final Figure 1: Trade Shows Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Houston Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: Las Vegas Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 4: Orlando Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 5: Paris Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 6: U.S. Shows Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.