New Perspectives Excel 2019 | Module 2: End of Module Project 2 Shingle Rock College
FORMAT WORKBOOK TEXT AND DATA
· Excel Projects Help
GETTING STARTED
1. Open the file NP_EX19_EOM2-2_FirstLastName_1.xlsx, available for download from the SAM website.
2. Save the file as NP_EX19_EOM2-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
a. If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
3. With the file NP_EX19_EOM2-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
a. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
1. Sid Kellerman works in the Office of Admissions at Shingle Rock College. Sid is adding formatting to the workbook he uses to track applications for the upcoming academic year to make the data easier to read and print. Switch to the Applications worksheet, then format the headings. Merge and Center the range A1:F1.
2. For the range A1:F1, apply the Accent 6 cell style, then bold the contents and change the font size to 18 point.
3. Merge and center the range A2:F2, apply the 40% - Accent 6 cell style, then change the font size to 12 point. [Mac Hint: The font size may automatically be changed to 12 points.]
4. For the range A4:A5, change the alignment to right.
5. Unmerge the range B4:C4.
6. Format the date and application total. For the range A4:B5, apply the 40% - Accent 5 cell style, then change the font size to 12 point.
7. For the range B4:B5, bold the cell contents.
8. For cell B4, apply the Long Date number format (e.g., Friday, July 16, 2021).
9. Find and replace all instances of the text "aplications" with Applications (Hint: Do not include the quotation marks. You should find and replace three instances.).
10. Format the application table by clearing the formatting from the range A6:F6.
11. For the range B8:B60, reduce the number of decimal places to 0.
12. For the range C8:C60, apply the Percentage number format with 2 decimal places, then copy the format to the range D8:E60.
13. For the range E8:E60, add conditional formatting using the Highlight Cells Rules, specifying a Less Than rule that formats a cell with Light Red Fill when cell contents are less than -0.01.
14. For the range E8:E60, add additional conditional formatting using the Highlight Cells Rules, specifying a Greater Than rule that formats a cell with Yellow Fill with Dark Yellow Text when cell contents are greater than 0.02.
15. For the range F8:F60, apply the Accounting number format with 2 decimal places and $ as the symbol.
16. For the range B60:C60, apply the Total cell style, then apply the style to cell F60.
17. Format the Applications worksheet for printing. Select row 7 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.5 inch. Change the print area to the range A1:F60.
18. On the left side of the footer, add the text Class of 2025 Applications. On the right side of the footer, use Header & Footer elements to insert the current page number, a space, the word of, another space, and the total number of pages.
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 SAM website to submit your completed project.
Final Figure 1: Applications Worksheet - Page 1
Final Figure 2: Applications Worksheet - Page 2