印度 Excel 薪金單格式：從單一登記表填寫並列印

If you run a small shop, office or studio in India, staff will sooner or later ask for a salary slip, for a bank loan, a rental agreement or a visa file. You don't need payroll software for that. A clear salary slip format in Excel, filled automatically from a simple monthly register, does the job. This guide focuses on the slip itself: what to put on it, how to lay it out and how to fill it with formulas.

Note: which deductions apply (PF, ESI, professional tax, TDS and so on) depends on your state, headcount and current rules. This article covers spreadsheet layout only. It is not tax, payroll or legal advice; check the rules with an accountant or the official sources.

What a typical salary slip shows

Most slips have the same five blocks:

1. Header: business name, address, "Salary slip for October 2026"
2. Employee details: name, employee ID, designation, department, date of joining, bank account (last 4 digits only), PAN or UAN if you record them
3. Attendance: working days in the month, days paid, leave taken
4. Earnings and deductions: two columns side by side
5. Net pay: in figures and in words, plus a line such as "This is a computer-generated slip"

Step 1: The data the slip reads from

Keep two sheets:

• Employees: A = Emp ID, B = Name, C = Designation, D = Department, E = Date of joining, F = Bank (last 4)
• Register: one row per employee per month. A = Month (first day, e.g. 01-10-2026), B = Emp ID, C = Working days, D = Days paid, E = Basic, F = HRA, G = Other allowances, H = Overtime/bonus, I = Gross, J–L = deductions, M = Total deductions, N = Net pay

The slip never holds numbers of its own. It only looks them up, so a mistake fixed in the register fixes the slip.

Step 2: Lay out the slip

On a Slip sheet, use columns B to E on an A4 portrait page:

• B2:E2 merged: =Settings!B2 (business name), bold, centred
• B3:E3 merged: address
• B4:E4 merged: ="Salary slip for "&TEXT($C$6,"mmmm yyyy")

Two inputs at the top:

• C6: Month (a dropdown of months from the register)
• C7: Emp ID (a dropdown from the Employees list)

Then the employee details in rows 9–12, labels in B and D, values in C and E.

Step 3: Pull employee details

C9:  =IFERROR(INDEX(Employees!$B:$B,MATCH($C$7,Employees!$A:$A,0)),"")
E9:  =IFERROR(INDEX(Employees!$C:$C,MATCH($C$7,Employees!$A:$A,0)),"")
C10: =IFERROR(INDEX(Employees!$D:$D,MATCH($C$7,Employees!$A:$A,0)),"")
E10: =IFERROR(TEXT(INDEX(Employees!$E:$E,MATCH($C$7,Employees!$A:$A,0)),"dd-mm-yyyy"),"")
C11: =IFERROR("XXXX"&INDEX(Employees!$F:$F,MATCH($C$7,Employees!$A:$A,0)),"")

Step 4: Pull the month's figures

Each money field needs a lookup on two conditions: the month and the employee. SUMIFS is the simplest way and works in older Excel too:

=SUMIFS(Register!$E:$E,Register!$A:$A,$C$6,Register!$B:$B,$C$7)

That gives Basic. Change only the first range for each field: $F:$F for HRA, $G:$G for allowances, $H:$H for overtime, $J:$J to $L:$L for deductions, $D:$D for days paid.

Lay earnings in B:C and deductions in D:E on the same rows, so the slip reads across:

Name the deduction rows to match what you actually deduct. Leave out rows that don't apply to you rather than showing zeros.

Step 5: Net pay in figures and words

C21: =C19-E19
B22: ="Net pay: Rupees "&TEXT(C21,"#,##,##0")&" only"

The #,##,##0 format gives Indian lakh-style grouping (1,25,000). Excel has no built-in function for Indian number words, so type the words if you need them in full.

Step 6: Catch blanks before printing

Add a check cell outside the print area:

=IF(COUNTIFS(Register!$A:$A,$C$6,Register!$B:$B,$C$7)=0,"No register row for this month",
 IF(COUNTIFS(Register!$A:$A,$C$6,Register!$B:$B,$C$7)>1,"Duplicate rows: check register","OK"))

Use Conditional Formatting to turn it red when it isn't "OK".

Step 7: Print a slip for each person

1. Select B2:E24, Page Layout > Print Area > Set Print Area.
2. Page Setup > Fit to 1 page, portrait, A4.
3. Pick the month in C6, then change C7 for each employee and File > Export > Create PDF.
4. Name files like Slip_2026-10_E003.pdf.

For a few staff this takes a couple of minutes a month.

Tips

• Protect the slip sheet (Review > Protect Sheet, leaving C6 and C7 unlocked) so formulas don't get typed over.
• Don't store full bank or ID numbers in a file you email around.
• Keep old register rows. They're your history if anyone asks for a past slip. This article was drafted with AI tools. It's general spreadsheet guidance for Excel, not tax, payroll or legal advice.