For this activity, let’s apply conditional formatting and page layout to an employeer’s time and pay tracking spreadsheet. Open the data here and copy/paste the values into a new workbook. Save the workbook as LastName_Payroll.xlsx.

First, we need to find the total hours worked and the total hours to help calculate gross pay. In Column K, use the SUM function to determine the hours worked.

Between rows M and N insert a new column and find the sum of cells L, M, and N. Make sure you autofill this formula for each employee.

Gross pay is calculated as (Total hours) x (Hourly rate). In cell P2 enter the formula that finds the product N1*O1. Then, autofill this formula to all employees.

Now let’s work on conditional formatting.

• Identify the managers: use a conditional rule to highlight the two row’s with the managers.
• Identify overtime: use a conditional rule to highlight any employees that worked overtime.
• Pay time and a half: use an IF function to calculate overtime pay.