How to Create a Salary Slip in Excel — With Indian Payroll Formulas

Every company needs salary slips. Whether you're in HR, accounting, or running a small business — building a salary slip template in Excel saves hours every month. Here's how to create one with auto-calculated components for Indian payroll.

Salary Slip Structure (Indian Standard)

A standard Indian salary slip has three sections:

┌─────────────────────────────────────────────┐
│  COMPANY NAME                               │
│  Pay Slip for: June 2026                    │
├─────────────────────────────────────────────┤
│  Employee Name:  ___    │  Emp ID:  ___     │
│  Designation:    ___    │  Department: ___  │
│  Date of Joining: ___  │  PAN: ___         │
│  Bank A/C: ___         │  UAN: ___         │
├──────────────────┬──────────────────────────┤
│  EARNINGS        │  DEDUCTIONS              │
│  Basic: ₹___     │  PF (12%): ₹___         │
│  HRA: ₹___       │  ESI (0.75%): ₹___      │
│  DA: ₹___        │  Prof Tax: ₹___         │
│  Conveyance: ₹___│  TDS: ₹___              │
│  Special: ₹___   │  Other: ₹___            │
├──────────────────┼──────────────────────────┤
│  Gross: ₹___     │  Total Deductions: ₹___ │
├──────────────────┴──────────────────────────┤
│  NET PAY: ₹___                              │
│  In Words: ___                              │
└─────────────────────────────────────────────┘

Step 1: Create the Employee Master Sheet

Create a sheet called "Master" with all employee details:

| Emp ID | Name         | Designation | Dept  | CTC    | DOJ        | PAN        | Bank A/C       | UAN          |
|--------|-------------|-------------|-------|--------|------------|------------|----------------|--------------|
| E001   | Rahul Kumar | Executive   | Sales | 360000 | 15-Mar-24  | ABCPK1234L | 1234567890     | 100123456789 |
| E002   | Priya Singh | Analyst     | MIS   | 480000 | 01-Jul-25  | XYZPS5678M | 9876543210     | 100987654321 |

Step 2: Set Up Salary Breakup Formulas

Standard Indian salary components as percentage of CTC:

Earnings (typically % of Basic):

Basic Pay    = CTC × 40%           =D2*0.40
HRA          = Basic × 50% (metro) =Basic*0.50    (or 40% for non-metro)
DA           = Basic × 10%         =Basic*0.10
Conveyance   = ₹1,600/month fixed  =1600
Special Allow= CTC - (Basic+HRA+DA+Conv+Employer PF+ESI)  ← balancing figure
Gross Salary = Basic+HRA+DA+Conv+Special

Deductions:

Employee PF  = Basic × 12%         =Basic*0.12    (max on ₹15,000)
ESI          = Gross × 0.75%       =Gross*0.0075  (if Gross ≤ ₹21,000)
Prof Tax     = ₹200/month (most states)
TDS          = Based on tax slab (calculate annually, deduct monthly)

Step 3: Build the Salary Slip Sheet

Create a sheet called "Salary Slip" with an input cell for Emp ID at the top.

Key Formulas:

Employee Name:
=VLOOKUP($B$3, Master!$A:$I, 2, FALSE)

Basic Pay (Monthly):
=VLOOKUP($B$3, Master!$A:$I, 5, FALSE) * 0.40 / 12

HRA:
=Basic_Monthly * 0.50

PF Deduction:
=MIN(Basic_Monthly, 15000) * 0.12

ESI (only if Gross ≤ 21000):
=IF(Gross_Monthly <= 21000, Gross_Monthly * 0.0075, 0)

Net Pay:
=Gross_Monthly - Total_Deductions

Step 4: Add Professional Tax Logic

Professional tax varies by state. Common rates:

=IF(Gross_Monthly <= 10000, 0,
 IF(Gross_Monthly <= 15000, 150,
 IF(Gross_Monthly <= 25000, 200, 200)))

— Maharashtra / Karnataka: ₹200/month (max ₹2,500/year)
— Tamil Nadu: varies by slab
— Delhi: No professional tax

Step 5: Number to Words (Net Pay in Words)

Indian salary slips show net pay in words. Use this helper approach:

=TEXT(Net_Pay, "₹#,##0") & " (Rupees " & [words formula] & " Only)"

For full number-to-words conversion, you'll need a custom VBA function or a helper table. Search "SpellNumber VBA" for ready-made code.

Step 6: Format & Print Setup

• Borders: Thin borders for inner cells, thick border for outer boundary
• Company header: Merge cells, bold, larger font (14pt)
• Currency format: Use Indian format: ₹#,##,##0 (File → Options → set locale to India)
• Print area: Select the salary slip → Page Layout → Print Area → Set Print Area
• Page setup: Landscape, fit to 1 page wide × 1 page tall

Step 7: Auto-Generate for All Employees

Two approaches:

Option A: Dropdown + Print (Simple)

1. Add a Data Validation dropdown in the Emp ID cell (list from Master sheet)
2. Select employee → Print → select next → Print

Option B: VBA Macro (Automated)

A simple macro loops through all employee IDs, generates each slip, and saves as PDF:

Sub GenerateAllSlips()
    Dim ws As Worksheet, empRange As Range
    Set ws = Sheets("Salary Slip")
    Set empRange = Sheets("Master").Range("A2:A" & _
        Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row)

    For Each emp In empRange
        ws.Range("B3").Value = emp.Value
        ws.ExportAsFixedFormat xlTypePDF, _
            "C:\Salary Slips\" & emp.Value & "_" & _
            Format(Date, "MMM-YYYY") & ".pdf"
    Next emp
    MsgBox "All salary slips generated!"
End Sub

Formulas Used in This Project

• VLOOKUP — pull employee data from master
• IF / nested IF — tax slab logic, ESI eligibility
• MIN — cap PF calculation at ₹15,000 basic
• ROUND — round amounts to whole rupees
• SUM — total earnings and deductions
• TEXT — format dates and currency
• CONCATENATE — build labels dynamically