Microsoft Excel – Lesson 3

/ Excel, Excel Resources, Office / By

Activity Overview

The following activity illustrates how a business can use spreadsheets to compute payroll amounts for its employees (known as a payroll register).

New Skill

In this activity, you will practice how to:

  1. Use formulas to multiply, add, and subtract numbers.
  2. Change page margins and orientation.
  3. Merge and center titles and subtitles.

Lesson

  1. Open the Payroll.xls file.
  2. Bold cells A1-L10.
  3. Underline row 10. Select the ENTIRE row.
  4. Format the width of columns A, B, and C to 12.0 and left align. Select the ENTIRE column.
  5. Format the width of columns D and E to 10.0 and center align. Select the ENTIRE column.
  6. Format the width of columns F – L to 11.0 and right align. Select the ENTIRE column.
  7. Format cells E12 – L31 as numbers displaying 2 decimal places.
  8. Compute GROSS PAY for the first employee Ivana Bernadin in cell F12. GROSS PAY = HOURS WORKED multiplied by HOURLY RATE.
  9. Compute FEDERAL TAX for the first employee in cell G12. FEDERAL TAX = GROSS PAY multiplied by 15%.
  10. Compute SOCIAL SEC. TAX for the first employee in cell H12. SOCIAL SEC. TAX = GROSS PAY multiplied by 6.2%.
  11. Compute MEDICARE TAX for the first employee in cell I12. MEDICARE TAX = GROSS PAY multiplied by 1.45%.
  12. Compute STATE TAX for the first employee in cell J12. STATE TAX = GROSS PAY multiplied by 4%.
  13. Compute PENSION for the first employee in cell K12. PENSION = GROSS PAY multiplied by 3%.
  14. Compute NET PAY for the first employee in cell L12. NET PAY = GROSS PAY minus FEDERAL, SOCIAL SEC., MEDICARE, STATE, and PENSION.
  15. Use the AutoFill feature to copy the formulas down to the remaining cells for each of the employees.
  16. Select the entire spreadsheet and change the font to Arial Narrow and 13 font size.
  17. Select the range A1: L1. Merge and Center across the selection.
    Select the range A2:L2. Merge and Center across the selection.
    Select the range A3:L3. Merge and Center across the selection.
    Select the range A4:L4. Merge and Center across the selection.
  18. Delete Sheet 2 and Sheet 3.
  19. Rename Sheet 1 to GAP PAYROLL.
  20. Change page setup settings as follows:
    a. Scale to fit to one page.
    b. Page orientation to landscape.
    c. Left and right margins to .25 inches.
    d. Horizontally and Vertically center.
    e. Insert a Custom Header using Left section: Your Name, Center section: GAP PAYROLL , and Right section: Today’s Date.
  21. Print Preview your spreadsheet to view your header and make sure it fits on one page.
  22. Save your document.
  23. Optional: Print a copy for review.
Scroll to Top