Excel Project 3 | Complete Solution

Excel Project 3
Use the attached file and perform the following steps: create pivot tables to provide the answers to the questions.
1.    Sort the data by ID#
2.    Rearrange the columns: ID#, Department, Title, Pay, Years Worked at Company, Gender, Age, Marital Status, Number of Dependents, Supervisor
3.    Name the sheet- “Original”
4.    Copy the sheet to create a new sheet (Click on the sheet tab, Hold down the control tab and drag)
5.    Name it Total Pay
6.    Group data by Title, showing total pay
7.    Which job title has the largest total pay?
8.    Create another copy of the “Original” sheet
9.    Name it “Average Years Worked”
10.    Group it by Department showing average years worked
11.    Which department has the lowest average years at the company?
12.    Create another copy of the “Original” sheet
13.    Name it “Title Count”
14.    Group it by Title, showing a count of employees by title. Put the count in the Pay Column.
15.    Why were you asked to put the Count in the Pay column? (Hint: Try grouping it with the Count in the Title company, collapse to level 2, and see what it looks like)
16.    Which Title has the most employees?
17.    Create another copy of the “Original” sheet
18.    Name it “Employee Count by Supervisor”
19.    Group it by Supervisor ID, showing the number of employees by supervisor. Show the count in the Title column
20.    Collapse this worksheet to the second level. Is this a good way to show this information? Why or why not? Explain your reasoning. If this is not a good way to show this data, regroup so that it shows the information in a more desirable way. (Hint: you may resort the data before doing the grouping if that helps)
21.    Create another copy of the “Original” sheet
22.    Name it “Dept by Gender”
23.    Group it by Department and Gender, showing a count of Males and Females by department
24.    What happens when you do this?
25.    Create another copy of the “Original” sheet
26.    Name it “Individual”
27.    Create a new grouping and provide the question that would be answered with the grouping.
28.    Create one more question of your own and provide the pivot table to answer that question.