Payroll calculations in Excel

A payroll sheet takes hours and pay rates and turns them into gross pay, deductions, net pay and the employer's own costs. Each step is a short formula that refers to a rate table. This page builds one week of payroll step by step and explains the reasoning behind every formula.

Gross to net in plain English

Payroll runs on a few terms that are worth getting straight first:

• Gross pay is what an employee earned before anything is taken out: hours times the hourly rate, plus any overtime premium.
• Overtime is pay for hours above a weekly threshold, at a higher rate. In the example table, hours above 40 are paid at 1.5 times the hourly rate.
• Pre-tax deductions are amounts taken from gross pay before income tax is worked out, such as some retirement contributions. They reduce taxable wages.
• Withholding is income tax the employer holds back from the employee's pay and later pays over on the employee's behalf.
• Employee payroll taxes (in the example table, Social Security and Medicare) are also taken out of the employee's pay.
• Net pay is what the employee actually receives.
• Employer payroll taxes are the business's own cost on top of gross pay. They are not taken from the employee.

A good payroll sheet keeps every rate in one table and has every formula point to it. When a rate changes, you change one cell, and the whole sheet follows.

Why this matters for MO-220 practice

The Excel for Accounting exam (MO-220) lists payroll under "Perform routine accounting activities". The skills are calculating gross and net pay for several employees, calculating the employer's payroll tax liabilities, and using functions to spot irregularities. This lesson has one exercise for each.

MO-220 task instructions describe the purpose rather than naming the function. You will see "flag any employee who worked more than 60 hours", not "use IF with OR". So the skill is to translate a business rule into the right formula. Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example: Summit Bike Repair

Summit Bike Repair runs payroll weekly. This is the week ending 6/14/2026. The example rate table sits in columns K and L:

Employees are in rows 5 to 12, with the name in A, hours in B, hourly rate in C and pre-tax deduction percentage in D. Gross pay goes in column E.

Step 1: gross pay with overtime

An employee who works 40 hours or less is paid hours times rate. Above 40, the first 40 hours are paid at the normal rate and only the extra hours get the multiplier. That "if" turns into an IF function. In E5:

=IF(B5>$L$4,$L$4*C5+(B5-$L$4)*C5*$L$5,B5*C5)

Read it in three parts. The test asks whether hours are above the threshold. If yes, pay the threshold hours at the normal rate, then add the extra hours at the rate times the multiplier. If no, pay hours times rate. The threshold and multiplier are locked with dollar signs so every row uses the same table cells when you fill down.

For E. Haddad, 43.5 hours at $42.00:

• Regular pay: 40 × $42.00 = $1,680.00
• Overtime pay: 3.5 × $42.00 × 1.5 = $220.50
• Gross pay: $1,900.50

Filled down, the column shows:

I. Petrov worked exactly 40 hours, so the test "more than 40" is false and there is no overtime. That edge case is a good one to check by hand.

Step 2: taxable wages, withholding and net pay

The second exercise continues with Copperleaf Studio, whose example table uses 12% withholding. Take E. Haddad there: 45 hours at $30.25, so gross pay is $1,436.88 (shown to the cent), with a 6% pre-tax deduction.

Follow the money. From $1,436.88 gross, the pre-tax deduction takes $86.21. Withholding is 12% of taxable wages, $162.08. Social Security and Medicare together are 7.65% of gross pay in this example, $109.92. What is left, $1,078.67, is net pay. Note the order: withholding is based on taxable wages, after the deduction, while the example Social Security and Medicare are based on gross pay.

Step 3: employer taxes and review flags

The third exercise, for Bluebird Veterinary, looks at the employer's side. The employer pays its own share of Social Security and Medicare plus unemployment tax, all on gross pay. In F5:

=E5*($L$7+$L$8+$L$9)

Bluebird's unemployment rate in the example table is 1.20%, so the combined rate is 8.85%. For A. Rivera, gross pay of $1,650.00 gives employer taxes of $146.03. Summed with =SUM(F5:F12), the week's employer taxes are $963.36. That total is a liability until it is paid, and the bookkeeping side records it as an expense and a payable. See payroll journal entries for how these amounts are posted.

Payroll review is the last step. Bluebird wants a flag on any row where someone worked more than 60 hours or is paid below the example minimum hourly rate in L10. Either condition is enough, so the test uses OR. In G5:

=IF(OR(B5>60,C5<$L$10),"Check","OK")

Two rows come back as Check: I. Petrov, with 69.5 hours, and H. Ito, paid $14.25 an hour. Everyone else shows OK. A flag column like this doesn't decide anything. It points a person at the rows that need a second look.

Common mistakes

Each exercise is checked against these errors, and the feedback tells you which one you made.

Ignoring overtime

A plain =B5*C5 pays every hour at the regular rate. E. Haddad's gross pay drops from $1,900.50 to $1,827.00. Anyone over the threshold is underpaid.

Paying every hour at the overtime rate

The opposite slip multiplies all hours by 1.5 once the threshold is passed. E. Haddad would get 43.5 × $42.00 × 1.5 = $2,740.50. Only the hours above 40 earn the premium.

Calculating withholding on gross pay

Withholding in these exercises is based on taxable wages, after the pre-tax deduction. Using gross pay overstates it: for Copperleaf's E. Haddad, $172.43 instead of $162.08.

Leaving Social Security and Medicare out of net pay

If net pay subtracts only the deduction and withholding, it comes to $1,188.59 for E. Haddad, which is $109.92 too high. Employee payroll taxes come out of the employee's pay too.

Leaving out unemployment tax

The employer's cost includes unemployment tax on top of its Social Security and Medicare share. Without it, A. Rivera's employer taxes would be $126.23 instead of $146.03.

Flagging only when both conditions are true

Using AND instead of OR flags a row only when someone works over 60 hours and is paid below the minimum. At Bluebird, nobody meets both, so no rows are flagged, and both real problems are missed.

How to check your payroll sheet

• Check one overtime row and one 40-hour row by hand.
• Net pay plus all the amounts taken out should equal gross pay.
• No formula should contain a typed rate such as 40, 1.5 or 0.062. Every rate should come from the table.
• Change one rate in the table and confirm the whole sheet updates.

Related topics: payroll journal entries shows how gross pay, withholdings and employer taxes are recorded, and accrued expenses adjusting entries covers wages earned but not yet paid at period end.