DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on Originally published at ashishexcelnotes.hashnode.dev

How to make a monthly salary slip and payroll register in Excel

#excel #smallbusiness #productivity #tutorial

If you pay a handful of staff, you don't need payroll software to stay organised. You need one register with a row per employee per month, and a slip that pulls from it. This guide builds both in Excel, so you type each number once and print a clean slip for anyone.

Note: deductions such as PF, ESI, professional tax or TDS depend on your state, your headcount and current rules. This article shows the spreadsheet mechanics only. It is not tax, payroll or legal advice; check the rates with an accountant or the official sources.

Sheet 1: Employees

Col Field
A Emp ID
B Name
C Designation
D Basic (monthly)
E HRA
F Other allowances
G Bank account (last 4 digits)

Keep only what you need. Storing full bank and ID numbers in a spreadsheet you email around is a risk.

Sheet 2: Register (one row per employee per month)

Col Field
A Month (first day, e.g. 01-10-2026)
B Emp ID
C Name
D Working days in month
E Days paid
F Basic earned
G HRA earned
H Allowances earned
I Gross
J Deduction 1
K Deduction 2
L Advance recovered
M Total deductions
N Net pay

Name (C2), looked up so you don't retype it:

=IF($B2="","",IFERROR(INDEX(Employees!$B:$B,MATCH($B2,Employees!$A:$A,0)),"Check ID"))
Enter fullscreen mode Exit fullscreen mode

Pro-rated basic (F2), for someone who joined mid-month or took unpaid leave:

=IF($B2="","",ROUND(INDEX(Employees!$D:$D,MATCH($B2,Employees!$A:$A,0))*E2/D2,0))
Enter fullscreen mode Exit fullscreen mode

Copy the same pattern for HRA (column E of Employees) and allowances (column F).

Working days (D2): if you pay on calendar days,

=DAY(EOMONTH(A2,0))
Enter fullscreen mode Exit fullscreen mode

If you pay on working days with Sunday off, use =NETWORKDAYS.INTL(A2,EOMONTH(A2,0),11). The 11 means only Sunday is a weekend.

Gross, deductions, net:

I2: =IF($B2="","",F2+G2+H2)
M2: =IF($B2="","",N(J2)+N(K2)+N(L2))
N2: =IF($B2="","",I2-M2)
Enter fullscreen mode Exit fullscreen mode

Leave J and K as typed values, or as a formula once you've confirmed the rule that applies to you. Putting a rate in a separate Settings cell (e.g. Settings!B2) is better than burying it inside formulas, because when it changes you update one cell.

Sheet 3: the Slip

Lay out a printable A4 slip with labels on the left. Put two input cells at the top:

  • C3: Month (pick from a dropdown of register months)
  • C4: Emp ID

Every other value uses one lookup on two conditions. For example Gross:

=IFERROR(INDEX(Register!$I:$I,MATCH(1,(Register!$A:$A=$C$3)*(Register!$B:$B=$C$4),0)),"")
Enter fullscreen mode Exit fullscreen mode

In Excel 365 or 2021 this works as typed. In older Excel, confirm it with Ctrl+Shift+Enter. Repeat for each field, changing only the first range.

For a simpler option, use SUMIFS for number fields:

=SUMIFS(Register!$N:$N,Register!$A:$A,$C$3,Register!$B:$B,$C$4)
Enter fullscreen mode Exit fullscreen mode

Net pay in words

Excel has no built-in "number to words" for Indian formats. Either type it, or keep a short line like "Net pay: ₹ 18,450 only" using:

="Net pay: ₹ "&TEXT(C20,"#,##,##0")&" only"
Enter fullscreen mode Exit fullscreen mode

The #,##,##0 format gives lakh-style grouping.

Printing

Select the slip area, Page Layout > Print Area > Set Print Area, then Fit to 1 page. Change C4, print or File > Export > PDF, repeat. For ten staff that's a couple of minutes.

Monthly summary

A pivot table on the Register (Rows: Month; Values: Sum of Gross, Sum of Total deductions, Sum of Net pay) gives you the monthly payroll cost for your books.

Overtime and one-off payments

Bonuses, overtime and reimbursements don't belong in the Employees sheet because they change each month. Add columns to the Register instead, for example Overtime hours, Overtime rate and Other earnings, and include them in Gross:

I2: =IF($B2="","",F2+G2+H2+N(O2)*N(P2)+N(Q2))
Enter fullscreen mode Exit fullscreen mode

Label them clearly on the slip so the employee can see where the extra amount came from.

Common mistakes

  1. Overwriting last month's rows. Add new rows each month; the register is your history.
  2. Days paid more than working days. Add Data Validation on E: whole number between 0 and =D2.
  3. Rates hard-coded in formulas. Keep them in a Settings sheet.
  4. Duplicate rows for the same person and month. A COUNTIFS check column, =COUNTIFS(A:A,A2,B:B,B2)>1, catches them. This article was drafted with AI tools. It's general spreadsheet guidance, not tax, payroll or legal advice.

Top comments (0)