### Free Educational Resources

• > Exploring Excel 05 A1 Salary Data
+

# Exploring Excel 05 A1 Salary Data

##### Rating:
(0)
Author: matthew stock
##### Description:

http://homework.plus/exploring-excel-05-a1-salary-data/

Salary Data

Project Description:
As the Human Resources Manager, you maintain employee salary data. You exported data from the corporate database into an Excel workbook. You want to display subtotals for salary by city. For further analysis, you want to create a PivotTable to determine what the total salaries would be if employees who earn an Excellent rating get a 5% increase in salary. Finally, you want to create a PivotChart that depicts what percentage of employees earn each performance rating.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step

Instructions

Points Possible

1

0

2

In the Subtotal worksheet, sort the data by Location in alphabetical order and then by Title in Alphabetical order.

5

3

In the Subtotal worksheet, use the Subtotal feature to calculate the average Salary by Location.

10

4

In the Subtotal worksheet, display only the Location averages and grand average. Expand the details for Chicago.

5

5

Use the Data worksheet to create a recommended PivotTable. Select the Sum of Salary by Title recommended PivotTable. Rename the new worksheet PivotTable.

10

6

Add the Location field to the COLUMNS area and remove the Last Name field from the ROWS area in the PivotTable.

5

7

Modify the VALUES field by changing the custom name toSalary Totals and formatting the values with Currency number type with zero decimal places.

10

8

Set a Performance filter to display Excellent ratings only.

5

9

Insert a calculated field named Field1 that calculates a 5% increase in salaries. The new value should give the total new salaries, not just the increase.

5

10

Modify the calculated field by changing the custom name toNew Salary Totals and formatting the values with Currency number type with zero decimal places.

5

(more)
##### Try a College Course Free

Sophia’s self-paced online courses are a great way to save time and money as you earn credits eligible for transfer to over 2,000 colleges and universities.*

No credit card required

25 Sophia partners guarantee credit transfer.

226 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 21 of Sophia’s online courses. More than 2,000 colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial