New Perspectives Excel 365 | Module 6: End of Module Project 1 Sunset Beach Inn

GETTING STARTED

1. Save the file NP_EX365_EOM6-1_FirstLastName_1.xlsx as NP_EX365_EOM6-1_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_EOM6-1_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. Sonia Barrera is a human resources manager for Sunset Beach Inn, a popular beachside hotel in Santa Barbara, California. The hotel recently completed its yearly performance reviews, rating each employee on a scale from 1 (unsatisfactory performance) to 5 (excellent performance). Sonia is organizing information on the performance review results and current yearly salaries in an Excel workbook. She asks for your help in updating and analyzing the data. Switch to the Employees worksheet. Unfreeze the top row of the worksheet.

2. Sonia wants to sort and filter the employee data. Format the range A4:G52 as a table with headers using Orange, Table Style Medium 2. Use Employees as the name of the table.

3. Apply the First Column table style option to separate the Employee # values from the rest of the data. Resize column A to its best fit.

4. A new employee started three months ago and just had her first performance review, so Sonia wants to include her data with the other employees. Insert the record shown in Table 1 to the end of the Employees table above row 53.

Table 1: New Record for the Employees Table

Employee # | First | Last | Department | Start Year | Pay Category | Yearly Review e330625 | Natalia | Petrova | Front of House | 2027 | 3 | 5

5. Sonia wants to quickly identify the employees who have been working at the hotel the longest. Sort the data in the Employees table first in ascending order by the Start Year field and then in ascending order by the Last field.

6. Sonia knows the Employees table contains a duplicate record. In the range A5:A53, create a conditional formatting Highlight Cells Rule that identifies duplicate cell values by formatting them with Light Red Fill with Dark Red Text. Delete the second instance of the duplicate record, the one with a Start Year of 2027.

7. The conditional formatting rule in column G highlights cells that contain the value 5, the highest performance review rating. Sonia wants to change the highlighting to use colors that are associated with positive values. Edit the conditional formatting Highlight Cells Rule for the range G5:G52 to highlight the cells containing values equal to 5 with a font color of Green, Accent 3, Darker 50% (7th column, 6th row in the Theme Colors palette) and a fill color of Green, Accent 3, Lighter 80% (7th column, 2nd row in the Theme Colors palette).

8. Sonia wants to list the number of years each employee has been working for Sunset Beach Inn. Insert a column to the right of the Yearly Review column. Use Years as the column heading. In cell H5, insert a formula without a function that uses a structured reference to subtract the Start Year from 2027, the current year. If Excel does not automatically copy the formula to the other cells in column H, fill the range H6:H52 with the formula in cell H5. Clear the conditional formatting rule from the range H5:H52 if Excel applies it.

9. Sonia asks you to make it easy to filter the table based on the employee's department and starting year. Insert two slicers, one based on the Department field and the other based on the Start Year field.

10. Resize both slicers to a height of 2.2" and a width of 1.75". Move the Department slicer so its upper-left corner is in cell I4 and its lower-right corner is in cell I14. Move the Start Year slicer so its upper-left corner is in cell J4 and its lower-right corner is in cell J14.

11. Use the slicers to filter the Employees table to show employees who started in 2026 or 2027 with specialties in the Front of House and Housekeeping departments.

12. Since Sonia is using slicers to filter the data, hide the filter buttons in the Employees table.

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: Employees Worksheet