CISC 1050 Assignment 6 Excel Pivot Tables due Nov. 7, 2016

Use this Excel worksheet provided on my Web site to do this assignment: www.sci.brooklyn.cuny.edu/~jones/cisc1050/Assignments/Employees.xls

Using sorting, pivot tables, and pivotcharts, summarize the data in various ways as requested below. Put all monetary amounts in currency format with 2 decimal places and a floating dollar sign.

Except for 4a, put each part on its own page in the workbook and rename the tab to identify what part it contains. Make the name fairly short, since there is a limit on the number of characters that will fit on the tab. If you can't see the next tab, there is a vertical bar next to the tabs; drag it to the right.

1. Copy the data to Sheet2: Sort the data by department. Insert subtotals on salaries, by department.

2. Copy the data to Sheet3: Sort the data first by title and then by gender.

3. Copy the data to Sheet4: Do a Custom sort on Clearance in this order:

N C S TS

4. Perform the following tasks. If it asks for a number of employees, use ID as the field (since it is a unique identifier), and choose COUNT from Value Field Settings.

a. Create a pivot table that shows the sum of salaries by gender within each department. Put this pivot table on the sheet with the data, starting in cell K1.

b. Create a pivot table that shows the maximum salary by title within each department.

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

d. Filter the pivot table in part c to exclude those in category N.

e. Create a pivot table that shows the number of emloyees by title and then by gender within each department. (Make department followed by title the row headings, and make gender the column headings.)

f. Create a pivot table that shows the number of female employees by title. (Make one of the items a row heading, the other a column heading; you choose which is which.)

Put a PivotChart for this data on the same worksheet, aligned directly under the pivot table, with one row blank in between.

Note that I have used a small enough data set that you can check your answers, so please do!

Rename the worksheet with the data as "Data" and make it the first worksheet in the workbook.

Put your name in backstage view and email me the entire workbook with the subject line A6 1050.