New Perspectives Excel 365 | Module 2: End of Module Project 2 AllGoods Home Improvement
FORMAT WORKBOOK TEXT AND DATA
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_EOM2-2_FirstLastName_1.xlsx as NP_EX365_EOM2-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. With the file NP_EX365_EOM2-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. Romina Castillo is a financial analyst for AllGoods Home Improvement, a national chain of home improvement and supply stores headquartered in Racine, Wisconsin. She is analyzing the stores that may need to be upgraded or replaced as they age. She asks you to add formatting to the workbook to make the data easier to read and print. In the Stores worksheet, merge and center the range A1:G1 to begin formatting the worksheet title.
2. For the range A1:G1, apply the Accent5 cell style, then change the font to Verdana, the font size to 20 point, and the font color to Black, Text 1.
3. Use the Format Painter to apply the formatting in the range A1:G1 to the range A2:G2. Reduce the font size to 14 point for the range A2:G2 to format it as a subtitle.
4. For the range A4:A6, change the horizontal alignment to right and the vertical alignment to middle. Bold the text, change the font color to Teal, Accent 4, Darker 50%, and then apply the Aqua, Accent 2, Lighter 80% fill color.
5. Unmerge the range B4:C4, and then apply the Short Date number format to cell B4 to display the complete date in the cell.
6. Copy only the formatting from the range A4:A6 to the range F4:F6.
7. Clear the formats from the range A7:G7 to include a row of white space between the summary statistics and the table of data.
8. Find and replace all instances of the text "Repairs", using Maintenance as the replacement text. (Hint: Do not include the quotation marks. You should find and replace two instances.)
9. Reduce the number of decimal places to 0 in the range B9:B61 since the number of stores is always a whole number.
10. Increase the number of decimal places to 2 in the range C9:C60 to display more precise data.
11. Apply the Percentage number format with 2 decimal places to the range D9:D60, and then copy the format to the range E9:F60 to use the same formatting for all percentages.
12. Apply conditional formatting to the range F9:F60 using the Highlight Cells Rules, specifying a Less Than rule that formats a cell with Green Fill with Dark Green Text when cell contents are less than 2%.
13. Add conditional formatting to the range F9:F60 using the Top/Bottom Rules, specifying that the Top 10 Items have a Light Red Fill with Dark Red Text.
14. Apply the Accounting number format with 2 decimal places and $ as the symbol in the range G9:G61 to clarify the values are dollar amounts.
15. In cell G4, enter a formula using the AVERAGE function that averages the age of the stores in years (the range C9:C60).
16. Format the Stores worksheet for printing by selecting row 8 as the print titles to be repeated on each page. Change the top and bottom margins to 0.8 inch, and the left and right margins to 0.25 inch. Change the print area to the range A1:G61.
17. On the left side of the footer, add the text AllGoods Stores. On the right side of the footer, use Header & Footer elements to insert the current page number.
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: Stores Worksheet - Page 1
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: Stores Worksheet - Page 2
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.