New Perspectives Excel 365 | Module 2: SAM Project A Sharma Cybersecurity Consulting

GETTING STARTED

1. Save the file NP_EX365_2A_FirstLastName_1.xlsx as NP_EX365_2A_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_2A_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. Yong Choe is the business manager at Sharma Cybersecurity Consulting. She asks you to format the workbook she uses to analyze monthly sales to make the data easier to read and use. On the Sales Analysis worksheet, change the theme of the workbook to the Office 2013-2022 Theme to use the standard theme for the company.

2. Start by formatting the headings on the Sales Analysis worksheet to make them more prominent as follows:

a. Merge and Center the range A1:G1.

b. Apply the Heading 1 style to the range A1:G1.

c. Merge and Center the range A2:G2.

d. Change the font color of the merged range A2:G2 to Blue-Gray, Text 2 (4th column, 1st row of the Theme Colors palette) to match the font color of the worksheet title.

e. Change the font of the merged ranges A1:G1 and A2:G2 to Californian FB to apply the font used in other company reports.

3. Next, Yong needs you to format the report date and average sales information to separate it from the other sales data as follows:

a. Change the background color of the range A4:E4 to Gold, Accent 4, Lighter 80% (8th column, 2nd row of the Theme Colors palette).

b. Format cells A4, C4, and A12 as bold to emphasize the text.

c. Format cell B4 to use a date format with a two-digit month, a two-digit day, and a two-digit year to use the standard date format for company reports.

4. Yong also wants you to format the table headings to clarify they are headings as follows.

a. Format the ranges A6:E6 and A13:G13 as italic.

b. Apply horizontal centering to the contents of each cell in the ranges B6:E6 and B13:G13 to improve their appearance.

c. Change the background color of the  ranges A6:E6 and A13:G13 to Blue, Accent 1, Lighter 60% (5th column, 3rd row of the Theme Colors palette) to format the ranges as column headings.

d. Change the background color of the  ranges A7:A10 and A14:A23 to Blue, Accent 1, Lighter 80% (5th column, 2nd row of the Theme Colors palette) to format the ranges as row headings.

5. Yong wants the total rows in each table to stand out. She asks you to apply the Total cell style to the ranges A10:E10 and B23:G23.

6. The row headings in the Sales to date table need to be formatted so it is clear they each apply to three rows of data. Format the row headings as follows:

a. Merge and Center the range A14:A16 and then center the contents of the merged cell vertically.

b. Use the Format Painter to apply the formats in the range A14:A16 to the ranges A17:A19 and A20:A22

c. For the merged ranges A14:A22, rotate the cell contents to 90 degrees.

7. Yong also wants you to format the sales numbers so they are easier to decipher. Format the numbers as follows:

a. In the ranges B7:D10 and C14:G23, apply the Accounting number format with zero decimal places. [Mac Hint: After completing this step, reperform step 5 on the ranges A10:E10 and B23:G23]

b. In the range E7:E10, apply the Percentage number format with one decimal place.

8. Yong asks you to identify the services that generated less than $40,000 after four months. Use the Highlight Cells Rules conditional formatting to format cells in the range G14:G22 with a value less than 40,000 using Light Red Fill with Dark Red Text.

9. Yong also needs you to calculate the average sales for the month. In cell E4, create a formula using the AVERAGE function with the values in the range B7:B9 to determine the average sales for the current month.

10. Increase the indent of the contents of cell C4 to separate the text from the date in cell B4.

11. Find and replace all instances of the word "Test" with Testing to correct the name of the category.

12. Finally, Yong needs you to format the worksheet for printing. Make the following changes:

a. Insert a page break to start a new page at row 12.

b. Set the margins to Narrow.

c. Set rows 1 and 2 as print titles.

d. Create a custom footer for the worksheet. In the left footer section, display the current page number using a Header and Footer element. In the center footer section, display the Sheet Name using a Header and Footer element.

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: Sales Analysis Worksheet