Reference no: EM133214221
1. Open ExcelTest.xls Workbook
2. Create 3 New Worksheets called: Manage, Formula, Pivot
3. In Manage worksheet, please do the following:
a. Copy all data from Original worksheet
b. Format the table, Freeze the top row
c. Hiding the Social Security Number (SSN) information
d. Format all cell in the first row to wrap text and center
e. Format date column to MM/YY
f. Change all data to Arial font, size 11
g. Sort the data by Job Rating
4. In Formula worksheet, please do the following:
a. Copy all data from Original worksheet
b. Create a new column called Earning that categorize Salary less than 25000, Low; Salary between 25000 and less than 50000, Moderate; and Salary equal or more than 50000, High (Use the logical IF function). Format the Low cell, red; the Moderate cell, yellow; and High cell, green (Use Conditional Formatting)
c. Create a New Table label Summary Table next to the Tax Table
d. In the Summary Table calculate:
i. Total of Salary (SUM)
ii. Average Salary by Job Rating (AVERAGE)
iii. Calculate number of people with Low, Moderate, High Salary (Use COUNTIF)
iv. Calculate the total salary of people with High Salary (Use SUMIF)
5. In Pivot worksheet, please do the following:
a. Copy all data from the Original worksheet
b. Create a pivot table of Total Salary of Each Department by Employee Status
c. Create a new column call Tax Rate, next Job Rating. Value of the Tax Rate of each employee column is determined by the Tax Table (VLOOKUP)
6. Create a chart
a. Create a chart of Earning and add title and legend, save it in a new worksheet called Chart
Calculate number of employee by department and status and a chart to display the information, save it in the Chart worksheet