A payroll summary in Excel isn't a replacement for payroll software — you still need that for tax calculations, filings, and compliance. But a clean payroll summary spreadsheet is useful for record-keeping, budgeting, and giving your finance team a quick view of payroll costs by department or pay period. Here's how to build one properly.
Your Payroll Register
One row per employee per pay period. Columns: employee name, employee ID, department, pay type (Salary, Hourly, Contractor), pay rate, hours worked (for hourly employees), gross pay, federal tax withheld, state tax withheld, benefits deductions, retirement deductions, other deductions, total deductions, and net pay.
For salaried employees, gross pay is the semi-monthly or bi-weekly salary amount. For hourly employees:
=F2*G2
Where F2 is hourly rate and G2 is hours worked. For overtime hours, apply the 1.5x multiplier:
=IF(G2>40, (40*F2)+((G2-40)*F2*1.5), G2*F2)
Calculating Total Deductions and Net Pay
Total deductions:
=H2+I2+J2+K2+L2
Where H through L are the individual deduction columns. Net pay:
=G2-M2
Where G2 is gross pay and M2 is total deductions.
Department Summary
Build a pivot table from your payroll register. Add Department to Rows and add Gross Pay, Total Deductions, and Net Pay to Values (all set to SUM). This gives finance a breakdown of payroll cost by department each period — useful for budget tracking and departmental P&L.
Period-Over-Period Comparison
Keep a running history by saving each pay period as a section in your payroll log (or a separate tab). Use SUMIFS to compare this period's total payroll to last period:
=SUMIFS(PayrollLog!H:H, PayrollLog!B:B, "2026-05")
Flag significant changes — a payroll that's 15% higher than last period probably means a new hire or a bonus run that finance needs to know about.
The Easy Way: Using SheetXAI in Excel
Example 1: You have employee and hours data already in the spreadsheet.
"I have an employee list with pay rates and hours worked on Sheet 1. Build a payroll summary calculating gross pay (with overtime for hourly employees), standard deductions, and net pay for each employee, with a department total summary."
SheetXAI reads your data, calculates gross pay including overtime, applies deductions, and builds the department summary.
Example 2: Your HR data lives in your HRIS or payroll system.
"Pull this pay period's hours and rates from our HRIS and build a payroll summary showing gross pay, deductions, and net pay by employee and department."
SheetXAI connects to your system and builds the payroll summary.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Headcount Tracker in Excel, How to Create a Monthly Expense Report in Google Sheets, and Google Sheets AI Guide.
