ABC company is a small computer security contractor that provides computer security analysis, design, and software implementation for the U.S. Government and commercial clients. ABC company competes for both private and U.S. Government computer security work by submitting detailed bids outlining the work the company will perform if awarded the contracts. Because all of the work involved computer security, a highly sensitive area, almost all of ABC company’ tasks require access to classified material or company confidential documents. Consequently, all of the security engineers (simply known as “engineers” within the company), have U.S. government clearances of either Secret or Top Secret. Some have even higher clearances for the 2% of ABC company’ work that involves so called black box security work. Most of the employees also hold clearances because they must handle classified documents.
Leslie is ABC company’ human resources (HR) manager. She maintains all employee records and is responsible for semiannual review reports, payroll processing, personal, records, recruiting data, employee training, and pension option information. At the heart of an HR system, are personnel records. Personnel record maintenance includes activities such as maintaining employee records, tracking cost center data, recording and maintaining pension information, and abscense/sick leave record keeping. Although most of the information resides in sophisticated database systems, Leslie maintains a basic employee worksheet for quick calculations and ad hoc report generation. Because ABC company is a small company, Leslie can take advantage of Excel’s excellent list management capabilities to satisfy many of her personal information management needs.
Leslie is ABC company’ human resources (HR) manager. She maintains all employee records and is responsible for semiannual review reports, payroll processing, personal, records, recruiting data, employee training, and pension option information. At the heart of an HR system, are personnel records. Personnel record maintenance includes activities such as maintaining employee records, tracking cost center data, recording and maintaining pension information, and abscense/sick leave record keeping. Although most of the information resides in sophisticated database systems, Leslie maintains a basic employee worksheet for quick calculations and ad hoc report generation. Because ABC company is a small company, Leslie can take advantage of Excel’s excellent list management capabilities to satisfy many of her personal information management needs.
- Leslie has asked you to assist with a number of functions:
- Create a new worksheet tab and name it Sort.
- Copy the table from the Security Analysis sheet into Sort.
- Use Excel’s Sorting functions (Sort & Filter Custom Filter) to sort the employee list in ascending order by department, then by last name, then by first name. (Each of these will be a new level in the custom sort.). Note: Click “Add Level” when you want to add the next filter.
- Create a new worksheet tab and name it Auto Filter.
- Copy the table from the Security Analysis sheet into Auto Filter.
- Using Excel’s AutoFilter feature, create a custom auto filter that will display all employees whose birth data is greater than or equal to 1/1/1965 and less than or equal to 12/31/1975.
- Create a new worksheet tab and name it Subtotal.
- Copy the table from the Sort sheet into Subtotal.
- Using the subtotal feature, create a sum of the salary for each department. You know you are correct if you end up with 1 subtotal per department.
- Create a new worksheet tab and name it Formatting.
- Copy the table from the Security Analysis sheet into Formatting.
- Using the Salary Column, change the font color to red if the cell value is greater than or equal to 55000. You must use the conditional formatting feature to complete this step.