SecureWorks Inc. is a small computer security contractor that provides computer security analysis, design, and software implementation for the U. S. government and commercial clients. SecureWorks competes for both private and U. S. government computer security contract work by submitting detailed bids outlining the work the company will perform if awarded the contracts. Because all of the work involves computer security, a highly sensitive area, almost all of SecureWorks tasks require access to classified material or company confidential documents. Consequently, all of the security engineerssimply 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 percent of SecureWorks work that involves so-called black box security work. Most of the employees also hold clearances because they must handle classified documents. Leslie Mamalis is SecureWorks human resources (HR) manager. She maintains all employee records and is responsible for semiannual review reports, payroll processing, personnel 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 absence and sick leave record keeping. While most of this information resides in sophisticated database systems, Leslie maintains a basic employee worksheet for quick calculations and ad hoc report generation. Because SecureWorks is a small company, Leslie can take advantage of Excels excellent list management capabilities to satisfy many of her personnel information management needs.

1. Copy the worksheet Data to a new worksheet called Sort. Sort the employee list in ascending order by department, then by last name, then by first name. 2. Copy the worksheet Data to a new worksheet called Autofilter. Using the Autofilter feature, create a custom filter that will display employees whose birth date is greater than or equal to 1/1/1965 and less than or equal to 12/31/1975. 3. Copy the worksheet Data to a new worksheet called Subtotal. Using the subtotal feature create a sum of the salary for each department. 4. Copy the worksheet Data to a new worksheet called 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.

need the finished excel document

ID First Name Last Name Department 1009 Kevin 1016 Oscar Gomez 1032 Hillary Flintsteel 1042 aria Andretti 1119 Sharad Manispour 1173 Roberta Kurzweil 1355 |Barbara Grabowski 1441 James Van Horn 1614 Artie 1851 Melinda English 2002 Edgar 2042 Hillar 2105 Luca 2122 Yuan 2344 Nemesha Mehta 2451 Francine |Detweiler 2678 Koshi 2733 John 2768 Phyllis Leonard 2896 Samuel Gates 2956 Michael Goldstein 3015 Patti 3043 |Joseph Paterno 3117 Frederich Bednarczyk Engineering Security Engineer 56,700.00 05/14/99 05/27/7O 3210 Carmen Ortega-Molina Engineering Security Engineer 46,000.00 09/16/99 03/28/52F 3314 Svetlana Kartashev 4006 Alice Ravilk Title Hire Date Birth Date Gender Clearance Salary $ 38,900.00 12/24/8903/04/61M $ 43,500.00 02/16/9004/29/67M $ 34,500.00 03/21/9008/22/58F $ 42,500.00 03/21/9008/20/70F Grundies Marketin Marketin l-larketing Staff Marketin Engineering Security Engineer 45.600.00 10/13/90 02/04/69M Management Manager Management Manager Management Manager Marketin Management Manager Engineering Security Engineer 53,300.00 02/08/94 03/21/70 M Management Staff Marketin Engineering Security Engineer 46,540.00 09/05/95 03/04/64 M Engineering Security Engineer 65.000.00 10/01/95 02/12/65 F Engineering Security Engineer 56,700.00 10/10/95 04/29/70 F Engineering Security Engineer 49,600.00 11/16/95 01/24/63F Engineering IStaff Engineering Security Engineer 59,800.00 12/13/96 07/02/70 F Engineering Security Engineer 66.300.00 01/28/97 04/10/74 M Engineering Security Engineer 51.000.00 04/11/97 03/07/63 M Engineering Security Engineer 64,300.00 07/06/98 03/10/66 F Marketin CSR CSR CSR $ 63,000.00 06/16/9212/22/58F $ 75,700.00 10/13/92 04/10/65 F $ 66,500.00 12/18/92 02/03/58M $ 41,000.00 05/16/93 07/13/70M $ 56,400.00 10/01/93 02/14/56F Lambros CSR TS RothrackE Cushner Pacioli Chan $ 32,000.00 08/26/94 05/06/61F $ 42,300.00 08/26/95 05/06/70M TS Staff TS Yamamoto Zumkowski $ 33,000.00 04/18/9612/12/72M TS TS TS Stonesifer Staff $ 32,000.00 07/06/98 03/10/68M Human Resoul Staff Human ResoulManager $ 38,000.00 11/05/01 05/03/82 F $ 43,000.00 12/01/01 01/26/55 M ID First Name Last Name Department 1009 Kevin 1016 Oscar Gomez 1032 Hillary Flintsteel 1042 aria Andretti 1119 Sharad Manispour 1173 Roberta Kurzweil 1355 |Barbara Grabowski 1441 James Van Horn 1614 Artie 1851 Melinda English 2002 Edgar 2042 Hillar 2105 Luca 2122 Yuan 2344 Nemesha Mehta 2451 Francine |Detweiler 2678 Koshi 2733 John 2768 Phyllis Leonard 2896 Samuel Gates 2956 Michael Goldstein 3015 Patti 3043 |Joseph Paterno 3117 Frederich Bednarczyk Engineering Security Engineer 56,700.00 05/14/99 05/27/7O 3210 Carmen Ortega-Molina Engineering Security Engineer 46,000.00 09/16/99 03/28/52F 3314 Svetlana Kartashev 4006 Alice Ravilk Title Hire Date Birth Date Gender Clearance Salary $ 38,900.00 12/24/8903/04/61M $ 43,500.00 02/16/9004/29/67M $ 34,500.00 03/21/9008/22/58F $ 42,500.00 03/21/9008/20/70F Grundies Marketin Marketin l-larketing Staff Marketin Engineering Security Engineer 45.600.00 10/13/90 02/04/69M Management Manager Management Manager Management Manager Marketin Management Manager Engineering Security Engineer 53,300.00 02/08/94 03/21/70 M Management Staff Marketin Engineering Security Engineer 46,540.00 09/05/95 03/04/64 M Engineering Security Engineer 65.000.00 10/01/95 02/12/65 F Engineering Security Engineer 56,700.00 10/10/95 04/29/70 F Engineering Security Engineer 49,600.00 11/16/95 01/24/63F Engineering IStaff Engineering Security Engineer 59,800.00 12/13/96 07/02/70 F Engineering Security Engineer 66.300.00 01/28/97 04/10/74 M Engineering Security Engineer 51.000.00 04/11/97 03/07/63 M Engineering Security Engineer 64,300.00 07/06/98 03/10/66 F Marketin CSR CSR CSR $ 63,000.00 06/16/9212/22/58F $ 75,700.00 10/13/92 04/10/65 F $ 66,500.00 12/18/92 02/03/58M $ 41,000.00 05/16/93 07/13/70M $ 56,400.00 10/01/93 02/14/56F Lambros CSR TS RothrackE Cushner Pacioli Chan $ 32,000.00 08/26/94 05/06/61F $ 42,300.00 08/26/95 05/06/70M TS Staff TS Yamamoto Zumkowski $ 33,000.00 04/18/9612/12/72M TS TS TS Stonesifer Staff $ 32,000.00 07/06/98 03/10/68M Human Resoul Staff Human ResoulManager $ 38,000.00 11/05/01 05/03/82 F $ 43,000.00 12/01/01 01/26/55 M