T3 Plug-in Consequently, all of the security personnel have clearances of either
ID: 3751080 • Letter: T
Question
T3 Plug-in
Consequently, all of the security personnel have clearances of either Secret or Top Se- cret. 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 genera- tion. 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:
One worksheet that is sorted by last name and hire data.
One worksheet that uses a custom sort by department in this order: Marketing, Human
Resources, Management, and Engineering.
One worksheet that uses a filter to display only those employees in the Engineering
department with a clearance of Top Secret (TS).
One worksheet that uses a custom filter to display only those employees born between
1960 and 1969 (inclusive).
One worksheet that totals the salaries by department and the grand total of all depart-
ment salaries. This worksheet should be sorted by department name first.
ID First Name Last Name Department Title Salary Hire Date Birth Date Gender Clearance 2956 Michael Goldstein Engineering Engineer 51,000 4/11/97 3/7/63 M TS 4123 Steve Ballmer Engineering Engineer 42,900 5/1/02 4/14/70 M N 3117 Frederich Bednarczyk Engineering Engineer 56,700 5/14/99 5/27/70 M S 2122 Yuan Chang Engineering Engineer 46,540 9/5/95 3/4/64 M TS 2451 Francine Detweiler Engineering Engineer 56,700 10/10/95 4/29/70 F S 2896 Samuel Gates Engineering Engineer 66,300 1/28/97 4/10/74 M S 2768 Phyllis Leonard Engineering Engineer 59,800 12/13/96 7/2/70 F TS 1119 Sharad Manispour Engineering Manager 54,500 10/13/90 2/4/69 M S 2344 Nemesha Mehta Engineering Engineer 65,000 10/1/95 2/12/65 F S 3210 Carmen Ortega-Molina Engineering Engineer 46,000 9/16/99 3/28/52 F S 2002 Edgar Rothrock Engineering Engineer 53,300 2/8/94 3/21/70 M S 3015 Patti Stonesifer Engineering Engineer 64,300 7/6/98 3/10/66 F S 2678 Koshi Yamamoto Engineering Engineer 49,600 11/16/95 1/24/63 F S 2733 John Zumkowski Engineering Staff 33,000 4/18/96 12/12/72 M TS 3314 Svetlana Kartashev Human Resources Staff 38,000 11/5/01 5/3/82 F N 4006 Alice Rovik Human Resources Manager 43,000 12/1/01 1/26/55 M S 2042 Hillary Cushner Management Staff 32,000 8/26/94 5/6/61 F TS 1851 Melinda English Management Manager 62,040 10/1/93 2/14/56 F TS 1355 Barbara Grabowski Management Manager 75,700 10/13/92 4/10/65 F C 1173 Roberta Kurzweil Management Manager 63,000 6/16/92 12/22/58 F S 1441 James Van Horn Management Manager 66,500 12/18/92 2/3/58 M S 1042 Maria Andretti Marketing CSR 42,500 3/21/90 8/20/70 F N 1032 Hillary Flintsteel Marketing Staff 34,500 3/21/90 8/22/58 F N 1009 Kevin Grundies Marketing CSR 38,900 12/24/89 3/4/61 M C 1614 Artie Lambros Marketing CSR 41,000 5/16/93 7/13/70 M N 2105 Luca Pacioli Marketing Staff 42,300 8/26/95 5/6/50 M S 1016 Oscar Gomez Marketing CSR 43,500 2/16/90 4/29/67 M NExplanation / Answer
Note: Excel 2016 is used in this case.
Answer:
To sort the data by last name and hire date follow the listed steps:
Step 1: Select all data.
Step 2: In Home tab, click on “Sort & Filter”, then click on option “Custom sort”.
Step 3: A sort dialog box will appear.
Step 4: Select “Last Name” in “Column” attribute from the drop-down menu. Select the “Order” also.
Step 5: Click on “+” from bottom left in the dialog box. Select “Hire Date” in “Column” attribute from the drop-down menu. Select the “Order” also.
Then Press “OK”.
The desired list will appear.
Answer:
To apply custom sort in the given order, first, you have to create a custom list. To do so in Excel 2016 for Mac, first click on excel menu, then preferences.
A dialog box, “Excel Preferences” will appear.
Click on “custom list”. A “Custom List” dialog box will appear.
Click on the left pane “List entries”. Type the entries as “Marketing, Human
Resources, Management, Engineering”. Then press “Add”. The list will be added in the custom list.
To custom sort the data by Department follow the listed steps:
Step 1: Select all data.
Step 2: In Home tab, click on “Sort & Filter”, then click on option “Custom sort”.
Step 3: A sort dialog box will appear.
Step 4: Select “Department” in “Column” attribute from the drop-down menu. Select the “Order” also. In “Order” drop-down menu, click on “Custom List”. A custom list will appear.
Select “Marketing….” Press OK.
The desired list will appear.
To apply filter, select whole range of data and from “Home” tab, click on “Filter”. “Filter” can also be selected from “Data” tab.
Then click on filter in “Department” column. A dialog box will appear.
First unselect all, then select “Engineering”.
Similarly, do the same for “Clearance” column to select “TS”. The desired list will appear as follows:
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.