1. 1.

   Open the NP_EX_6-3.xlsx workbook located in the Excel6 > Review folder included with your Data Files. Save the workbook as NP_EX_6_Recruits in the location specified by your instructor.

2. 2.

   In the Documentation sheet, enter your name and the date in the range B3:B4.

3. 3.

   Go to the Applications worksheet containing a list of applications made to the Atlanta office for 12 positions. The worksheet lists the dates that each applicant completed each stage of the recruitment process from the date the application was received, through the phone screening, manager interview, onsite interview, offer date, and acceptance date. The value #N/A indicates that the applicant did not pass to that recruitment stage. See the Table Data worksheet for a description of each field.

4. 4.

   Use conditional formatting to locate records that have a duplicate Applicant ID in the range A5:A1043. Delete the second duplicate record. Do not remove the conditional formatting rule when you are finished.

5. 5.

   One applicant record is duplicated for all fields except for the Applicant ID field. Use the Remove Duplicates command to remove the duplicate.

6. 6.

   In the Applications worksheet, show how applicants are tracked through the recruitment process by sorting the data range by the Position field in A to Z order and then by Acceptance Date, Offer Date, Onsite Date, Manager Date, and finally Phone Date fields, with all dates sorted from newest to oldest. The sorted data should show applicants for each position that went farthest last.

7. 7.

   Filter the data in the Applications worksheet to show only those records for which the value of the Hired field is Yes.

8. 8.

   Copy the filtered values and paste them into the Hires worksheet starting at cell A4.

9. 9.

   In the Hires worksheet, do the following:

   1. Sort the data in ascending order of the Position field.

   2. Add subtotal rows at each change in the value of the Position field, showing the average of the Base Salary field within each Position.

   3. Freeze panes at cell F5 so that you can scroll vertically and horizontally while keeping the data labels in view.

10. 10.

    Return to the Applications worksheet and clear the filters from the data.

11. 11.

    A colleague of Jaceks wants specific information about five hires to the IT and Marketing departments. Go to the IT and Marketing worksheet and complete the following advanced filter:

    1. Complete the criteria range to create a list of new hires from the IT department with base salaries greater than $70,000 or from the Marketing department with base salaries greater than $60,000.

    2. Run the Advanced Filter using the copy data to another location option. Use the data in the range A4:N1041 of the Applications worksheet as the list range, the range A4:B6 in the IT and Marketing worksheet as criteria range, and cell D4 of the IT and Marketing worksheet as the Copy to cell.

12. 12.

    Return to the Applications worksheet, convert the data range to an Excel table, and then rename the table using Recruits as the name.

13. 13.

    In the Recruits table, do the following:

    1. Insert a new field named Full Name between the Last Name and Position fields.

    2. In the Full Name field, enter the formula =[Last Name] & , & [First Name] to insert the applicants full names.

    3. Insert a new field after the Base Salary field named Days to Hire by entering the field name in cell P4.

    4. In the Days to Hire field, use your mouse to enter a formula calculating the value of the Acceptance Date field in cell M5 minus the Application Date field in cell G5.

    5. Apply the General number format to the result so that the values appear as days rather than dates and then resize the column to make the field name visible.

14. 14.

    Insert a slicer for the Position field. Move the slicer to the Metrics worksheet and resize it to fit in the range B4:B13. Display the slicer button in two columns.

15. 15.

    In the range E5:E10 of the Metrics worksheet, so the following to count the number of applicants who reach each stage of the recruitment process:

    1. In cell E5, use the SUBTOTAL function with the COUNTA function to count the number of entries in the Applicant ID field of the Recruits table.

    2. In cell E6, use the SUBTOTAL function with the COUNT function to count the number of values in the Phone Date field.

    3. In cell E7, use the SUBTOTAL function with the COUNT function to count the number of values in the Manager Date field.

    4. In cell E8, use the SUBTOTAL function with the COUNT function to count the number of values in the Onsite Date field.

    5. In cells E9 and E10, use the SUBTOTAL function with the COUNT function to count the number of values in the Offer Date and Acceptance Date fields, respectively.

16. 16.

    Jacek wants to track certain Key Performance Indexes (KPIs) that indicate the efficiency of the recruitment process. Calculate the following values:

    1. In cell E11, calculate the number of applicants for each hire by dividing the value in cell E5 by the value in cell E10.

    2. In cell E12, calculate the number of interviews for each hire by dividing the sum of the values in cells E7 and E8 by the value in cell E10.

    3. In the range F6:F9, calculate the percent of the original applicants that survive to each step in the process by dividing the number of applicants that made it to each step by the value in cell E5.

    4. In the range E5:E10, use conditional formatting to add orange data bars with a gradient fill to the values to show how the number of applicants is trimmed during recruitment.

17. 17.

    In the Applications worksheet, create a Histogram chart of the data in the range P4:P1041 to the view the distribution of the number of days required for hiring new employees.

18. 18.

    Do the following to the chart:

    1. Move the chart to the Metrics worksheet, and then resize the chart to cover the range H4:M13.

    2. Change the chart title to Days to Hire and add data labels to the bars in the histogram.

    3. In the axis options, change the histograms Bin width to 5 with an Overflow bin value of 50 and an Underflow bin value of 25.

19. 19.

    Test the dashboard you created by showing the recruitment statistics for the Programmer, Systems Analyst, and Website Designer positions. The KPIs updated to reflect this subset of the data and the histogram changes to show the distributions of the number of days required to fill those three positions.

20. 20.

    Save the workbook, and then close it.