1 Start Excel. Open Exp19_Excel_AppCapstone_Comp.x/sx. Grader has automatically added 0 your last name to the beginning of the filename. 2 Fill the range A1:E1 from the Employee_Info worksheet across all worksheets, maintaining the 2 formatting. 3 Make the New_Construction worksheet active and create Range Names based on the data in 2 the range A6:B9. 4 Ungroup the worksheets and ensure the Employee_Info worksheet is active. Click cell G6 and 3 enter a nested logical function that calculates employee 401K eligibility. If the employee is full time (FT) and was hired before the 401k cutoff date 1/1/19, then he or she is eligible and Y should be displayed, non-eligible employees should be indicated with a N. Be sure to utilize the date located in cell H3 as a reference in the formula. Use the fill handle to copy the function down completing the range G6:G25. 5 Apply conditional formatting to the range G6:G25 that highlights eligible employees with Green 2 Fill with Dark Green text. Eligible employees are denoted with a Y in column G. 6 Create a Data Validation list in cell J7 based on the employee IDs located in the range A6:A25. 2 Add the Input Message Select Employee ID and use the Stop Style Error Alert. 7 Enter a nested INDEX and MATCH function in cell K7 that examines the range B6:H25 and 2 returns the corresponding employee information based on the match values in cell J7 and cell K6. Note K6 contains a validation list that can be used to select various lookup categories. Use the Data Validation list in cell J7 to select Employee_ID 31461 and select Salary in cell K6 to test the function. 8 Enter a conditional statistical function in cell K14 that calculates the total number of PT 2 employees. Use the range E6:E25 to complete the function. 9 Enter a conditional statistical function in cell K15 that calculates the total value of PT employee 2 salaries. Use the range E6:E25 to complete the function. 10 Enter a conditional statistical function in cell K16 that calculates the average value of PT 2 employee salaries. Use the range E6:E25 to complete the function. 11 Enter a conditional statistical function in cell K17 that calculates the highest PT employee 1.6 salary. Use the range E6:E25 to complete the function. 12 Apply Currency Number Format to the range K15:K17. 2 13 Click cell K11 and type FT. Click cell A28 and type Full Time Employees. 2