Salary Sheet in Excel India: Format, PF at ₹25,000, ESI

A standard salary sheet in Excel India offices use needs one row per employee with fixed pay, paid days, earned basic, HRA and allowances, gross pay, PF, ESI, professional tax, TDS, other deductions and net pay. Prorate each component as fixed pay × paid days ÷ days in month and round to whole rupees. For PF, use 12% of PF wages, and note the EPF wage ceiling rose from ₹15,000 to ₹25,000 per month from 17 September 2026; ESI still applies to employees with gross wages up to ₹21,000, at 0.75% (employee) and 3.25% (employer). Keep every rate in a settings cell that HR confirms.

Salary sheet in Excel India: the columns and formulas

Keep three sheets: PaySettings (month start date, PF rate, PF ceiling, ESI rates and ceiling, each a named cell), Employees (a table named Emp with Emp ID, Name, Department, Basic, HRA, Other Allowance, monthly PT amount) and Salary (a table named Pay). In Pay, type these in the first row; the table copies them down:

• Name: =XLOOKUP([@[Emp ID]],Emp[Emp ID],Emp[Name],"Check ID")
• Days in Month: =DAY(EOMONTH(PayMonth,0))
• Paid Days: from your attendance sheet with XLOOKUP, or typed
• Earned Basic: =ROUND(XLOOKUP([@[Emp ID]],Emp[Emp ID],Emp[Basic])*[@[Paid Days]]/[@[Days in Month]],0) (same pattern for HRA and Other)
• Gross: =[@[Earned Basic]]+[@[Earned HRA]]+[@[Earned Other]]
• PF Wages: =IF(ApplyCeiling="Yes",MIN([@[Earned Basic]],PFCeiling),[@[Earned Basic]])
• PF: =ROUND([@[PF Wages]]*PFRate,0)
• ESI (employee): =IF([@Gross]<=ESICeiling,[@Gross]*ESIRate,0), rounded as HR advises under ESIC’s rules
• Total Deductions: PF + ESI + PT + advance recovery + TDS
• Net Pay: =[@Gross]-[@[Total Deductions]]

On Excel 2019 or earlier, replace XLOOKUP with INDEX and MATCH, for example =INDEX(Emp[Basic],MATCH([@[Emp ID]],Emp[Emp ID],0)).

PF at the new ₹25,000 ceiling and ESI: what changes in the sheet

The Ministry of Labour and Employment raised the EPF wage ceiling to ₹25,000 per month by Notification S.O. 5109(E) dated 17 September 2026, effective from that date. For employees whose PF wages lie between ₹15,000 and ₹25,000, mandatory contributions now run on the higher wage, so their take-home pay falls slightly while retirement savings rise. In the sheet, you only change one cell: PFCeiling from 15000 to 25000. Whether a particular employee’s contribution is restricted to the ceiling, or paid on full PF wages, is a policy and legal question for HR.

ESI is separate. The ESI wage ceiling was not changed by the EPF notification and remains ₹21,000 per month gross. Contributions are 0.75% from the employee and 3.25% from the employer. A rise in the ESI ceiling has been discussed but, as on 29 October 2026, not notified, so keep it as an input cell.

Also add a check for the 50% wage rule under the four Labour Codes, in force from 21 November 2025. If excluded components (such as HRA and certain allowances) exceed half of total remuneration, the excess counts as wages for social security. A flag column: =IF(([@[Earned HRA]]+[@[Earned Other]])>[@Gross]/2,"Review wage base",""), if HRA and Other are your excluded components. HR decides which components are excluded.

Worked examples

Employee A, partial month. Basic ₹20,000, HRA ₹8,000, Other ₹4,000; October 2026 has 31 days; paid days 29.

• Earned Basic 20,000 × 29 ÷ 31 = 18,709.68, rounded 18,710; HRA 7,484; Other 3,742; Gross 29,936.
• PF wages 18,710 (below ₹25,000); PF 12% = 2,245.
• Gross above ₹21,000, so no ESI. Advance recovery 1,000. Net pay 26,691.

Employee B, ESI applies. Basic ₹10,000, HRA ₹4,000, Other ₹4,000, full month: Gross ₹18,000. PF 12% of 10,000 = 1,200. ESI employee 0.75% of 18,000 = 135; employer ESI 3.25% = 585 (shown in a separate employer-cost column, not deducted). Net pay 16,665.

Work one employee out on paper each month you change anything. If the paper and the sheet agree, trust the template.

Checks before you pay

1. No row shows “Check ID”.
2. Paid Days never exceed Days in Month (highlight with a conditional formatting rule).
3. Head count matches: =ROWS(Pay[Emp ID])=ROWS(Emp[Emp ID]).
4. Total Net Pay equals the bank transfer file total.
5. A pivot of Gross and Net by department matches last month, except for known changes.

Keep the Employees sheet, with bank details, restricted. Generate payslips with XLOOKUP from a dropdown of Emp IDs, save each as PDF and send each employee only their own. Never paste the salary sheet into an AI tool to “check” it; describe the columns instead.

On exit, the Code on Wages, 2019 requires wages to be paid within two working days of removal, dismissal, retrenchment or resignation (s.17(2)), so keep a separate full and final settlement row ready.

Frequently asked questions

What is the format of a salary sheet in Excel India offices use?

One row per employee with Emp ID, name, paid days, earned basic, HRA and allowances, gross, PF, ESI, professional tax, TDS, other deductions and net pay. Keep rates in a settings sheet and fixed pay in an employee master, and use formulas so nothing is typed by hand.

How do I calculate PF in Excel after the ₹25,000 ceiling?

Set PF wages as the lower of earned basic (or your PF wage base) and ₹25,000 where the ceiling applies: =MIN(Basic,25000). Then PF is =ROUND(PFWages*12%,0). The new ceiling applies from 17 September 2026.

Has the ESI limit also increased to ₹25,000?

No. The September 2026 notification changed only the EPF wage ceiling. The ESI ceiling remains ₹21,000 gross per month as on 29 October 2026, with 0.75% employee and 3.25% employer contributions.

How do I calculate salary for absent days in Excel?

Multiply each fixed component by paid days and divide by days in the month, then round: =ROUND(Basic*PaidDays/DaysInMonth,0). If your policy uses a fixed 26 or 30-day month, use that number instead.

Quick quiz

Sources

• Ministry of Labour and Employment, Notification S.O. 5109(E) dated 17 September 2026 (EPF wage ceiling ₹25,000)
• Code on Social Security, 2020 (definition of wages; EPF and ESI chapters), in force from 21 November 2025
• Code on Wages, 2019, s.17(2) (payment on removal, dismissal, retrenchment or resignation)
• Employees’ State Insurance contribution rates: 0.75% employee, 3.25% employer; wage ceiling ₹21,000

Build the full salary sheet and payslip in the Excel and AI for Office Work certificate course.

This page is for education and general information, not legal advice for a specific case. Law as on 29 October 2026.