Important: Do this in excel, for part 1 and 2: select any random departments, and any random employee names and ID. you may select any random inputs for salary and everything else. then do the part 3 to 5 based on what you choose.

(1)**On one sheet:

1. a)createe a simple listing of 3 departments relative to your industry
2. b)In a separate column, create a list for:
3. Full-TimePart-Time

(2)**On a separate new sheet, createe a listing of 20 employees including:

1. a)Employee ID
2. b)Last Name
3. c)First Name
4. d)Department Name
5. Use Data Validation to createe a listing of departments from which to select (use the listing
6. from the sheet created in step 1)
7. e)Job Title
8. f)Full-time or Part Time
9. Use Data Validation to createe a listing from which to select (use the listing from the sheet
10. created in step 1)
11. g)Annual Salary

-Note: You will eventually be creating an Organization Chart for these employees.

(3)***CREATE A COPY OF THE EMPLOYEE LISTING SHEET (created in step 2)...ON THE NEW SHEET...

1. a)Sortthe table alphabetically by department, then by salary highest to lowest
2. b)ApplyData BarConditional Formattingto the salary
3. c)Filter the tableby the department of your choice

(4) ***CREATE ANOTHER COPY OF THE EMPLOYEE LISTINGSHEET FROM STEP 2...ONA NEW SHEET

1. a)Copy the sheet and prepare it to applySubtotals.Subtotal for the average salary by department.
2. b)Collapse to show only level 2

Revised 9.20.2020

(5)**Using theEMPLOYEE LISTINGsheet created inSTEP 2, create aPivot Tableon a new worksheet showing:

1. a)the sum of the annual salaries for full-time versus part-time employees by job title
2. b)The report should be set to filter by Department