Ready-To-Use Payroll Excel Template With Attendance

Payroll Excel Template with Attendance a fully automated template in Excel, OpenOffice Calc, and Google Sheet to manage the complete payroll process.

A simple 3 step process to manage your company payroll:

1. Mark the attendance.
2. Insert CTC and the applicable allowances/deductions.
3. Print the salary slips.

Download Payroll Excel Template With Attendance (Excel, OpenOffice Calc, and Google Sheet)

Simple, easy, and fully automated Payroll Excel Template with Attendance predefined formulas and functions that help you process the payroll of 50 employees in just a few minutes. A payroll template is also referred to as Salary Sheet. The template follows the rules of the Indian salary structure.

Click the link below to download the desired file format:

Microsoft Excel OpenOffice Calc Google Sheet

You can also download other HR templates such as Simple Salary Sheet or Employee Salary Sheet depending on the company requirement.

Additionally, the attendance sheet consists of predefined formulas that automatically displays every day off with different color. Just select the month and it will automatically mark the weekend for you.

Moreover, the template consists of easy navigation buttons. These buttons help you easily move along the multiple sheets in the template. You can print all 50 salary slips or payslips at the click of a button.

Furthermore, the template also helps you store monthly payroll data that helps the HR staff prepare multiple reports.

Note: There are no navigation buttons in Google sheet and OpenOffice Calc file format. The template functions in the same manner in all the file formats: Excel, Google sheet, and Openoffice Calc. Google Sheet and Openoffice Calc users have to manually copy the monthly data to the “Salary Data” sheet. 

Understanding Contents of Payroll Excel Template With Attendance

This template consists of 6 sheets: Employee Attendance Sheet, Salary Calculation Sheet, Approval Sheet, Salary Slip Printing Sheet, Salary Data Sheet, and Salary Slip Generator.

Let us discuss each sheet individually to understand the working of the payroll excel template with attendance and the payroll process it follows.

Employee Attendance Sheet

Every salary process starts with attendance. It helps to count working days for each employee.

In this sheet, first, you need to select the month and year. Based on your selection, the template will automatically display the weekdays. Each Sunday column fills in orange automatically with the help of conditional formatting.

In case, you want to mark Saturday and Sunday both as a week off then you need to change the conditional formatting by following these steps:

1. Go to the “Home” tab. Navigate to “Conditional Formatting” in the ribbon area under “Styles”.
2. Click on the “New Rule” option.
3. Insert the same rule for =E$8=” Sat” and select applies to $E$8:$AI$60. This will fill your Saturday columns with orange.

The Employee ID and Employee Name are auto-populated based on entries made in the salary calculation sheet.

Mark the attendance. “P” with presence and “L” with Leave of the employee on each day of the week. In the second last column, insert “Allowed Leaves” as per your company policy.

With the help of COUNTIF functions, the template calculated the leaves and presence. It will deduct the allowed leaves from the same and will display the days payable accordingly.

The last row of the attendance sheet displays the daily strength of the organization.

At the start of every new month use the “Clear All” button at the top to clear the contents of attendance. This will clear the data from cells E9 to AI58.

Salary Calculation Sheet

Initially, the user needs to insert the following details one time only as these details do not change unless an employee leaves or the company recruits a new employee:

Sr.No
Employee ID
Employee Name
Designation
Department
Gender
Location
CTC
Conveyance Allowance
Medical Allowance
Professional Tax
TDS
Salary Advance
Bonus
Bank Name
Branch
Account Number

TDS rate: Currently, the TDS rate is 10% till May 2020 which was 7.5% earlier. These rates have been increased for FY 2020-2021. Insert applicable tax rate in this column based on employee’s salary. This task needs to be done one time.

Professional Tax: The user needs to enter this amount manually. This amount differed based on salary payable. If the salary of the employee is Rs. 15000 or below then NIL. From Rs. 15001 to Rs. 20000 it is Rs. 150 and above Rs. 20000 it is Rs. 200. This cannot be more than Rs. 2400 yearly.

As soon as you insert the above details the following fields are auto-calculated as per the predefined formulas:

Month & Year

Based on your selection in the Attendance Sheet, this column is auto-populates both month and year from Attendance Sheet.

Working Days, Allowed Leaves, Leaves Allowed, & Days Payable

The salary sheet fetches these data automatically from the attendance sheet.

Basic Salary

The basic salary is calculated here as 60% of the CTC. You can change the formula according to your company policy. Thus, keep in mind that once you change the formula, kindly copy the same formula to the whole column using the fill handle or copy-paste option.

Depending on days of attendance the column calculates the Basic salary. The formula applied here is:

=IF(N8=0, ” “, O8/K8*N8*60%).

House Rent Allowance

The template calculates HRA as 40% or 50% based on the location of the employee. If the HR selects the location of the employee as metro then 50% and if non-metro then 40% of basic salary.

Moreover, similar to Basic Salary, HRA is also calculated based on attendance. The formula applied here is :

=IF(G8=”Metro”,P8/K8*N8*50%,IF(G8=”Non-Metro”,P8/K8*N8*40%, 0))

Conveyance Actual & Medical Actual

As the user enters the allowed conveyance and medical allowance, the Conveyance and Medical Actual amounts column will calculate the amount based on attendance. Often, these allowances can differ from company to company.

Special Allowance & Special Allowance Actual

These columns are also auto-populated based on the remaining amount of CTC after subtracting basic, HRA, medical allowance, and conveyance allowance.

The formula applied for the Special Allowance column is =IF(N8=0,” “, O8-(P8+Q8+R8+T8)). Similar to other allowances, the sheet calculates special allowance also according to attendance.

Total Allowances

This column sums up all the allowances adding HRA, Conveyance, Medical, and Special allowances.

Gross Salary

Basic in addition with Allowances equals Gross salary. This amount is before any kind of deductions. The following formula has been applied here: =Q8+S8+U8+W8.

TDS

This column calculates 10% TDS on annual gross salary above Rs based on the above-mentioned TDS rate. 500000. If you change the formula, then copy the same to other cells of the same column.

EPF (Employee Provident Fund)

EPF stands for employee provident fund. Companies with more than 20 employees must register for EPF. The employer deducts 12% of the gross salary from the employee’s salary.

If this amount is less than Rs. 1800 then the minimum amount of Rs. 1800 will be deducted or else the actual amount will be deducted. The formula applied here is =IF(Y8=0,0,IF(Y8*12%<1800,1800,Y8*12%)).

Salary Advance

Insert the amount of salary advance taken by the employee and if not then leave blank.

Total Deductions

This column populates the total amount of deductions applicable to salary. This sums up Professional Tax, TDS, EPF, and salary advances.

Bonus

Insert if applicable or else leave blank. Usually, in India, companies pay a bonus during Diwali. Thus, this column will usually remain blank.

Net Salary

This column is auto-populated. Gross Salary minus total deductions plus bonus equals Net Salary.

Once you are done with this, click on the “Save Data” button to save the monthly payroll data to the Salary Data sheet every month.

For more details on Salary components kindly view the FAQ section below this article.

Click on the “Approval Sheet” Button to navigate to that sheet.

Approval Sheet

This sheet doesn’t require any manual entry. Just print the sheet and present the same for approval to the HR authority.

This sheet displays only basic salary, total allowances, and total deductions. Click the print button above and print this approval sheet.

Salary Data Sheet

By clicking the “Save Data” button in the Salary Calculation sheet this sheet auto-populates itself. Do not click the “Save Data” button more than once otherwise same data will be copied.

After approval and saving data now comes the salary payment. Transfer the amount to the respective bank accounts. Now you can print the salary slip. To print the salary slips click on the “Salary Slip” button.

This sheet consists of filter options. Hence, the HR staff can use this data for analysis. You can prepare the monthly, quarterly, half-yearly, and yearly salary reports.

Moreover, this sheet can help you prepare an employee-wise report for each year.

Not only that, with the help of the filter option you can choose n number of criteria. Thus, it helps you to prepare a customized report as per the requirement of your management.

Salary Slip Printing Sheet

This sheet consists of 50 salary slips that automatically fetch data of each employee. Just click the “Print” button and this will print all 50 salary slips in one click.

Salary Slip Generator

You can generate the salary slip of an individual employee by navigating to Salary Slip Generator. Select the employee ID. The sheet will display the salary slip for that respective employee.

Click on the “Print” button to print the individual salary slip.

Frequently Asked Questions Payroll Excel Template With Attendance

If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.

We also design customized templates according to your needs. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.