New Perspectives Excel 365 | Module 5: End of Module Project 2 Premium Skills Workshops
GENERATING REPORTS FROM MULTIPLE WORKBOOKS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_EOM5-2_FirstLastName_1.xlsx as NP_EX365_EOM5-2_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_EOM5-2_Projections_2029.xlsx
3. With the file NP_EX365_EOM5-2_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. Jocelyn Ng is the content developer for Skills for Success, a company that produces business skills workshops for entrepreneurs and small business owners in the Pacific Northwest. The company is studying whether to launch a new workshop and needs an overview of workshop expenses. Jocelyn is using an Excel workbook with multiple worksheets to summarize four years of these expenses. She asks for your help in completing the expense information. Switch to the Financing worksheet. Break the external link in the worksheet so that the formulas in the range C15:F17 of the Financing worksheet are replaced with static values.
2. Jocelyn wants to apply consistent formatting to the worksheets she collected from separate workbooks. Group the Financing, Marketing, and Soft Skills worksheets together. In the grouped worksheets, change the font size in the merged range B2:G2 to 24 point. Bold the values in the range B6:B9. Resize the column width of columns C:G to 13. Do not ungroup the worksheets.
3. With the worksheets still grouped, change the text in cell B2 to read: Premium Skills Workshops In cell B9, change the text "Supplies" to read: Materials Do not ungroup the worksheets.
4. With the worksheets still grouped, enter a formula in cell C10 using the SUM function that totals the expenses for 2025. Copy the formula to the range D10:F10. Ungroup the worksheets and then check to confirm that the formatting and formulas from Steps 2-3 are present in all three worksheets.
5. Jocelyn wants to create a copy of the formatted Marketing worksheet to use for expense data for a workshop on strategic planning the company is considering adding. Create a copy of the Marketing worksheet between the Soft Skills worksheet and the Combined Expenses worksheet, and then change the worksheet name to Strategic Planning for the copied worksheet. Edit the text to read Strategic Planning in the merged range B3:G3. Clear the contents of the range C6:F9. (Hint: Ignore errors because Jocelyn will enter the expense data later.)
6. Switch to the Combined Expenses worksheet. Jocelyn wants to combine the expense data from each of the current workshops. In cell B6, enter a formula without using a function that references cell B6 in the Strategic Planning worksheet. Copy the formula from cell B6 to the range B7:B9 without copying the formatting.
7. Jocelyn started to create named ranges in the worksheet and has asked you to complete the work. Create a defined name for the range C10:F10 using Expenses as the range name.
8. Create names for the ranges C6:F6, C7:F7, C8:F8, and C9:F9 using the labels shown in column B.
9. Apply the defined names Expenses_2025, Expenses_2026, Expenses_2027, and Expenses_2028 to the formulas in the range C10:F10.
10. Change the defined name from _2029 to Expenses_2029 for the range H6:H9.
11. Delete the defined name Total_Expenses (cell H10) because it will not be used in formulas.
12. Jocelyn wants to compare 2028 expense totals to the projected expense totals for 2029 and needs to add the 2029 data to the Combined Expenses worksheet. Open the file Support_EX365_EOM5-2_Projections_2029.xlsx. Switch back to the original workbook and go to the Combined Expenses worksheet. Create an external reference by linking cell H6 in the Combined Expenses worksheet to cell C6 in the Projected Expenses worksheet in the Support_EX365_EOM5-2_Projections_2029.xlsx workbook. Link each cell in the range H7:H9 in the Combined Expenses worksheet to the corresponding cell in the Support_EX365_EOM5-2_Projections_2029.xlsx workbook. Do not break the links. Close the Support_EX365_EOM5-2_Projections_2029.xlsx workbook.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
Final Figure 1: Financing
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: Marketing
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: Soft Skills
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: Strategic Planning
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: Combined Expenses
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.