Engage HRMS
☰

Salary Sheet Format in Excel

Excel templateLast reviewed Engage HR editorial team

A salary sheet is the monthly working of each employee's pay: fixed salary by component, prorated for the days actually paid, less PF, ESI, professional tax, TDS and any recoveries, to arrive at net pay. It is the calculation the payslips and the wage register are filled from.

Download in Excel

At a glance

Summary of this excel template
Document typeExcel template
Kept byEmployer
Sheets includedSalary sheet
Download formatExcel (.xlsx)
Statutory referenceCode on Wages, 2019
Last reviewed28 October 2026
Maintained byEngage HR editorial team

Salary sheet, payslip, wage register and salary breakup

Four documents that carry the same numbers for different readers.

DocumentWhat it isWho reads itHow it relates to this sheet
Salary sheetThe month's calculation for every employee, one row each.Payroll and finance, and whoever approves the bank transfer.This template.
PayslipOne employee's earnings and deductions for the month.The employee, and later their bank or next employer.Each row of the salary sheet becomes one payslip.
Wage registerThe statutory record of wages paid, in the prescribed form.The Inspector-cum-Facilitator on inspection.Filled from the salary sheet, in the columns your state's form prescribes.
Salary breakupThe fixed monthly components agreed at hiring.The candidate, in the offer and appointment letters.The Basic, HRA and Special allowance columns here are the breakup.

Standard salary sheet format

A salary sheet runs left to right in the order pay is worked out. Keeping that order lets anyone check a row by reading across it.

  1. Employee details. Serial number, employee code, name and designation. The code must match the attendance sheet, because paid days are copied across on it.
  2. Fixed salary. The monthly components from the appointment letter, here basic, HRA and special allowance, and their total as gross fixed salary. Beside them, the part of the salary excluded from PF wages, which here is the HRA.
  3. Paid days. Taken from the attendance sheet for the month.
  4. Earned salary. Each component multiplied by paid days and divided by the days in the month, rounded to the rupee, and their total as earned gross. Then PF wages: earned gross less the earned excluded part, but never less than half of earned gross.
  5. Deductions. Employee PF, employee ESI, professional tax, TDS and any other recovery such as a salary advance, with their total.
  6. Net pay. Earned gross less total deductions. This is the amount transferred to the employee.
  7. Employer contributions. Employer PF and employer ESI, and the month's cost to the company.

Rates sit on a separate Settings tab: the PF rate and wage ceiling, the ESI rates and ceiling, the days in the month and the professional tax rule. When a rate changes, it changes in one cell and every row follows.

Preview of the sheets

Salary sheet

The same five employees as the attendance sheet example, paid for October 2026 with Karnataka professional tax. The paid days are the ones that sheet produced.

S.NoEmployee codeEmployee nameDesignationBasicHRASpecial allowanceExcluded from PF wagesGross fixedPaid daysEarned basicEarned HRAEarned specialEarned grossEarned excludedPF wagesPF (employee)ESI (employee)Professional taxTDSAdvance or other recoveryTotal deductionsNet payDeductions under halfPF (employer)ESI (employer)Cost to company (month)
1E101Priya SharmaAccountant20,0008,00012,0008,00040,0003120,0008,00012,00040,0008,00032,0003,0000200003,20036,800OK3,000043,000
2E102Arjun NairSales Executive12,5005,0007,5005,00025,00028.511,4924,5976,89522,9844,59718,3872,2060002,0004,20618,778OK2,206025,190
3E103Fatima SheikhOperations Associate9,0003,6005,4003,60018,000319,0003,6005,40018,0003,60014,4001,7281350001,86316,137OK1,72858520,313
4E104Rahul VermaOperations Associate9,0003,6005,4003,60018,00019.55,6612,2653,39711,3232,2659,0581,087850001,17210,151OK1,08736812,778
5E105Sneha KulkarniHR Manager60,00024,00036,00024,0001,20,00030.559,03223,61335,4191,18,06423,61394,4513,00002007,345010,5451,07,519OK3,00001,21,064
  • Earned components are rounded to the rupee before they are added, so earned gross can differ by a rupee from prorating gross fixed directly.
  • PF runs on PF wages, not on basic. Excluded from PF wages holds the fixed components section 2(88) of the Code on Social Security leaves out, here HRA; add conveyance or any other excluded allowance to it. Special allowance is not excluded.

Shaded columns calculate on their own. The formulas, as they read in the first data row (row 4) of the sheet:

ColumnExcel formulaWhat it does
Gross fixed=E4+F4+G4The fixed monthly salary, before attendance.
Earned basic=ROUND(E4*J4/Settings!$B$2,0)Basic prorated on paid days, to the nearest rupee.
Earned HRA=ROUND(F4*J4/Settings!$B$2,0)HRA prorated the same way.
Earned special=ROUND(G4*J4/Settings!$B$2,0)Special allowance prorated the same way.
Earned gross=K4+L4+M4The month's earnings: the sum of the prorated components.
Earned excluded=ROUND(H4*J4/Settings!$B$2,0)The excluded components prorated on paid days, on the same footing as earned gross.
PF wages=MAX(N4-O4,N4/2)Earned gross less the excluded components, but never less than half of earned gross, as section 2(88) of the Code on Social Security sets wages.
PF (employee)=ROUND(MIN(P4,Settings!$B$4)*Settings!$B$3,0)12 per cent of PF wages, capped at the wage ceiling on the Settings sheet.
ESI (employee)=IF(I4<=Settings!$B$7,ROUNDUP(N4*Settings!$B$5,0),0)0.75 per cent of earned gross, rounded up, if gross fixed salary is within the ESI ceiling; otherwise nil.
Professional tax=IF(N4>=Settings!$B$8,Settings!$B$9,0)The state's monthly professional tax, from the two settings.
Total deductions=Q4+R4+S4+T4+U4Everything deducted from this month's pay.
Net pay=N4-V4Earned gross less total deductions: the amount to transfer.
Deductions under half=IF(V4>N4/2,"Over 50%","OK")Flags a row where deductions exceed half of the month's earned gross.
PF (employer)=ROUND(MIN(P4,Settings!$B$4)*Settings!$B$3,0)The employer's 12 per cent, on the same capped PF wages.
ESI (employer)=IF(I4<=Settings!$B$7,ROUNDUP(N4*Settings!$B$6,0),0)3.25 per cent of earned gross, rounded up, for covered employees.
Cost to company (month)=N4+Y4+Z4Earned gross plus the employer contributions.

What it has to contain

ElementWhy it matters
The pay month and days in the monthProration divides by the days in the month, so the same paid days give different pay in February and March. Putting the month and its days on the sheet makes every row checkable.
Fixed salary by componentPF runs on wages built from the components less the excluded ones, HRA exemption on HRA, and the Code on Wages wage test on the share of basic in total pay. A sheet that carries only a gross figure cannot work out any of them.
Paid days from the attendance recordPaid days are the only attendance input. Typing them in by hand, rather than copying from the closed attendance sheet, is where a day goes missing between the two records.
Each statutory deduction in its own columnPF, ESI and professional tax are each remitted separately, on different dates, to different authorities. A single deductions column makes the remittance impossible to reconcile.
Rates in one placeRates change. The PF wage ceiling moved from 15,000 to 25,000 rupees in September 2026. A rate typed into every formula is a rate someone forgets to update in one row.
Net pay and employer costNet pay is what the bank transfer must match, and employer cost is what finance budgets against. A sheet that stops at net pay leaves the second figure to be worked out somewhere else, differently.

How to use it

  1. Update the Settings tab for the month. Set the days in the month, and check the PF, ESI and professional tax settings against the current rates. In February, change the professional tax amount if your state charges more that month.
  2. Enter or confirm fixed salary. For each employee, enter basic, HRA and special allowance as they stand in the appointment letter or the latest revision. Add new joiners as new rows with their employee code.
  3. Copy paid days from the closed attendance sheet. Take the Paid days column from the month's attendance sheet against each employee code. Do not take it before the attendance month is closed, or late corrections will be missed.
  4. Enter TDS and recoveries. Add each employee's monthly TDS from their annual tax projection, and any advance or loan recovery due this month. These are the only deductions entered by hand.
  5. Check the rows. Look down the Deductions under half column for any row marked Over 50%, and compare the total of net pay with last month's. A large movement without a matching change in headcount or attendance needs explaining before the transfer.
  6. Pay, then issue payslips and remit. Transfer net pay within the time the Code on Wages allows, issue a payslip from each row, and remit PF, ESI, professional tax and TDS from their columns by their due dates.

How earned salary is calculated from paid days

Earned salary is each fixed component multiplied by paid days and divided by the days in the month. For an employee on 25,000 with 28.5 paid days in a 31-day month, basic of 12,500 becomes 11,492, and the other components follow the same way. Their total is the month's earned gross.

Arjun Nair in the example has a gross fixed salary of 25,000 rupees: basic 12,500, HRA 5,000 and special allowance 7,500. The attendance sheet gave him 28.5 paid days in October, which has 31 days.

Earned basic is 12,500 times 28.5 divided by 31, which is 11,491.94, rounded to 11,492. Earned HRA is 4,597 and earned special allowance is 6,895. His earned gross is 22,984.

His HRA of 4,597 is the only component excluded from PF wages, so his PF wages are 22,984 less 4,597, which is 18,387, and his PF is 12 per cent of that, 2,206. Twelve per cent of earned basic alone would have been 1,379. He is not covered by ESI, because his gross fixed salary of 25,000 is above the 21,000 ceiling, even though he earned less than that this month. His earned gross is below the 25,000 rupee Karnataka professional tax threshold, so no professional tax is due. With a salary advance recovery of 2,000, his deductions come to 4,206 and his net pay to 18,778.

Dividing by the calendar days in the month is the common method, and the one the template uses. Some employers divide by a fixed 26 or 30 days instead. Whatever the method, it belongs in the compensation policy and should be the same every month.

PF, ESI and professional tax in the sheet

Employee PF is 12 per cent of PF wages, which are earned gross less HRA and other excluded components but never under half of it, capped at 25,000 rupees a month. Employee ESI is 0.75 per cent of gross where gross fixed salary is 21,000 or less. Professional tax depends on the state.

The PF wage ceiling rose from 15,000 to 25,000 rupees a month on 17 September 2026, by notification S.O. 5109(E) under the Code on Social Security, 2020. Sneha Kulkarni in the example has PF wages of 94,451, so her PF is worked on the 25,000 cap: 3,000 from her and 3,000 from the employer. Many employers pay on the cap. Others pay on full PF wages, and for them the ceiling setting can simply be raised.

PF is worked on wages as section 2(88) of the Code on Social Security, 2020 defines them, not on basic. The definition takes all remuneration and lists what is excluded, such as HRA, conveyance, overtime and commission. Special allowance is not on the list, so it counts. Where the excluded components come to more than half of all remuneration, the excess is added back, so PF wages are never less than half of earned gross. The PF wages column does both in one formula, =MAX(earned gross less earned excluded, earned gross / 2), from the Excluded from PF wages column you fill for each employee. Arjun Nair's PF is 2,206 on that basis, against 1,379 on basic alone.

ESI coverage is decided on gross fixed salary, and the deduction is worked on the month's earned gross. Fatima Sheikh and Rahul Verma, at 18,000, are covered, and Rahul's ESI is worked on the 11,323 he earned after joining mid-month.

Professional tax uses the Karnataka rule in the example: nil below 25,000 a month, and 200 a month from there. Other states have their own slabs, listed on the professional tax pages, and Delhi, Haryana and Uttar Pradesh charge none.

Where a spreadsheet stops working

The sheet works while one person runs payroll for a few dozen people with a stable structure. It struggles with arrears and revisions back-dated across months, with full and final settlements, with employees in several states on different professional tax slabs, and with the annual tax projection behind every TDS figure.

Each of those is a side calculation feeding one cell in the sheet, and each is done somewhere else, usually in another spreadsheet. When a figure is questioned, the trail runs through several files.

That is the point at which payroll software pays for itself. It takes paid days straight from attendance, applies each state's rules, works out TDS from the declared investments, and produces the payslips, the bank file and the statutory returns from the same run.

Common mistakes

MistakeWhy it causes troubleWhat to do instead
Capping PF at 15,000 after 17 September 2026The PF wage ceiling is 25,000 rupees a month from that date. A sheet built before it still caps the employee share at 1,800 instead of 3,000, so every employee earning more than 15,000 in basic is under-deducted.Hold the ceiling in one settings cell and set it to 25,000, or remove the cap if you pay PF on full basic.
Deciding ESI coverage on the month's earned payAn employee on 22,000 with three days of loss of pay earns under 21,000 that month, and the sheet starts deducting ESI from someone who is not covered. The reverse happens with a covered employee who works overtime.Test coverage on gross fixed salary, as the template does, and let the deduction run on the month's earnings.
Prorating gross and then splitting itThe components no longer match the structure in the appointment letter, PF is worked on a basic that never appears anywhere, and the payslip cannot be reconciled with the letter.Prorate each component on paid days, round each one, then add them up.
Recovering a whole advance in one monthWith TDS and statutory deductions on top, the month's deductions can exceed half of wages, which the Code on Wages does not allow, and the employee's take-home collapses in one month.Spread the recovery so the Deductions under half column stays at OK, and record the schedule with the advance.
Overwriting formulas with typed numbersA figure typed over a formula to fix one row stops updating. Next month the sheet is copied, the typed figure is carried forward, and nobody remembers it is there.Correct the input, not the result. If a one-off adjustment is needed, put it in the recovery column or a separate arrears column.

Statutory reference

Act
Code on Wages, 2019
Key limits
The rates of PF, ESI and professional tax are set under other laws and notifications and are held on the template's Settings tab. The forms of the wage register and wage slip are prescribed by the appropriate Government; this template is the calculation they are filled from.
Provisions of the Code on Wages, 2019 referred to on this page
ProvisionWhat it says
Section 2(y), first provisoWhere payments the definition of wages otherwise excludes, such as house rent allowance and conveyance, exceed one-half of all remuneration, the excess is deemed remuneration and added to wages.
Section 17(1)Sets the time for paying wages. For a monthly wage period, wages are paid before the expiry of the seventh day of the succeeding month.
Section 18(3)Limits the total deductions in any wage period to fifty per cent of wages.
Section 20Allows a deduction for absence from duty only in proportion: it may not bear a larger proportion to the wages than the period of absence bears to the total period the employee was required to work.
Section 50Requires every employer to maintain a register of persons employed, a muster roll and wages, to display an abstract of the Code with the wage rates and pay dates, and to issue wage slips in the prescribed form.

Read the Code on Wages, 2019 in full

Frequently asked questions

How do I make a salary sheet in Excel?

Put one employee per row with their code and name, then the fixed components (basic, HRA, special allowance), paid days from attendance, the earned components prorated on paid days, the deductions (PF, ESI, professional tax, TDS, recoveries), and net pay. Keep the rates on a separate tab. The free template on this page has all of it, with formulas.

What is the formula for salary based on attendance?

Earned component = fixed monthly component x paid days / days in the month, rounded to the rupee. In Excel, =ROUND(E4*I4/31,0) for basic in E4 and paid days in I4. Do it for each component and add them up for earned gross.

How is PF calculated in a salary sheet?

Employee PF is 12 per cent of PF wages, capped at the PF wage ceiling of 25,000 rupees a month from 17 September 2026, so the most deducted is 3,000 a month. PF wages are earned gross less the components the Code on Social Security excludes, such as HRA and conveyance, but never less than half of earned gross: =MAX(gross-excluded,gross/2). Then =ROUND(MIN(PFwages,25000)*12%,0). The employer pays a matching 12 per cent.

When is ESI deducted from salary?

When the employee's gross fixed salary is 21,000 rupees a month or less. The employee pays 0.75 per cent of the month's gross and the employer 3.25 per cent. Coverage is tested on the fixed salary, so loss of pay in one month does not bring someone into ESI.

What is the difference between a salary sheet and a payslip?

The salary sheet is the employer's calculation for everyone, one row per employee. A payslip is one employee's row, issued to them. Each row of the salary sheet becomes a payslip, and you can build one from it with the free payslip generator.

Is a salary sheet the same as the wage register?

No. The wage register is a statutory record kept in the form prescribed by the appropriate Government, and its columns are set by those rules. The salary sheet is the calculation the register is filled from. Check your state's notified form for what the register must show.

How much can be deducted from salary in a month?

Under section 18(3) of the Code on Wages, 2019, total deductions in a wage period may not exceed half of wages. The template's Deductions under half column flags any employee whose deductions cross half of earned gross, which is the usual sign that an advance recovery needs spreading.

By when must salary be paid?

For a monthly wage period, before the end of the seventh day of the following month, under section 17(1) of the Code on Wages, 2019. For an employee who leaves, wages due must be paid within two working days of the last day.

Payroll without the spreadsheet

Engage runs payroll from the attendance it captures, so paid days never get copied across by hand. It applies PF, ESI and professional tax by state, works out TDS from each employee's declarations, and produces the payslips, the bank transfer file and the statutory returns from the same run.

See payroll software
WhatsApp