Salary Sheet Format in Excel (with EPF, ESI, PT, TDS)

Q: What is the difference between gross and net salary?

Gross salary is the total of all earnings before deductions. Net salary (in-hand) is gross minus EPF, ESI, professional tax, TDS and any LOP or advances.

Q: Should EPF be calculated on basic only or gross?

EPF is calculated on **Basic + DA** (plus retaining allowance), not full gross. Many employers cap it on the ₹15,000 wage ceiling.

Q: How do I add a new employee to the salary sheet?

Insert a row and copy the formula columns down — because all rates live on the Settings sheet, the new row computes automatically.

Q: Can one Excel file handle the whole month's payroll?

Yes. One salary sheet computes gross, all statutory deductions and net pay for every employee, and a totals row reconciles against your payout. ---

⬇ Download the free salary sheet (EPF/ESI) template (Excel) — no sign-up

Last updated: 27 June 2026 · Formulas tested in Excel 2010 and later.

A salary sheet in Excel is one table where each row is an employee and columns hold earnings (Basic, DA, HRA, allowances), statutory deductions (EPF, ESI, professional tax, TDS) and the resulting net pay. Gross is the sum of earnings; net is gross minus deductions. Once the formula columns are set, you copy them down for every employee and the whole month reconciles in a single Net Pay column.

Key takeaways

A salary sheet has three blocks: earnings → gross, deductions, and net pay.
Gross salary = Basic + DA + HRA + Conveyance + Special allowance.
Net pay = Gross − (EPF + ESI + PT + TDS + any LOP/advance).
Use SUM for gross, MIN/IF for capped deductions, and VLOOKUP for state-based professional tax.
Keep statutory rates (12%, 0.75%, slab tables) in a separate Settings sheet so one change updates every row.

Fact box. The standard salary-sheet identity is Net Pay = Gross Salary − Total Deductions, where Gross = sum of all earnings and Total Deductions = EPF + ESI + professional tax + TDS + any loss-of-pay or advances. (Source: standard Indian payroll practice.)

What columns should a salary sheet have?

A complete salary sheet has employee identifiers, attendance, earnings, deductions and net pay. Here is the recommended column order:

#	Column	Purpose
1	Emp ID / Name	Identifier
2	Paid Days / LOP	From attendance
3	Basic	Earning
4	DA	Earning
5	HRA	Earning
6	Conveyance	Earning
7	Special Allowance	Earning
8	Gross Salary	`=SUM(C2:G2)`
9	EPF (employee)	Deduction
10	ESI (employee)	Deduction
11	Professional Tax	Deduction
12	TDS	Deduction
13	Total Deductions	`=SUM(I2:L2)`
14	Net Pay	`=H2-M2`

What are the formulas for each column?

Each deduction column has a single formula you copy down. Assume Gross is in column H and Basic+DA in a helper cell (say BasicDA).

Column	Formula	Note
Gross (H2)	`=SUM(C2:G2)`	Sum of earnings
EPF employee (I2)	`=ROUND(MIN(BasicDA,15000)*12%,0)`	Drop the `MIN` if you contribute on actual wages
ESI employee (J2)	`=IF(H2<=21000,ROUND(H2*0.75%,0),0)`	Only while gross ≤ ₹21,000
Professional tax (K2)	`=VLOOKUP(State,PT_Table,2,TRUE)`	State slab lookup
TDS (L2)	`=ROUND(AnnualTax/12,0)`	From the TDS sheet
Total deductions (M2)	`=SUM(I2:L2)`
Net pay (N2)	`=H2-M2`	In-hand

Fact box. Putting all statutory rates — the 12% EPF rate, the ₹15,000 ceiling, the 0.75% ESI rate, the ₹21,000 threshold and the PT slab table — on a separate Settings sheet means a single edit updates every employee row, instead of editing hundreds of formulas when a rate changes. (Source: Excel best practice.)

How do I handle loss of pay (LOP)?

Loss of pay reduces earnings proportionally to unpaid days. Pro-rate each earning by paid days ÷ total days in the month.

For Basic in a 30-day month with PaidDays: =ROUND(Monthly_Basic*PaidDays/30,0)

Apply the same ratio to DA, HRA and allowances. Compute deductions on the reduced (actual) figures, not the full monthly salary, so EPF and ESI fall correctly when an employee has LOP.

How to build it step by step

Create a Settings sheet with rates: EPF 12%, ceiling 15000, ESI 0.75%, threshold 21000, and a PT slab table per state.
On the Salary sheet, enter employee rows and earnings columns.
Add the Gross formula (=SUM(...)), then copy down.
Add EPF, ESI, PT and TDS formulas referencing the Settings sheet, copy down.
Add Total Deductions and Net Pay columns.
Add a totals row at the bottom (=SUM(...)) to reconcile against your bank transfer.

How Ankeshan helps: Ankeshan ships this salary sheet pre-built inside Excel with EPF, ESI, PT and TDS columns already wired and rates kept current — you fill in names and attendance, it computes net pay. (Launching soon — join the waitlist for the free template, no sign-up.)

Frequently asked questions

What is the difference between gross and net salary? Gross salary is the total of all earnings before deductions. Net salary (in-hand) is gross minus EPF, ESI, professional tax, TDS and any LOP or advances.

Should EPF be calculated on basic only or gross? EPF is calculated on Basic + DA (plus retaining allowance), not full gross. Many employers cap it on the ₹15,000 wage ceiling.

How do I add a new employee to the salary sheet? Insert a row and copy the formula columns down — because all rates live on the Settings sheet, the new row computes automatically.

Can one Excel file handle the whole month's payroll? Yes. One salary sheet computes gross, all statutory deductions and net pay for every employee, and a totals row reconciles against your payout.

Sources

EPFO — epfindia.gov.in (EPF rate, wage ceiling).
ESIC — esic.gov.in (ESI rate, threshold).
State professional-tax departments (PT slabs).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.