91|EXCEL – Lending a Helping Hand

BYU Student Author: @Marco
Reviewers: @Spencer, @Christian, @Erick_Sizilio
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

Need a program? Click here.

You’ve been working at a big tech company called Pear Inc. for a couple of years as an accountant. You enjoy working there and helping your co-workers. After lunch, you notice that your new co-worker in the HR department, Jane, is having some trouble. You ask her if there’s anything you can help her with. She responds by asking if you can help her analyze some employee data on excel. You happily accept and begin conducting your analysis.


  1. She tells you there are duplicates and would like to delete them.
  2. Make a new sheet and call it “Info Needed”, this is where you’ll put all info from the next instructions.
  3. Separate employee names to only show first names and name the column “First Name”.
  4. Calculate the age of every employee and name the column “Age”.
  5. Make a column named “Birth Month” and show the birth month of every employee (ex: December, August).
  6. Calculate the # of years the employee has been employed at the company (no decimals) and name the column “Years Employed”.
  7. Bonuses are being given out to the employees who have worked 10 or more years.
    • Highlight the cells that are 10 or more years in the “Years Employed” column.
    • Make a new column named “# of Bonuses” with the number of employees that have worked 10 or more years.
  8. Jane would like to start preparing birthday cards for people who have birthdays in December, make a column named “# of December Birthdays” and calculate the number of birthdays in December.
  9. Make a column named “Average Age” and calculate the average age of employees.

Data Files