Employee Attendance and Salary Sheet in Excel (2026): Formulas, Example and When to Switch

Thousands of Indian businesses run payroll from an Excel sheet: one tab for attendance, one for salary, formulas connecting them. Done well, it works for a small team. Done badly — and most sheets drift into "done badly" over time — it produces wrong salaries, broken formulas and hours of checking every month.

This guide shows you how to build an employee attendance and salary sheet in Excel with the right formulas, a worked example, the mistakes to avoid, and the clear signs that it's time to move from Excel to payroll software.

Updated 30 September 2026.

The short answer

• A good Excel payroll workbook has three sheets: an employee master, a monthly attendance sheet, and a salary sheet.
• Key formulas: COUNTIF for attendance codes, paid days, prorated components, PF on earned basic, ESI on earned gross, and net pay.
• Use one row per employee and the same employee code on every sheet.
• Protect formulas and keep a copy of every month's final file.
• Move to software when you pass 15–20 employees, PF and ESI apply, or payroll takes more than a day.

Sheet 1: Employee master

Column	Example
Employee code	EMP-007
Name	Sunita Rao
Department	Accounts
Date of joining	01-06-2024
Basic	15,000
HRA	6,000
Special allowance	4,000
Gross	=SUM(basic:special) → 25,000
PF applicable (Y/N)	Y
ESI applicable (Y/N)	N
UAN / ESIC IP number
Bank account

Keep this sheet as the single source of salary data. Every other sheet looks up from it.

Sheet 2: Monthly attendance

One row per employee, one column per day (1–31), using codes:

• P present, A absent, HD half day, CL/SL/EL paid leave, LOP leave without pay, WO weekly off, H holiday.

Totals at the end of each row:

Column	Formula (row 5, days in columns D:AH)
Present	=COUNTIF(D5:AH5,"P")
Half days	=COUNTIF(D5:AH5,"HD")
Paid leave	=COUNTIF(D5:AH5,"CL")+COUNTIF(D5:AH5,"SL")+COUNTIF(D5:AH5,"EL")
Weekly offs + holidays	=COUNTIF(D5:AH5,"WO")+COUNTIF(D5:AH5,"H")
Unpaid days	=COUNTIF(D5:AH5,"A")+COUNTIF(D5:AH5,"LOP")+0.5*[half days]
Days in month	=DAY(EOMONTH(month_start,0))
Paid days	=[days in month]−[unpaid days]

Sheet 3: Salary calculation

Use VLOOKUP or XLOOKUP to bring in salary components from the master, and paid days from the attendance sheet.

Column	Formula
Basic earned	=ROUND(basic × paid_days ÷ days_in_month, 0)
HRA earned	=ROUND(hra × paid_days ÷ days_in_month, 0)
Special earned	=ROUND(special × paid_days ÷ days_in_month, 0)
Gross earned	=SUM(basic_earned:special_earned)
Employee PF	=IF(pf="Y", ROUND(MIN(basic_earned, 25000) × 12%, 0), 0)
Employee ESI	=IF(esi="Y", ROUNDUP(gross_earned × 0.75%, 0), 0)
Professional Tax	As per your state's slab
TDS	As calculated
Other deductions	Advances, recoveries
Total deductions	=SUM(...)
Net pay	=gross_earned − total_deductions

The PF formula above assumes your company caps contributions at the ₹25,000 ceiling (from 17 September 2026). Adjust it to your policy.

Worked example

Sunita (gross ₹25,000, basic ₹15,000) in September (30 days):

• Attendance: 20 P, 1 HD, 2 CL, 5 WO/H, 2 LOP
• Unpaid days = 2 + 0.5 = 2.5
• Paid days = 30 − 2.5 = 27.5

Item	Calculation	Amount
Basic earned	15,000 × 27.5 ÷ 30	₹13,750
HRA earned	6,000 × 27.5 ÷ 30	₹5,500
Special earned	4,000 × 27.5 ÷ 30	₹3,667
Gross earned		₹22,917
Employee PF	12% of ₹13,750	₹1,650
Employee ESI	Not applicable (gross above ₹21,000)	₹0
Professional Tax	State-dependent	₹200
Net pay		₹21,067

Common Excel payroll mistakes

1. Hard-typed numbers overwriting formulas.
2. Copying last month's file and forgetting to update days in the month.
3. Loss of pay applied only to basic.
4. PF on full basic instead of basic earned.
5. ESI eligibility re-checked every month instead of per contribution period.
6. Broken lookups when a new employee is inserted in the middle.
7. No version history — nobody knows which file was final.
8. Payslips typed separately from the salary sheet.

Tips if you're staying on Excel

• Lock formula cells and protect sheets.
• Use Excel Tables so formulas extend automatically.
• Use data validation for attendance codes.
• Keep a final, read-only copy of each month.
• Reconcile totals with your bank transfer and PF/ESI challans.

Signs it's time to move to software

• More than 15–20 employees.
• PF and ESI apply, and you prepare ECR and ESIC files by hand.
• Attendance comes from a biometric machine and is retyped.
• Payroll takes more than a day.
• You've corrected payslips after sending them.
• Only one person understands the workbook.

What changes with payroll software

Task	Excel	Payroll software
Attendance	Typed codes	From biometric or mobile check-in
Leave	Typed codes	Approved in the app, balances tracked
Paid days	COUNTIF formulas	Calculated automatically
Proration	Formulas per component	Automatic, including joiners and leavers
PF and ESI	Formulas, then manual return files	Calculated, with ECR and ESIC return generated
Payslips	Mail merge or manual	Generated and emailed in one run
History	Files in folders	Every month stored

How PeopleVio replaces the Excel workbook

• Bulk import your employee master from Excel.
• Attendance from biometric machines or geofenced mobile check-in; leave and regularisation in the app.
• Payroll with paid days, proration, PF and ESI on earned wages, TDS, and custom earnings and deductions.
• Payslips emailed to every employee.
• PF ECR and ESIC return files generated each month.

Limitations: no Professional Tax calculation (add it as a deduction), proration uses calendar days, and there's no shift rostering.

Active employees	Monthly billing	Yearly billing (per month)
15	₹885	₹735
30	₹1,470	₹1,230
60	₹2,940	₹2,460

Prices exclude GST. See the pricing page.

Frequently asked questions

How do I make an employee attendance and salary sheet in Excel? Create an employee master, a monthly attendance sheet with codes and COUNTIF totals, and a salary sheet that prorates each component by paid days and calculates PF, ESI and net pay.

What is the formula for salary calculation in Excel? Earned component = ROUND(monthly component × paid days ÷ days in month, 0). Net pay = gross earned − deductions.

How do I calculate PF in Excel? =ROUND(MIN(basic_earned, 25000) × 12%, 0) if you cap at the ₹25,000 ceiling; otherwise 12% of basic earned.

How do I calculate ESI in Excel? =ROUNDUP(gross_earned × 0.75%, 0) for the employee share, for employees covered in the contribution period.

When should I stop using Excel for payroll? Usually beyond 15–20 employees, or once PF, ESI and attendance-based pay make the workbook slow and error-prone.

The bottom line

A well-built Excel sheet can run payroll for a small team. But every formula you add is one more thing to break. When payroll takes more than a day or errors start appearing, move the same logic into software that does it automatically.

See PeopleVio's pricing or book a demo.

Related reading: Why founders move from Excel to HRMS · Attendance register format · How to calculate salary per day · PF calculation on salary