Instructions:

Open up the T$3\_$Employee$\_$Data spreadsheet file that I have posted here. Save this spreadsheet on your computer under the following name:

firstname$\_$lastname$\_$TA$2($where firstname is your first name & lastname is your last name$).$

Turn to problem #$3("$Filtering SecurelT data"$)$ on pages $14$ & $15$ of plug$-$in module T$3.$

First do part $1.$ Do this by copying the data from the source worksheet $("$employee data"$)$ into a new worksheet that you will label "Sort". Perform the

required data sorting operation on the list as specified in part $1$ of this exercise $($i$.$e$.$ sorted by last name and hire date$).$ After you have done this, rename

the worksheet "Sort".

Next do part $2.$ Do this by copying the data from the source worksheet $("$employee data"$)$ into a new worksheet that you will label "custom sort". Perform

the required data sorting operation on the list as specified in part $2$ of this exercise $($i$.$e$.$ using the custom list sorting parameter of Marketing, Human

Resources, Management, and Engineering$).$ After you have done this, rename the worksheet "Custom Sort".

Next do part $3.$ Do this by copying the data from the source worksheet $("$employee data"$)$ into a new worksheet that you will label "filter". Perform the

required filtering operation on the list as specified in part $3$ of this exercise $($i$.$e$.$ using a filter to display only those employees in the Engineering

department with a clearance of Top Secret$).$ After you have done this, rename the worksheet "Filter".

Next do part $4.$ Do this by copying the data from the source worksheet $("$employee data"$)$ into a new worksheet that you will label "custom filter". Perform

the required filtering operation on the list as specified in part $3$ of this exercise $($i$.$e$.$ using a custom display only those employees born between $1960$ &

$1969,$ inclusive $[$HINT: Think of $>=$ and as well as when the dates when a year begins & ends$]).$ After you have done this, rename the worksheet

"Custom Filter".

Next do part $5.$ You will create a pivot table which totals salaries by department. When creating this Pivot table, choose the option to insert the pivot

table into into a new worksheet. After you have created an appropriate pivot table, name the worksheet "SumSalary".

PART $6($ASSIGNMENT ADDITION #$1)$: Create a Pivot table that tabulates the number of employees by department & gender. Name the worksheet for

this "Employees by DeptGender". HINT: What data element do you need to COUNT in order to generate this pivot table? It would obviously have to be

a data element whose value was different $($unique$,$ like a database table key$)$ for every row in the table in order to insure an appropriate tabulation of

employees by department$).$ After you have created this pivot table, name the worksheet "Employees by DeptGender".

PART $7($ASSIGNMENT ADDITION #$2)$: Finally, go back to the worksheet named "Employees by DeptGender". Find the cell that displays the number of

female employees in the management department. Click on this to reveal the audit trail of raw data $($employee records$)$ that was used in calculating this

tally. Use conditional formatting to display in red $($or brown$)$ the hire dates of those female employees in management who were hired after $\frac{1}{1}\frac{?}{1993}.$

Rename the worksheet upon which this audit trail has appeared "Data: Fs in Mgmt$".$

Finally, you must re$-$order your worksheets in the following sequence and titles $($and eliminate any extra work sheets that are not needed!$)$:

a$.$ Employee Data $($i$.$e$.$ the raw data you started with$)$

b$.$ Sort

c$.$ Custom Sort

d$.$ Filter

e$.$ Custom Filter

f$.$ SumSalary

g$.$ Employees by DeptGender

h$.$ Data: Fs in Mgmt

Save your work $($probably a good idea after completing each step$)!$

Please submit your assignment by uploading the completed spreadsheet file to the appropriate dropbox for TA$2.$