Logistics
Use pivot tables to summarize data
The file provided gives information about employees in a small company; the data is self-explanatory. You are to create several pivot tables from this data. Each pivot table must be shown on a separate sheet, labeled with the term indicated in bold/italics below. Be sure to edit the pivot table labels to explain what data is being shown (e.g., Number of Employees). When creating the pivot charts be sure to remove any superfluous labeling.
1. ByDepartment. Create a pivot table and pivot chart(column chart) showing the number of employees by department.
2. ByGender. Create a pivot tableand pivot chart(pie chart) showing the number of employees by gender.
3. SalaryByTitle. Create a pivot table and pivot chart(bar chart) showing the average salary by job title.
4. SalaryByTitle&Gender. Create a pivot table showing the average salary by job title and gender.
5. SalaryByDepartment. Create a pivot table showing the total salary by department.
6. StartDateByDepartment. Create a pivot table showing the oldest start date by department. Since the “grand total” is meaningless in this case, do not display it.
7. HiredAfter1990. Create a pivot table showing the average salary by job title, for Customer Service Reps and Managers (only) who were hired in 1991 or later. Since the “grand total” is meaningless in this case, do not display it.
ID First Name Last Name Department Title Salary Hire Date Birth Date Gender Clearance
1009 Kevin Grundies Marketing Customer Service Rep 38900 12/24/89 03/04/61 M C
1016 Oscar Gomez Marketing Customer Service Rep 43500 02/16/90 04/29/67 M N
1032 Hillary Flintsteel Marketing Staff 34500 03/21/90 08/22/58 F N
1042 Maria Andretti Marketing Customer Service Rep 42500 03/21/90 08/20/70 F N
1119 Sharad Manispour Engineering Security Engineer 45600 10/13/90 02/04/69 M S
1173 Roberta Kurzweil Management Manager 63000 06/16/92 12/22/58 F S
1355 Barbara Grabowski Management Manager 75700 10/13/92 04/10/65 F C
1441 James Van Horn Management Manager 66500 12/18/92 02/03/58 M S
1614 Artie Lambros Marketing Customer Service Rep 41000 05/16/93 07/13/70 M N
1851 Melinda English Management Manager 56400 10/01/93 02/14/56 F TS
2002 Edgar Rothrock Engineering Security Engineer 53300 02/08/94 03/21/70 M S
2042 Hillary Cushner Management Staff 32000 08/26/94 05/06/61 F TS
2105 Luca Pacioli Marketing Staff 42300 08/26/95 05/06/70 M S
2122 Yuan Chang Engineering Security Engineer 46540 09/05/95 03/04/64 M TS
2344 Nemesha Mehta Engineering Security Engineer 65000 10/01/95 02/12/65 F S
2451 Francine Detweiler Engineering Security Engineer 56700 10/10/95 04/29/70 F S
2678 Koshi Yamamoto Engineering Security Engineer 49600 11/16/95 01/24/63 F S
2733 John Zumkowski Engineering Staff 33000 04/18/96 12/12/72 M TS
2768 Phyllis Leonard Engineering Security Engineer 59800 12/13/96 07/02/70 F TS
2896 Samuel Gates Engineering Security Engineer 66300 01/28/97 04/10/74 M S
2956 Michael Goldstein Engineering Security Engineer 51000 04/11/97 03/07/63 M TS
3015 Patti Stonesifer Engineering Security Engineer 64300 07/06/98 03/10/66 F S
3043 Joseph Paterno Marketing Staff 32000 07/06/98 03/10/68 M N
3117 Frederich Bednarczyk Engineering Security Engineer 56700 05/14/99 05/27/70 M S
3210 Carmen Ortega-Molina Engineering Security Engineer 46000 09/16/99 03/28/52 F S
3314 Svetlana Kartashev Human Resources Staff 38000 11/05/01 05/03/82 F N
4006 Alice Rovik Human Resources Manager 43000 12/01/01 01/26/55 M S