3. Filtering SecureIT Data SecureIT, Inc., is a small computer security contractor that provides computer security analysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security work. While most of the personnel information for SecureIT resides in database systems, a basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excel’s excellent list management facilities to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, T3_Employee_Data.xls, to assist SecureIT with producing several worksheet summaries. Here is what is needed: 1. One worksheet that is sorted by last name and hire data. 2. One worksheet that uses a custom sort by department in this order: Marketing, Human Resources, Management, and Engineering. 3. One worksheet that uses a filter to display only those employees in the Engineering department with a clearance of Top Secret (TS). 4. One worksheet that uses a custom filter to display only those employees born between 1960 and 1969 (inclusive). 5. One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first.
3. Filtering SecureIT Data
SecureIT, Inc., is a small
analysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security
work. While most of the personnel information for SecureIT resides in
basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excel’s excellent list management facilities to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, T3_Employee_Data.xls, to assist SecureIT with producing several worksheet summaries. Here is what is needed:
1. One worksheet that is sorted by last name and hire data.
2. One worksheet that uses a custom sort by department in this order: Marketing, Human
Resources, Management, and Engineering.
3. One worksheet that uses a filter to display only those employees in the Engineering
department with a clearance of Top Secret (TS).
4. One worksheet that uses a custom filter to display only those employees born between
1960 and 1969 (inclusive).
5. One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first.


To complete the tasks, you can follow these steps:
- Open the T3_Employee_Data.xls file in Excel.
- Select the first worksheet, and then click on the "Data" tab in the top menu.
- Click on the "Sort A to Z" button to sort by last name, and then click on the "Add Level" button to add a sort by hire date.
- Select the second worksheet, and then click on the "Data" tab in the top menu.
- Click on the "Sort" button, and then select "Custom Sort" from the drop-down menu.
- In the "Sort" dialog box, under "Column," select the "Department" column.
- In the "Order" column, enter "Marketing" in the first row, "Human Resources" in the second row, "Management" in the third row, and "Engineering" in the fourth row.
- Click "OK" to apply the custom sort.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps









