Attendance Sheet with Salary in Excel Format: Free Templates & Guide

An attendance sheet with salary in Excel format helps small businesses and HR teams manage working days, leaves, and payroll in one place. It saves time, reduces manual errors, and keeps salary records organized. You can use it to calculate pay automatically based on attendance, including overtime, deductions, and bonuses.

Below, you’ll find free Excel templates and a step-by-step guide to create your own. No advanced Excel skills needed.

Why Use Excel for Attendance and Salary?

For small and medium businesses, Excel is one of the easiest tools to manage attendance and payroll. You don’t need expensive software or technical knowledge to start.

1. Cost-effective
Excel is free or already available in most offices. It’s perfect for startups and small teams that can’t invest in full HR systems yet.

2. Flexible customization
You can design columns, formulas, and formats the way your company works — whether you pay monthly, daily, or hourly. Excel formulas like COUNTIF, SUM, and IF let you build smart calculations for attendance and salary.

3. Easy automation
With simple formulas, Excel can calculate total working days, leaves, and net pay automatically. You can also use conditional formatting to highlight absences or delays.

4. Easy to share and update
You can email or upload sheets to shared drives, and your accountant or HR can update them daily.

5. Good first step before ERP
When your business grows, you can shift from Excel to a system like Kladana ERP, where attendance, payroll, and inventory are linked automatically.

Key Features of an Attendance & Salary Sheet

A good attendance and salary sheet in Excel combines time tracking and payroll management in one document. It helps you record attendance, calculate pay, and maintain proof of working days.

Here’s what it usually includes:

To make the sheet easier to read, use:

• Color codes (e.g., green for Present, red for Absent)
• Drop-down lists for attendance codes (Data → Data Validation → List: P, A, L, H)
• Freeze Panes to keep employee names visible while scrolling

These elements keep your payroll data clean and simple to update.

Excel Templates for Attendance with Salary

Different companies handle payroll in different ways. Some calculate monthly salaries, others pay per day or per project. That’s why we’ve prepared several attendance and salary Excel templates for common use cases.

1. Simple Monthly Attendance + Salary Sheet

Best for small teams and shops.
Tracks daily attendance using P, A, L, H codes and calculates total working days, paid days, and basic salary.

Includes a formula to adjust pay automatically when someone is absent.

🧩 Example formula:
=Basic_Salary/Total_Working_Days*Paid_Days

2. Overtime and Late Fine Format

Ideal for factories and service teams where employees work extra hours or have penalties for late arrivals.

The sheet includes columns for overtime hours, overtime pay rate, and late fines.
All calculations are automatic once you enter hours.

🧩 Example formula:
=Overtime_Hours*Overtime_Rate−Late_Fine

3. Yearly Attendance Register with Salary Summary

Useful for HR departments and accountants who need annual overviews.
Each month has its own tab, and the summary sheet totals attendance, salary, and deductions for the whole year.

Helps analyze employee performance and track leave balances.

4. GST/TDS-Compliant Salary Format

Designed for companies that must show statutory deductions such as TDS or GST (for contractors or consultants).

The template includes extra columns for tax rate, tax amount, and total after tax.
You can export it as a tax-compliant payslip for documentation.

💡 Tip: Each template can be adapted. You can copy the basic structure and add your own columns for bonuses, incentives, or shift-based pay.

How to Create an Attendance & Salary Sheet in Excel

You can build your own attendance and salary sheet in less than an hour. Follow these steps — no advanced Excel knowledge needed.

1. Set up the table structure

Create columns for:

• Employee details: Name, ID, Department
• Days of the month: 1 to 31
• Summary columns: Total Present, Absent, Leave, Paid Days, Salary

Freeze the top row so headers stay visible when scrolling.

2. Add drop-down lists for attendance codes

Select the daily cells → go to Data → Data Validation → List → enter:
P, A, L, H

Now you can choose attendance codes from a menu instead of typing them.
You can also add colors:

• Green = Present
• Red = Absent
• Yellow = Leave
• Blue = Holiday

Use Conditional Formatting → Highlight Cells Rules → Text that Contains to set these.

3. Count attendance automatically

In the summary columns, use formulas to calculate totals.

• Present Days:
  =COUNTIF(B3:AF3,”P”)
• Absent Days:
  =COUNTIF(B3:AF3,”A”)
• Leave Days:
  =COUNTIF(B3:AF3,”L”)
• Paid Days:
  =COUNTIF(B3:AF3,”P”)+COUNTIF(B3:AF3,”L”)+COUNTIF(B3:AF3,”H”)

This saves time and reduces manual counting errors.

4. Add salary calculation formulas

Now set up formulas to calculate salary based on attendance:

• Per-day rate:
  =Basic_Salary/Total_Working_Days
• Gross salary:
  =Per_Day_Rate*Paid_Days
• Net salary (after deductions):
  =Gross_Salary−Deductions

You can also include Overtime:

• Overtime pay: =Overtime_Hours*Overtime_Rate
• Add it to salary: =Gross_Salary+Overtime_Pay−Deductions

5. Use conditional formatting for quick checks

Color-code absences and late arrivals to spot attendance issues faster.
Example: use light red fill for cells marked “A” to highlight missing days.

6. Lock salary formulas

To prevent accidental edits, protect formulas:

• Select formula cells → Right-click →Format Cells → Protection → Lock
• Go toReview → Protect Sheet → Set Password

Now only HR or the accountant can change them.

7. Save and duplicate for each month

Save your sheet as a master template. For every new month, duplicate the file and update only the date range.

💡 Tip: If you use Google Sheets, enable “Protect range” and “Share view-only” access for extra safety.

Best Practices for HR & Payroll

An Excel-based attendance and salary system works well if it’s maintained regularly. Here are some tips to keep your records accurate and compliant.

1. Update attendance daily
Record attendance at the end of each workday. It helps avoid confusion about leaves or overtime later.

2. Verify leave policies before deductions
Always check the company’s leave policy before adjusting pay. Unapproved leave, paid leave, and half-days affect calculations differently.

3. Keep separate sheets for each month
Don’t overwrite data. Create a new tab or file for each month and keep them in one folder named by year.

4. Backup regularly
Save your Excel sheet on a shared drive or cloud service (Google Drive, OneDrive). Set auto-backup to prevent data loss.

5. Protect sensitive information
Salary details are confidential. Use password protection and restrict editing rights.

6. Reconcile with bank payments
At the end of the month, compare Excel totals with the actual salary transfers to ensure accuracy.

7. Prepare for automation
When your payroll grows or includes multiple locations, shift to a business management tool like an ERP system. It automates attendance, payroll, and reporting while keeping all records secure and centralized.

Download Free Excel Templates

You can use these ready-made templates right away or customize them for your business. Each file includes built-in formulas for attendance tracking, salary calculation, and deductions.

1. Monthly Attendance + Salary Template

Best for small offices and startups.
Includes daily attendance codes (P, A, L, H) and auto-calculation of working days, paid days, and net pay. You only enter employee names and basic salaries — the sheet does the rest.

2. Yearly Payroll Management Sheet

Suitable for HR teams handling multiple months.
Has separate tabs for each month and one summary sheet with total attendance, gross salary, and deductions for the year. Simplifies audit preparation and employee reviews.

3. Overtime Calculation Format

Created for factories, workshops, or service companies.
Includes columns for overtime hours and overtime rate. Calculates final salary automatically after subtracting adjustments.

4. GST/TDS Salary Template

Designed for companies that must show taxes or contractor payments.
Includes tax fields, percentage formulas, and total after-tax calculations. Helps generate professional, compliant payslips.

Frequently Asked Questions On Attendance Sheet with Salary

Payroll and attendance management often raise the same practical questions. Here are clear answers you can use while working with your Excel sheet.

How do I calculate salary based on attendance in Excel?

Divide the basic salary by the number of working days and multiply by the paid days.
Example: =Basic_Salary/Total_Working_Days*Paid_Days

Can Excel handle overtime and deductions automatically?

Yes. Add columns for overtime and deductions, then use formulas such as:
Overtime pay: =Overtime_Hours*Overtime_Rate
Net salary: =Gross+Overtime−Deductions

Is an Excel-based attendance sheet valid for payroll?

Yes. It works well for small teams. Keep monthly backups and lock formulas so no one can change them accidentally.

How do I record a half-day in attendance?

Use HD or assign a value of 0.5.
Paid days example: =COUNTIF(range,”P”)+COUNTIF(range,”L”)+COUNTIF(range,”H”)+COUNTIF(range,”HD”)*0.5

How do I create attendance drop-down codes?

Select the daily cells → Data → Data Validation → List → Enter:
P, A, L, H, HD
This keeps the sheet consistent and prevents typing errors.

How do I handle paid leave, sick leave, or unpaid leave?

Use separate codes:

• PL for paid leave
• SL for sick leave
• UL for unpaid leave

Add them to your paid days formula where needed.

Can I lock salary formulas so staff can’t edit them?

Yes. Select formula cells → Format Cells → Protection → Lock → Protect Sheet. Only authorized users with the password can modify the formulas.

How do I adapt the sheet for hourly or daily wages?

Add a rate column.
Hourly: =Hours_Worked*Hourly_Rate
Daily: =Paid_Days*Daily_Rate

How do I manage different pay periods in one month?

Add separate salary tables for each period, such as 1–15 and 16–30. Keep attendance shared but calculate salary in two blocks.

How do I export or share the salary sheet?

Use Save as PDF for payslips or summaries. In Google Sheets, share the file as view-only to prevent changes.