Lesson 1.2 – MS Excel: Attendance Sheets, Salary Calculation, Payroll Tables

Introduction

Microsoft Excel is one of the most powerful tools for HR professionals. It is widely used for attendance management, leave tracking, salary calculations, and payroll management. Excel’s formulas, tables, and charts make data handling faster, more accurate, and professional.

1. Importance of Excel in HR

• Attendance Tracking: Daily, weekly, and monthly attendance records.

• Leave Management: Easy to calculate casual, sick, and earned leaves.

• Salary & Payroll: Automates gross salary, deductions, and net pay.

• Data Analysis: HR can use Excel to analyze employee trends like absenteeism.

• Reports & Charts: Create professional payroll and attendance reports.

2. Attendance Sheets in Excel

Steps:

1. Open a new workbook and create columns for:

   • Employee ID, Name, Department, Days of Month (1–30/31).

2. Mark P = Present, A = Absent, L = Leave.

3. Use the COUNTIF formula to calculate total present and absent days.

Formula Example:
=COUNTIF(C2:AG2,"P") → Counts total presents for one employee.

3. Salary Calculation in Excel

Basic Salary Structure:

• Basic Pay

• House Rent Allowance (HRA)

• Dearness Allowance (DA)

• Other Allowances

• Deductions (PF, ESI, Tax, etc.)

• Net Salary = Gross Salary – Deductions

Formula Example:

• HRA = =B2*0.20 (20% of Basic Pay)

• DA = =B2*0.10 (10% of Basic Pay)

• Gross Salary = =B2+C2+D2+E2

• Net Salary = =Gross - Deductions

4. Payroll Tables

Payroll in Excel helps HR maintain records of all employees’ salaries, attendance, deductions, and net payments.

Steps:

1. Create a table with columns:

   • Employee ID | Name | Basic | HRA | DA | Other Allowances | Deductions | Net Salary

2. Use formulas to automatically calculate Net Salary.

3. Apply Conditional Formatting to highlight errors (e.g., negative salary).

4. Protect the sheet with a password to ensure data security.

5. Best Practices

• Always keep one master payroll sheet and one monthly sheet.

• Use Data Validation to avoid wrong entries (like alphabets in numbers).

• Keep employee ID unique for easy tracking.

• Save payroll data in Excel and PDF format for official records.

• Regularly backup data for security.

Sample Formats for Practice

(A) Sample Attendance Sheet

(B) Sample Salary Calculation Table

(C) Sample Payroll Summary Report

Payroll Report – September 2025

Conclusion

Excel is an indispensable tool for HR professionals. With the right formulas and structured payroll tables, HR can save time, reduce errors, and ensure transparency in salary processing.