Excel Skills for Payroll Jobs

The Excel skills for payroll jobs are less about fancy features and more about getting every cent right: gross pay with overtime, rounding, working-day counts for pro-rata pay, totals by department and careful checks before anyone gets paid. Payroll software does much of the processing, but the exports, reconciliations and one-off calculations happen in Excel. This guide shows each skill on one small timesheet, the way a payroll Excel test would ask for it.

What payroll teams use Excel for#

Payroll staff use Excel to prepare, check and explain pay. Typical tasks are:

• Timesheets and overtime: turning hours into gross pay.
• Pro-rata pay: paying new starters and leavers for part of a period.
• Totals and journals: summing pay by department or cost centre for the accounts team.
• Reconciliations: comparing this period's payroll with the last one and explaining the differences.
• Lookups: pulling rates, tax bands or bank details from a master list.

All examples below use this timesheet for one week:

Skill 1: Gross pay with overtime#

Gross pay is hours times rate, but overtime hours usually pay more. Under a rule of time-and-a-half after 40 hours, split the hours in two:

=MIN(C2,40)*D2+MAX(0,C2-40)*D2*1.5

• MIN(C2,40) gives the normal hours, never more than 40.
• MAX(0,C2-40) gives the overtime hours, never less than 0.

For Ana, that is 40 × 18.50 = 740.00 plus 4 × 18.50 × 1.5 = 111.00, so 851.00. Dev's 46 hours give 896.00 + 201.60 = 1,097.60. Eli worked under 40 hours, so his overtime part is 0. Keep the 40 and the 1.5 in input cells (for example $H$1 and $H$2) so a policy change is one edit. The payroll calculator guide extends this sheet with tax and net pay.

Skill 2: Round every money result#

In payroll, you round with a formula, not with formatting. Formatting a cell to two decimals only hides the extra digits, and those hidden fractions can make a total differ from the payslips by a cent or two.

=ROUND(MIN(C2,40)*D2+MAX(0,C2-40)*D2*1.5,2)

The 2 means two decimal places. A few related tools come up often:

Follow your company's policy on which direction to round. The ROUND function guide and the rounding lesson cover all three functions.

Skill 3: Working days with NETWORKDAYS and WORKDAY#

NETWORKDAYS counts the working days (Monday to Friday) between two dates, including both ends. It is the standard way to work out pro-rata pay for someone who starts or leaves mid-month.

Say a new starter on a monthly salary of 3,000 begins on Monday 16 November 2026:

=NETWORKDAYS(DATE(2026,11,1),DATE(2026,11,30))
=NETWORKDAYS(DATE(2026,11,16),DATE(2026,11,30))
=ROUND(3000*11/21,2)

November 2026 has 21 working days, the starter works 11 of them, and the pro-rata pay is 1,571.43. In a real sheet, point the formulas at cells instead of typing the numbers. Add a range of public holidays as the third argument, for example =NETWORKDAYS(B2,C2,$H$2:$H$10), and those days are left out too. The NETWORKDAYS function guide explains the arguments.

WORKDAY moves a date by working days. If payday is the 25th but that is a Sunday, this returns the Friday before:

=WORKDAY(DATE(2026,10,25)+1,-1)

The result is Friday 23 October 2026. Adding 1 first means a 25th that is already a weekday stays as it is. The NETWORKDAYS and WORKDAY lesson practises both.

Skill 4: Totals by department with SUMIFS#

The accounts team needs payroll costs split by department or cost centre. SUMIFS adds the numbers that meet one or more conditions:

=SUMIFS(E2:E6,B2:B6,"Ops")

This returns 2,316.25 (851.00 + 790.00 + 675.25). Sales comes to 1,895.60. Add more pairs for more conditions, such as a pay period column. Then check that the department totals add up to the grand total: if =SUM(E2:E6) is 4,211.85 and the two departments add to the same, nothing is missing. The SUMIFS function guide covers the syntax.

A PivotTable gives the same split with no formulas, and it is quicker when there are many departments.

Skill 5: Lookups and reconciliations#

Payroll often joins data from two lists, such as this period's hours and the employee master with rates. XLOOKUP (Excel 2021 or Microsoft 365) or VLOOKUP with FALSE does that:

=XLOOKUP(A2,Master!A:A,Master!D:D,"Not found")

"Not found" flags a new employee missing from the master list, which is exactly what you want to catch before payday.

A reconciliation compares two sets of numbers and explains every difference. A simple one puts last period's gross next to this period's, with a variance column and a flag:

=IF(ABS(F2-E2)>100,"Check","")

Anything flagged gets a reason: a pay rise, overtime, a new starter, or an error. Bookkeeping and accounts payable roles use the same habits; see the Excel skills for bookkeepers and the Excel skills for accounts payable.

Working safely with pay data#

Payroll files hold sensitive personal data, so a few habits matter as much as formulas:

• Paste values before you send a file, so nobody sees the workings or linked sheets.
• Protect input sheets so formulas can't be typed over by accident.
• Check hidden rows and columns before sharing. Hidden is not deleted.
• Keep one input per cell (rates, thresholds, holiday lists) and lock them with $ in formulas.
• Total and cross-check every report against the payroll register.

How to prepare for a payroll Excel test#

Payroll and HR Excel tests usually give you a small employee table and ask for totals, overtime, rounding, date counts and lookups, often against a timer. To prepare:

1. Drill the core functions until you can type them without notes: SUM, SUMIFS, IF, ROUND, NETWORKDAYS, XLOOKUP or VLOOKUP.
2. Practise under time pressure. A timed basic Excel test shows how fast you really are.
3. Check your level with the Excel level test to see which skills to study first.
4. Read the HR side too. Many payroll roles sit in HR, so the Excel skills for HR jobs are worth knowing.

Practise it#

Try the exercise on this page: it is the Ops total from the timesheet, and the answer should be 2,316.25. Then work through the HR interview drill, which practises headcount, pay and date questions in a live spreadsheet with instant feedback, and practise NETWORKDAYS and WORKDAY until pro-rata pay feels routine.