Payroll in Excel: Step-by-Step with Formulas (Template)

Excel is still how many Indian small businesses, start-ups and accountants run payroll. It costs nothing extra, everyone knows it, and a well-built workbook handles PF, ESI, professional tax and TDS for a small team. A badly built one, with rates typed into formulas and no checks, is how payroll errors happen. This guide shows how to build a monthly payroll workbook step by step, with the exact formulas, a worked example for three employees, and the controls that keep it reliable. If you only need a simple one-page salary sheet, see our salary sheet format; for the overall monthly cycle, our payroll process guide.

The payroll formula

Every payroll workbook follows the same logic:

• Gross pay = sum of earnings (basic, DA, HRA, allowances, overtime, arrears) for the days paid.
• Net pay = gross pay − employee deductions (PF, ESI, professional tax, TDS, loan or advance recovery).
• Employer cost = gross pay + employer PF + EDLI + PF administration charges + employer ESI.

Excel's job is to apply this the same way for every employee, every month, with statutory rules built into formulas rather than typed by hand.

Step 1: Plan the workbook

Format the Employees, Attendance and Payroll ranges as Excel tables (Ctrl+T) so formulas extend automatically when you add a row, and give the key settings names (Formulas > Define Name), such as PF_Rate and PF_Ceiling, so formulas are readable.

Step 2: Build the Settings sheet

Rates come from EPFO and the ESIC contribution page; our PF wage ceiling guide explains the ₹25,000 limit, and the professional tax guide links to each State's slabs. When a rule changes, you change one cell, not hundreds of formulas.

Step 3: Employees and attendance

The Employees sheet holds the monthly rate of each component. For our example:

The Attendance sheet for October 2026 records paid days: Riya 31, Arjun 31 and Meena 29 (two days of loss of pay). Keep loss of pay as a separate column so the payslip can show it. Our attendance sheet template can feed this sheet.

Step 4: Earned salary and gross pay

In the Payroll sheet, bring in each employee's rate and paid days with XLOOKUP, then prorate. With the employee ID in column A:

Paid days:   =XLOOKUP([@ID], Attendance[ID], Attendance[Paid Days])
Earned basic: =ROUND(XLOOKUP([@ID], Employees[ID], Employees[Basic]) * [@[Paid Days]] / Days_In_Month, 0)
Gross:        =SUM([@[Earned Basic]]:[@[Earned Special]]) + [@Overtime] + [@Arrears]

Overtime and arrears are covered below; overtime is part of gross pay but is excluded from wages for PF.

Repeat the earned formula for HRA, conveyance and special allowance. In older Excel without XLOOKUP, use INDEX(Employees[Basic], MATCH([@ID], Employees[ID], 0)). Whether you divide by calendar days, 30 or 26 is a company policy; see salary on 26 or 30 days and our pro-rata salary guide. Meena's earned components come to ₹11,226 + ₹4,490 + ₹1,497 + ₹6,174 = ₹23,387.

Step 5: Wages for PF under the Labour Codes

Under the Labour Codes, PF is calculated on wages: all pay except listed exclusions such as HRA and conveyance, with any excluded amount above 50% of total pay added back. With the excluded allowances in one column:

Excluded:  =[@[Earned HRA]] + [@[Earned Conveyance]]
Wages:     =[@Gross] - [@Excluded] + MAX(0, [@Excluded] - 50%*[@Gross])
PF wages:  =MIN(ROUND([@Wages],0), PF_Ceiling)

For Riya, wages are ₹32,000 (gross ₹40,000 less HRA ₹8,000) and PF wages are capped at ₹25,000. This is a simplified version of the wage definition; our guide to salary structure under the Labour Codes explains the full rule and the items that count. Some employers pay PF on full wages above the ceiling; if you do, remove the MIN.

Step 6: Deduction formulas

Employee PF:  =ROUND([@[PF Wages]] * PF_Rate, 0)
ESI (employee): =IF(XLOOKUP([@ID],Employees[ID],Employees[Monthly Gross]) <= ESI_Limit, ROUNDUP([@Gross]*ESI_Emp, 0), 0)
Professional tax: =XLOOKUP([@Gross], PT[Salary From], PT[PT], 0, -1)
TDS:          =XLOOKUP([@ID], TDS[ID], TDS[Monthly TDS], 0)
Net pay:      =[@Gross] - [@[Employee PF]] - [@ESI] - [@PT] - [@TDS] - [@Recovery]

• ESI is tested on the employee's rate of pay against the ₹21,000 limit and rounded up to the next rupee. ESI coverage also continues to the end of a contribution period once an employee is covered, so keep an ESI-covered flag in the Employees sheet rather than relying only on this month's pay. This example tests ESI on gross pay. The Labour Ministry's view is that ESI wages follow the Labour Codes wage definition; on that basis Meena (wages ₹18,600 at her full rate) would be covered, and Arjun's ESI would be on ₹14,400. Pick one basis with your adviser and use an ESI Wages column in the formula; see our ESI guide.
• Professional tax uses an approximate-match lookup on the State's slab table (match mode -1 returns the slab at or below the salary). In Karnataka, ₹200 applies from ₹25,000, and ₹300 in February; add a month condition for that, because a slab lookup alone returns ₹200. Use VLOOKUP(Gross, PT_Table, 2, TRUE) in older Excel.
• TDS comes from a separate sheet that projects each employee's annual taxable salary, applies the chosen regime and divides the remaining tax over the remaining months; see our TDS on salary calculator.

Step 7: Employer contributions

Employer EPS:  =ROUND([@[PF Wages]] * EPS_Rate, 0)
Employer EPF:  =ROUND([@[PF Wages]] * PF_Rate, 0) - [@[Employer EPS]]
EDLI:          =ROUND([@[PF Wages]] * EDLI_Rate, 0)
Employer ESI:  =IF([@[ESI Covered]]="Yes", ROUNDUP([@Gross]*ESI_Er, 0), 0)

Use the ESI-covered flag, not the employee's ESI, because employees on very low daily wages are exempt from the employee share while the employer still pays.

PF administration charges are worked out on the total in the Summary sheet: =MAX(Admin_Min, ROUND(SUM(Payroll[PF Wages])*Admin_Rate, 0)) (with Admin_Min = 500 in Settings), because the ₹500 minimum applies to the establishment, not to each employee.

Worked example: October 2026 payroll

On gross pay, Meena's monthly rate of ₹25,000 puts her outside ESI (see the note above); her earned gross after loss of pay is ₹23,387, below the Karnataka PT threshold, so no PT is deducted this month. PF administration charges are 0.5% of ₹56,800 = ₹284, so the ₹500 minimum applies. Total employer cost is ₹81,387 + ₹4,732 + ₹2,084 + ₹284 + ₹500 + ₹585 = ₹89,572.

Overtime, arrears and recoveries

Add columns to the Payroll sheet for the items that change month to month:

Overtime:  =ROUND(XLOOKUP([@ID],Attendance[ID],Attendance[OT Hours]) * (([@[Rate Basic]]+[@[Rate DA]]) / Days_In_Month / 8) * 2, 0)
Arrears:   =XLOOKUP([@ID], Arrears[ID], Arrears[Amount], 0)
Recovery:  =MIN(XLOOKUP([@ID], Advances[ID], Advances[Instalment], 0), MAX(0, 50%*[@Wages] - [@[Employee PF]] - [@ESI] - [@PT] - [@TDS]))

• Overtime is paid at no less than twice the ordinary rate of wages. The hourly rate here is the monthly ordinary rate divided by days in the month and 8 hours; use the basis your policy or State rules require, and our overtime calculator to check it.
• Arrears from a backdated revision go into gross pay for the month paid, with PF and ESI on them; work them out with the salary arrears calculator.
• Recoveries of advances and loans reduce net pay, not gross. The MIN keeps total deductions within the Code on Wages limit of 50% of wages in a month, after PF, ESI, PT and TDS.

Joiners and leavers in the middle of the month

For an employee who joins or leaves mid-month, calculate paid days from the dates rather than typing them:

Paid days: =MAX(0, MIN(Month_End, [@[Last Day]]) - MAX(EOMONTH(Month_End,-1)+1, [@[Join Date]]) + 1) - [@LOP]

Leave the last-day column blank for current employees, and wrap it so a blank counts as the month end: IF([@[Last Day]]="", Month_End, [@[Last Day]]). Leavers' final pay, leave encashment and gratuity are part of the full and final settlement, which is easier to handle outside the monthly sheet; see our full and final settlement calculator.

Step 8: Payslip sheet

Put an employee ID in one cell (with a data-validation drop-down of IDs) and pull every figure with XLOOKUP:

Name:      =XLOOKUP($B$2, Employees[ID], Employees[Name])
Paid days: =XLOOKUP($B$2, Payroll[ID], Payroll[Paid Days])
Net pay:   =XLOOKUP($B$2, Payroll[ID], Payroll[Net Pay])

Show earnings on the left, deductions on the right, net pay in words if you like, and the employer's name, month, UAN and bank details. Print to PDF for each employee, or use our free salary slip generator and salary slip format.

Step 9: Summary sheet and the journal entry

The Summary sheet totals each column with SUM, and with SUMIFS where you need totals by department or location:

Net pay (bank file):   =SUM(Payroll[Net Pay])
PF challan:            =SUM(Payroll[Employee PF]) + SUM(Payroll[Employer EPS]) + SUM(Payroll[Employer EPF]) + SUM(Payroll[EDLI]) + Admin_Charges
ESI challan:           =SUM(Payroll[ESI]) + SUM(Payroll[Employer ESI])
Gross by department:   =SUMIFS(Payroll[Gross], Payroll[Dept], "Sales")

For our example the PF challan is ₹6,816 + ₹4,732 + ₹2,084 + ₹284 + ₹500 = ₹14,416 and the ESI challan ₹720. These totals become the month's salary entry in your books; see our salary journal entry guide or, for Tally users, the salary entry in Tally Prime. Then reconcile them with the bank and challans as described in our payroll reconciliation guide.

Step 10: Make the workbook reliable

• No typed numbers in formulas: every rate and ceiling comes from Settings.
• Lock formula cells: unlock only the input columns, then use Review > Protect Sheet.
• Data validation: drop-downs for IDs, departments and yes/no flags; limits on paid days (0 to days in month).
• Check totals: a check cell such as =SUM(Payroll[Net Pay]) + SUM(Payroll[Employee PF]) + SUM(Payroll[ESI]) + SUM(Payroll[PT]) + SUM(Payroll[TDS]) + SUM(Payroll[Recovery]) - SUM(Payroll[Gross]) should show 0; also tie the PF total to the ECR and net pay to the bank file.
• Movement check: compare this month's gross and headcount with last month and explain every difference.
• One file per month: save a copy each month (for example, Payroll_2026-10.xlsx) so past payroll never changes, and keep backups.
• Restrict access: salary data is confidential; protect the file with a password and limit who can open it.

Running payroll in Excel each month

1. Copy last month's file and rename it for the new month; change Month_End in Settings.
2. Update Employees: add joiners, mark leavers with their last day, and enter any salary revisions with arrears.
3. Update Settings if any rate, ceiling or slab changed during the month.
4. Enter attendance: paid days, loss of pay and overtime hours; enter advances, recoveries and TDS.
5. Review the Payroll sheet: check the movement against last month, the check cells and any negative or unusual net pay.
6. Get approval of the Summary, then prepare the bank transfer file from the Net Pay column.
7. Pay and file: salary by the 7th, TDS by the 7th, PF and ESI by the 15th, PT by the State's due date; see the payroll compliance calendar.
8. Issue payslips and post the journal, then reconcile and file the month's workbook as read-only.

Who should run payroll in Excel?

Excel suits businesses with a small, stable team, one or two locations and a simple salary structure, and someone who is comfortable with formulas and keeps up with PF, ESI, PT and TDS changes. It is less suitable when headcount grows, when several people need access, when employees expect online payslips and tax declarations, or when the business has many States with different professional tax and labour welfare fund rules. Many businesses start in Excel and move to software later; a clean Excel workbook makes that move easier because the data is already organised.

Excel formulas every payroll workbook uses

Common mistakes in Excel payroll

• Hard-coded rates: a formula with *12% or 25000 typed in will be missed when the rule changes.
• Copy-paste errors: one row with an overwritten formula pays one employee wrongly every month. Tables and sheet protection prevent this.
• Overwriting last month: editing the same file every month destroys the record of what was paid; keep one file per month.
• ESI on this month's earned pay: coverage depends on the rate of pay and the contribution period, not on a month with loss of pay.
• PF on basic only: under the Labour Codes, PF wages may include allowances that are not excluded, and excluded allowances above 50% of pay.
• No reconciliation: totals never matched with the bank file and challans until something goes wrong.

Payroll templates: Excel, Word or PDF?

Word and PDF payroll templates are fine for printing a payslip or a payroll summary, but they do not calculate anything, so every figure has to be worked out elsewhere. For running payroll, use a spreadsheet (Excel or Google Sheets) with formulas; use Word or PDF only for the output, such as payslips and the summary sent for approval. A good payroll template has the six sheets described above, a settings sheet for rates, protected formulas, and a check that totals agree.

Can you automate payroll in Excel?

Partly. Tables, named ranges and lookups automate most of the calculation, and Power Query can import attendance from a biometric export. Macros (VBA) can print payslips to PDF in one go. What Excel cannot do is keep up with the rules by itself: when a PF ceiling, ESI rate, PT slab or tax slab changes, someone has to update the Settings sheet. It also cannot file ECRs, ESI returns or TDS returns; you still upload those on the portals. Once a team grows beyond 20 to 30 employees, or payroll needs approvals and employee self-service, dedicated payroll software or payroll in Tally Prime is usually worth it.

Payroll in Google Sheets

The same workbook works in Google Sheets. XLOOKUP, SUMIFS, ROUND, MIN, IF and EOMONTH all exist there, and named ranges work the same way, Sheets tables support column references such as Payroll[Gross], but not this-row references like [@Gross]; use ordinary cell references for row formulas. Sheets is free and easy to share, which is also its risk: limit sharing to named people and turn off link sharing for payroll files.

Frequently asked questions

How do I calculate payroll in Excel?

Set up an employee sheet with each person's salary components, an attendance sheet with paid days, and a payroll sheet that looks up both. Prorate each component by paid days, add them to get gross pay, calculate PF, ESI, professional tax and TDS with formulas, and subtract them to get net pay. A summary sheet then totals each column for the bank transfer, challans and journal entry.

What is the formula for payroll?

The basic payroll formula is: gross pay = sum of earnings for the days paid; net pay = gross pay minus employee deductions (PF, ESI, professional tax, TDS and recoveries). Employer cost = gross pay plus employer PF, EDLI, administration charges and employer ESI.

What Excel formulas are used in payroll?

The most useful are SUM and SUMIFS for totals, ROUND and ROUNDUP for statutory rounding, MIN for wage ceilings, IF or IFS for eligibility and slabs, XLOOKUP or INDEX with MATCH to pull employee data, EOMONTH and DAY for days in the month, and NETWORKDAYS where working days matter.

Is Excel a payroll software?

No. Excel is a spreadsheet, not payroll software, but it can run payroll for a small team if the workbook is built carefully. It does not file returns, update statutory rates or keep an audit trail on its own, so the formulas must be checked and updated whenever rules change.

Is payroll in Excel free?

Excel needs a Microsoft licence, but payroll in Excel has no extra cost beyond that, and Google Sheets is free with a Google account. The same formulas work in Google Sheets, including XLOOKUP. The cost is the time to build, check and maintain the workbook.

What are the basic Excel formulas for payroll?

Seven formulas cover most payroll work: SUM for gross pay and totals, SUMIFS for totals by department, ROUND for rupee rounding, ROUNDUP for ESI, MIN for the PF wage ceiling, IF for eligibility checks, and XLOOKUP (or VLOOKUP) to pull each employee's salary, attendance and professional tax slab.

How do I create a payroll template in Excel?

Create separate sheets for settings (rates and ceilings), employees, attendance, the payroll calculation, payslips and a monthly summary. Keep rates in the settings sheet and refer to them in formulas, lock formula cells, and copy the workbook for each month so earlier months stay unchanged.

Position as of 15 November 2026. Rates and ceilings as they apply to private-sector employers; the PF wage formula is simplified, so check the Labour Codes wage definition for your salary structure. Excel is a trademark of Microsoft Corporation and Google Sheets of Google LLC. General information, not tax or legal advice.