Using the Employee Data and the Departments Data CSV files (download from the BB Course

Content -> Excel Files folder), perform the following tasks:

1. Import Employees Data to a new sheet and name it Employees. Import Departments Data to

a new sheet and name it Departments. Both sheets in the same Workbook. Save it as YourFirstName_YourLastName.xlsx. Submit this file after completing the following steps.

In the Employees sheet, perform:

2. Create sheet heading as Excel Teaching Company by inserting a new row at the top. Merge,

Center the sheet heading (Row 1, Column A to J) and Format (Bold, font size 20, highlight). Also,

make the data column headings Bold.

3. Insert a new column between D and E, and name it Dept Name. Using VLOOKUP fill this

column with the Department Names from Departments sheet

4. Sort on Department, Title and Salary in that order. (order: A to Z for Names; Largest to Smallest

for Salary).

5. Conditionally format the Hire Date column if employee is on probation, i.e. hired during last

two years (hint: Conditional Formatting -> Highlight Cells Rules -> Greater Than 10/09/2016).

6. Using Filter exclude the employees that have no Security Clearance, i.e. Clearance =N.

7. Using Subtotal, show the average salary of the employees by each department, showing

summary below the data.

8. Create a Pivot Table from this data on the same sheet (on the right side, next to the data). Use

Title as rows, Gander as columns, and Salary (sum) as values. Filter out blanks from both rows

and columns of this pivot table.

9. Create a Pivot Chart from this table using 3-D Clustered columns. Place it right under the pivot

table.

Employee data

ID	First Name	Last Name	Dept ID	Title	Salary	Hire Date	Birth Date	Gender	Clearance
1009	Kevin	Grundies	401	CSR	38900	12/24/1989	3/4/1961	M	C
1016	Oscar	Gomez	401	CSR	43500	1/25/2017	4/29/1967	M	N
1032	Hillary	Flintsteel	401	Staff	34500	2/5/2017	8/22/1958	F	N
1042	Maria	Andretti	401	CSR	42500	3/21/1990	8/20/1970	F	N
1119	Sharad	Manispour	101	Manager	95200	10/13/1990	2/4/1969	M	S
1173	Roberta	Kurzweil	301	Manager	85600	6/16/1992	12/22/1958	F	S
1355	Barbara	Grabowski	301	Manager	89600	10/13/1992	4/10/1965	F	C
1441	James	Van Horn	301	Manager	66500	12/18/1992	2/3/1958	M	S
1614	Artie	Lambros	401	CSR	41000	2/2/2017	7/13/1970	M	N
1851	Melinda	English	301	Manager	97200	10/1/1993	2/14/1956	F	TS
2002	Edgar	Rothrock	101	Engineer	53300	11/27/2016	3/21/1970	M	S
2042	Hillary	Cushner	301	Staff	32000	8/26/1994	5/6/1961	F	TS
2105	Luca	Pacioli	401	Staff	42300	1/28/2017	5/6/1950	M	S
2122	Yuan	Chang	101	Engineer	46540	9/26/2016	3/4/1964	M	TS
2344	Nemesha	Mehta	101	Engineer	65000	10/1/1995	2/12/1965	F	S
2451	Francine	Detweiler	101	Engineer	56700	10/26/2016	4/29/1970	F	S
2678	Koshi	Yamamoto	101	Engineer	49600	11/16/1995	1/24/1963	F	S
2733	John	Zumkowski	101	Staff	33000	2/1/2017	12/12/1972	M	TS
2768	Phyllis	Leonard	101	Engineer	59800	12/13/1996	7/2/1970	F	TS
2896	Samuel	Gates	101	Engineer	66300	1/28/1997	4/10/1974	M	S
2956	Michael	Goldstein	101	Engineer	51000	4/11/1997	3/7/1963	M	TS
3015	Patti	Stonesifer	101	Engineer	64300	7/6/1998	3/10/1966	F	S
3117	Frederich	Bednarczyk	101	Engineer	56700	1/20/2017	5/27/1970	M	S
3210	Carmen	Ortega-Molina	101	Engineer	46000	9/16/1999	3/28/1952	F	S
3314	Svetlana	Kartashev	201	Staff	38000	11/5/2001	5/3/1982	F	S
4006	Alice	Rovik	201	Manager	43000	12/1/2001	1/26/1955	M	S
4123	Steve	Ballmer	101	Engineer	42900	2/22/2017	4/14/1970	M	N

Department data

Dept Code	Dept Name
101	Engineering
201	Human Resources
301	Management
401	Marketing