Excel Dates

Updated 2017.1201

Open the Employee file. Then make the following changes:

  1. [1] Save it with the name Employee-FirstName-LastName.xlsx using your first and last name.
  2. [1] Add your name to the right part of a custom header.
  3. [1] Set the font for the entire worksheet to 10-point Verdana.
  4. [1] Merge and center rows 1-2 over columns A through M (you will have to perform 2 separate operations to complete this, one for each row).
  5. [1] Set the Cell Style of A1 to Title.
  6. [1] Set the Cell Style of A2 to Heading 4.
  7. [1] Make the words Today (A4) and Raise Rate (A5) bold.
  8. [1] Set the Cell Style of A7:M7 to Heading 3.
  9. [1] Turn the "wrap text" feature on for row 7. Adjust all column widths so that no words in the column headings are split over two lines.
  10. [1] Center all of the text in row 7.
  11. [1] Put a formula for the current date in cell B4. This should always display the current date (the date the spreadsheet is opened), no matter when the spreadsheet is opened. If necessary, widen column B.
  12. [1] Put the number 5% in cell B5. Format it as a percent with 2 decimal places.
  13. [2] Put a formula in the Job Title column that will look up the ID Number (from column C) in the lookup table on the JobTitles worksheet. Note that you must do an exact lookup.
  14. [1] In the New Hourly Wage column, put the proposed new hourly wage with a raise added in. Calculate this by taking the current hourly wage times the raise rate (B5) and adding on the current hourly wage. (e.g. if the current hourly wage is $10.00 and the raise rate is 5%, multiply $10.00 times 5% (50 cents) and add that to the original hourly wage (10.00), giving the new hourly wage of $10.50.
  15. [1] Format the New Hourly Wage column as Accounting with 2 decimal places. Format the original Hourly Wage column the same way.
  16. [1] In the Annual Salary column, put the proposed new hourly rate multiplied by 2080 (the number of hours in a 52-week year).
  17. [1] Put the total of all of the annual salaries at the bottom of the Annual Salary column.
  18. [1] Format the total of the annual salaries with the Total cell style.
  19. [1] Delete row 46.
  20. [1] Format all values in the Annual Salary column, including the total at the bottom, as Accounting with 0 decimal places.
  21. [1] The Hire Date and Birth Date columns currently have date serial numbers in them. Format these serial numbers with the 14-Mar-2012 format (4-digit year). Right-align the columns. If necessary, widen them.
  22. [2] In the Years with Company column, put a formula to calculate the number of years an employee has been employed. Subtract the year of the Hire Datefrom the year ofToday. This will give the number of years that the employee will have been employed at the end of the current calendar year.
  23. [2] In the College Grad column, put a formula that will display either the value TRUE (no quotation marks) or the value FALSE (also no quotation marks). Put the value TRUE for all employees who have 16 or more years of Education. Put the value FALSE for everybody else. Note that you do not have to use an IF function here.
  24. [1] Sort the data in descending order using the "Years with Company" as the sort key.
  25. [1] Apply an AutoFilter (not an Advanced Filter) that selects only those employees who have been with the company for 30 or more years. You cannot just check of the boxes. You must provide a rule.
  26. [1] Make sure all columns are wide enough to display the data they contain.
  27. [1] In Print Preview, set the page orientation for the worksheet to landscape.
  28. [1] In Print Preview, make sure that all of the columns of the printout are scaled to fit on a single page.
  29. [2] Create a Column Chart (2D Clustered Column) that displays the last names of the employees (just those selected in the filter above) on the horizontal axis and the annual salary of each of those employees on the vertical axis.
  30. [1] Use the Move Chart button to move the chart to its own worksheet.
  31. [1] Set the Chart Style to Style 7.
  32. [1] Change the Fill Color for all of the bars to Dark Red from the Standard Colors row.
  33. [1] Set the title of the vertical (Y) axis to Salary.
  34. [1] Make the title of the chart Employees with 30+ Years of Service.
  35. [1] If your chart has a legend, delete the legend.
  36. [1] Set the font size for the following to 14: the numbers on the vertical axis, the title on the vertical axis, and the names on the x-axis.

40 points.

9/28/2018Employee (1).docxPage 1 of 2