Excel 薪金單格式教學：從單一月度登記表自動填寫及列印薪金單

Written with AI assistance.

Staff, contractors and part-timers often ask for a payslip, for a loan, a rental application or their own records. 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: what to put on it, how to lay it out and how to fill it with formulas.

Note: which deductions apply, what a payslip must show and what you must report depend on where you operate. This article covers spreadsheet layout only. It is not tax, payroll, legal or accounting advice; check the rules with a qualified adviser.

What a typical salary slip shows

1. Header: business name, address, "Salary slip for October 2026"
2. Employee details: name, employee ID, job title, start date, bank account (last 4 digits only)
3. Attendance: days in the month, days paid
4. Earnings and deductions: two columns side by side
5. Net pay, plus a line such as "This is a computer-generated slip"

Step 1: The data the slip reads from

Keep three sheets:

• Settings: B1 = business name, B2 = address, B3 = currency label, B4 = name of deduction 1 (for example "Tax withholding"), B5 = name of deduction 2 (for example "Pension")
• Employees: A = Emp ID, B = Name, C = Job title, D = Start date, E = Bank (last 4)
• Register: one row per employee per month. A = Month (first day), B = Emp ID, C = Days in month, D = Days paid, E = Base pay, F = Allowances, G = Other earnings, H = Gross, I = Deduction 1, J = Deduction 2, K = Other deductions, L = Total deductions, M = 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 a portrait page:

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

Two inputs at the top:

• C6: Month (type the first day of the month, or use a dropdown of register months)
• C7: Emp ID (a dropdown from the Employees list)

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(TEXT(INDEX(Employees!$D:$D,MATCH($C$7,Employees!$A:$A,0)),"dd mmm yyyy"),"")
C11: =IFERROR("XXXX"&INDEX(Employees!$E:$E,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 base pay. Change only the first range for each field: $F:$F for allowances, $G:$G for other earnings, $I:$I to $K:$K for deductions, $D:$D for days paid.

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

Leave out rows that don't apply to you rather than showing zeros.

Step 5: Net pay

C21: =C18-E18
B22: ="Net pay: "&TEXT(C21,"#,##0.00")&" "&Settings!B3

C18 is the gross cell and E18 the total deductions cell in the layout above. Adjust the references to match your rows.

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, then Page Layout > Print Area > Set Print Area.
2. Page Setup > Fit to 1 page, portrait.
3. Pick the month in C6, then change C7 for each employee and use 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.

Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.