Ravindra BagaleCourses & study guides मराठी Track your progress

Excel for HR and Payroll Teams: Attendance, Salary, Incentives and Employee Records (With Templates)

By Last updated:

This guide is for people in the hr profession who use Excel every day. You will see every common file, the formulas people actually type, and downloadable templates with made-up Indian ₹ data. Related short lessons are linked — we do not re-teach them here.

What you'll learn

  • Which roles use Excel in hr
  • Every common Excel file type for this profession
  • Sample tables with realistic ₹ examples (made-up names)
  • Formulas: Present days, Net salary, Prorata basic, Incentive and more
  • Ready templates you can download and reuse

Downloads: hr-payroll-attendance-salary.xlsx (Employee master + attendance + leave + salary + incentives + headcount)

Example data only

All names, amounts, rates and dates are made up for practice. They are not real client or employer records. Follow your organisation's own rules and software for live work.

In short

One profession → many Excel files. Each file is one clear list. Yellow cells are inputs; green cells are formulas. Start with the Roles table, then open the matching template.

Why

Some topics already have a focused lesson. Use those for depth; use this guide for the full profession picture.

What

Chapter What it covers
Attendance tracker (Daily Life) Daily present / absent / late.
Salary sheet basics Payslip style calculations if present on site.
22. KPI in All Industries (HR) HR KPIs.

How

  1. Skim the link if you need that one skill.
  2. Come back here for the full file list and templates.

1. Who uses Excel

Why

People data changes every day. Excel keeps the working copy before HRMS.

What

Role Typical Excel work
HR executive Employee master, joining/exit checklist.
Attendance desk Daily present/absent/leave marks.
Payroll executive Salary register, deductions, net pay.
HRBP / manager Headcount, attrition, incentive summary.

How

  1. Match your role.
  2. Download hr-payroll-attendance-salary.xlsx.
  3. Yellow = type; green = formula.

2. Every common Excel file

Why

If you only learn one dashboard, you miss the daily registers that feed it.

What

File What one row / sheet holds
Employee master Emp ID, name, department, DOJ, CTC, status.
Monthly attendance One column per day or Present days count.
Leave register Leave type, from, to, days, balance.
Salary register Basic, HRA, allowances, deductions, net.
Incentive sheet Target, achievement %, incentive ₹.
Headcount MIS Joiners, leavers, closing headcount.

How

  1. Pick the file that matches your role.
  2. Download the template that contains that sheet.
  3. Replace yellow sample rows with your practice data (still made-up if you share screenshots).

3. Formulas people use

Why

Typed numbers go stale. Formulas update when inputs change.

What

Name Formula What it does
Present days =COUNTIF(day_range,"P") Count Present marks.
Net salary =gross-deductions Take-home teaching total.
Prorata basic =basic*(present/paid_days) Simple month proration.
Incentive =IF(ach%>=1,rate*sales,0) Pay only if target met (example rule).
Leave balance =opening+credited-taken Running leave balance.
Headcount close =open+join-exit Closing strength.
PF teaching =MIN(basic,15000)*0.12 Simple PF on capped basic (teaching).
Attrition % =exits/avg_headcount Rough monthly attrition.

How

  1. Open the Formulas used sheet inside each template.
  2. Change only yellow cells.
  3. Watch green cells recalculate.

3. Employee master and attendance

Why

Wrong master → wrong salary credits.

What

Master list plus monthly attendance with Present count.

How

  1. Add a joiner on Employee master (yellow).
  2. On Attendance, mark P/A/L; Present days counts P.
  3. Status Active/Exit drives who appears in payroll.
Salary register — Net = Gross − Deductions

Figure. Salary register — Net = Gross − Deductions

4. Salary and incentives

Why

Net pay must be explainable line by line.

What

Salary register with gross, PF teaching column, net; incentive sheet with target rule.

How

  1. Open Salary register. Change Present days; prorata basic updates.
  2. Open Incentives. Achievement % below 100% → incentive 0 in this teaching rule.
  3. Never use these PF figures as legal advice — confirm with your payroll vendor.

5. Headcount MIS

Why

Leadership asks strength, not stories.

What

Open + joiners − exits = closing.

How

  1. Enter joiners and exits for October.
  2. Closing headcount is green.
  3. Attrition % uses exits ÷ average strength.

6. Sheet previews

Why

See the layout before you open Excel.

What

Screenshots of the main sheets in the downloadable templates.

How

Scroll the figures, then download the matching .xlsx.

Attendance — COUNTIF Present

Figure. Attendance — COUNTIF Present

Salary register — Net = Gross − Deductions

Figure. Salary register — Net = Gross − Deductions

7. Common mistakes

Mistake What goes wrong Fix
Salary on exited employees Overpayment. Filter Status = Active before payroll.
Leave balance as typed number only Balance drifts. Opening + credited − taken.
Copying real Aadhaar into practice files Privacy risk. Made-up IDs only here.

8. FAQ

Are PF/ESI formulas legally exact?

No — teaching shapes only. Use your payroll software and current statutes.

Link to attendance Daily Life lesson?

Yes — see Related chapters for a focused attendance tracker.

9. Try at home

Try at home

  1. On Attendance, how many Present days does Ravindra Bagale have?
  2. On Salary, what is Net for Emp E101?
  3. On Headcount, Closing for October?

Recap

In short

  1. 4 roles use Excel in this profession.
  2. 6 common file types are listed above.
  3. 1 downloadable templates with yellow inputs and green formulas.
  4. Rates and rules here are teaching examples only.
  5. Use Related chapters for focused skills.

Quick revision cards

Quick revision

1 interview card, 1 comparison card and 1 revision card. Save on your phone or download as PNG. Numbers are made up for practice.

Interview card: How do you get Present days from a month grid in Excel?
Card 1 of 3: Interview card – How do you get Present days from a month grid in Excel? · Download PNG
Comparison card: Gross vs Net salary columns
Card 2 of 3: Comparison card – Gross vs Net salary columns · Download PNG
Revision card: Closing headcount
Card 3 of 3: Revision card – Closing headcount · Download PNG