FINDING OUT INFORMATION ABOUT YOUR EMPLOYEES Suppose you own a small business and have a workbook with the following list A IDUnique employees identification number B First NameEmployees first name C Last NameEmployees last name D DepartmentEmployees department E TitleEmployees job title F SalaryEmployees annual salary G Hire DateDate employee was hired H Birth DateEmployees birthday I GenderFemale (F) or Male (M) J ClearanceN (none), C (confidential), S (secret), or TS (top secret) You can obtain this workbook from the Web site that supports this text ( www mhhe com haag and select XLM D) Its filename is XLMD Employee xls Perform the following tasks (and please list instructions) a Create a pivot table that shows average salary by gender within department b Create a pivot table that shows the number of employees by clearance c Use conditional formatting to highlight those employees in the Engineering department d Use conditional formatting to highlight those employees who have no clearance (none) e Use basic Filter to show only those employees who have top secret clearance (TS) f Use Custom Filter to show only those employees who earn more than $50,000 ID First Name Last Name Department Title Salary Hire Date Birth Date Gender Clearance 1009 Kevin Grundies Marketing CSR 38,900 12 24 89 3 4 61 M C 1016 Oscar Gomez Marketing CSR 43,500 2 16 90 4 29 67 M N 1032 Hillary Flintsteel Marketing Staff 34,500 3 21 90 8 22 58 F N 1042 Maria Andretti Marketing CSR 42,500 3 21 90 8 20 70 F N 1119 Sharad Manispour Engineering Security Engineer 45,600 10 13 90 2 4 69 M S 1173 Roberta Kurzweil Management Manager 63,000 6 16 92 12 22 58 F S 1355 Barbara Grabowski Management Manager 75,700 10 13 92 4 10 65 F C 1441 James Van Horn Management Manager 66,500 12 18 92 2 3 58 M S 1614 Artie Lambros Marketing CSR 41,000 5 16 93 7 13 70 M N 1851 Melinda English Management Manager 56,400 10 1 93 2 14 56 F TS 2002 Edgar Rothrock Engineering Security Engineer 53,300 2 8 94 3 21 70 M S 2042 Hillary Cushner Management Staff 32,000 8 26 94 5 6 61 F TS 2105 Luca Pacioli Marketing Staff 42,300 8 26 95 5 6 50 M S 2122 Yuan Chang Engineering Security Engineer 46,540 9 5 95 3 4 64 M TS 2344 Nemesha Mehta Engineering Security Engineer 65,000 10 1 95 2 12 65 F S 2451 Francine Detweiler Engineering Security Engineer 56,700 10 10 95 4 29 70 F S 2678 Koshi Yamamoto Engineering Security 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 2768 Phyllis Leonard Engineering Security Engineer 59,800 12 13 96 7 2 70 F TS 2896 Samuel Gates Engineering Security Engineer 66,300 1 28 97 4 10 74 M S 2956 Michael Goldstein Engineering Security Engineer 51,000 4 11 97 3 7 63 M TS 3015 Patti Stonesifer Engineering Security Engineer 64,300 7 6 98 3 10 66 F S 3043 Joseph Paterno Marketing Staff 32,000 7 6 98 3 10 68 M N 3117 Frederich Bednarczyk Engineering Security Engineer 56,700 5 14 99 5 27 70 M S 3210 Carmen Ortega Molina Engineering Security Engineer 46,000 9 16 99 3 28 52 F S 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 25, 2024

FINDING OUT INFORMATION ABOUT YOUR EMPLOYEES Suppose you own a small business and have a workbook with the following list: A. IDUnique employees identification number

FINDING OUT INFORMATION ABOUT YOUR EMPLOYEES Suppose you own a small business and have a workbook with the following list:

A. IDUnique employees identification number

B. First NameEmployees first name

C. Last NameEmployees last name

D. DepartmentEmployees department

E. TitleEmployees job title

F. SalaryEmployees annual salary

G. Hire DateDate employee was hired

H. Birth DateEmployees birthday

I. GenderFemale (F) or Male (M)

J. ClearanceN (none), C (confidential), S (secret), or TS (top secret)

You can obtain this workbook from the Web site that supports this text ( www.mhhe.com/ haag and select XLM/D). Its filename is XLMD_ Employee.xls. Perform the following tasks (and please list instructions):

a. Create a pivot table that shows average salary by gender within department.

b. Create a pivot table that shows the number of employees by clearance.

c. Use conditional formatting to highlight those employees in the Engineering department.

d. Use conditional formatting to highlight those employees who have no clearance (none).

e. Use basic Filter to show only those employees who have top secret clearance (TS).

f. Use Custom Filter to show only those employees who earn more than $50,000.

ID	First Name	Last Name	Department	Title	Salary	Hire Date	Birth Date	Gender	Clearance
1009	Kevin	Grundies	Marketing	CSR	38,900	12/24/89	3/4/61	M	C
1016	Oscar	Gomez	Marketing	CSR	43,500	2/16/90	4/29/67	M	N
1032	Hillary	Flintsteel	Marketing	Staff	34,500	3/21/90	8/22/58	F	N
1042	Maria	Andretti	Marketing	CSR	42,500	3/21/90	8/20/70	F	N
1119	Sharad	Manispour	Engineering	Security Engineer	45,600	10/13/90	2/4/69	M	S
1173	Roberta	Kurzweil	Management	Manager	63,000	6/16/92	12/22/58	F	S
1355	Barbara	Grabowski	Management	Manager	75,700	10/13/92	4/10/65	F	C
1441	James	Van Horn	Management	Manager	66,500	12/18/92	2/3/58	M	S
1614	Artie	Lambros	Marketing	CSR	41,000	5/16/93	7/13/70	M	N
1851	Melinda	English	Management	Manager	56,400	10/1/93	2/14/56	F	TS
2002	Edgar	Rothrock	Engineering	Security Engineer	53,300	2/8/94	3/21/70	M	S
2042	Hillary	Cushner	Management	Staff	32,000	8/26/94	5/6/61	F	TS
2105	Luca	Pacioli	Marketing	Staff	42,300	8/26/95	5/6/50	M	S
2122	Yuan	Chang	Engineering	Security Engineer	46,540	9/5/95	3/4/64	M	TS
2344	Nemesha	Mehta	Engineering	Security Engineer	65,000	10/1/95	2/12/65	F	S
2451	Francine	Detweiler	Engineering	Security Engineer	56,700	10/10/95	4/29/70	F	S
2678	Koshi	Yamamoto	Engineering	Security 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
2768	Phyllis	Leonard	Engineering	Security Engineer	59,800	12/13/96	7/2/70	F	TS
2896	Samuel	Gates	Engineering	Security Engineer	66,300	1/28/97	4/10/74	M	S
2956	Michael	Goldstein	Engineering	Security Engineer	51,000	4/11/97	3/7/63	M	TS
3015	Patti	Stonesifer	Engineering	Security Engineer	64,300	7/6/98	3/10/66	F	S
3043	Joseph	Paterno	Marketing	Staff	32,000	7/6/98	3/10/68	M	N
3117	Frederich	Bednarczyk	Engineering	Security Engineer	56,700	5/14/99	5/27/70	M	S
3210	Carmen	Ortega-Molina	Engineering	Security Engineer	46,000	9/16/99	3/28/52	F	S
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