DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on AI-assisted

Salary slip format in Excel: a printable payslip you fill from one register

#excel #spreadsheets #productivity #tutorial

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)),"")
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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:

Earnings Amount Deductions Amount
Base pay … =Settings!B4 …
Allowances … =Settings!B5 …
Other earnings … Other deductions …
Gross =SUM(C15:C17) Total deductions =SUM(E15:E17)

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
Enter fullscreen mode Exit fullscreen mode

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"))
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)