Salary Sheet With Attendance Register is a complete payroll sheet in which you can maintain records of attendance for your employees and also calculate the salaries of 40 employees.
What is Salary Sheet?
Salary Sheet Excel Template is a payroll document in which you can record payroll data for multiple employees along with Salary slip.
What is the Attendance Sheet?
Employee Attendance Sheet is a document that records the presence, absence, sick leave, etc of employees for payroll or salary purposes.
Salary Sheet With Attendance Register Excel Template
After the success of our previous templates viz: salary sheet and attendance sheet, we received ample of requests from our readers to combine both in one sheet.
This sheet will ease your payroll calculation and make it a simple and quick process. It is now just 3 step process:
- Update attendance regularly.
- Enter Salary Calculations
- Print Salary Slips
That’s It. Your work is all done. All the files are interlinked with each other. No repetitive tasks.
We have created a Salary Sheet With Attendance Register Template with advance VBA code and predefined formulas for easy calculations.
If you want to download only Salary Sheet without attendance you can download it from the link below:
You can download the HR and Payroll template in Apple Numbers from the link given below. Apple Numbers is another spreadsheet program similar to Microsoft Excel for the MAC operating system.
Let us discuss how to efficiently and easily use this template.
Note: After you download the template, don’t forget to enable the content in the security warning pop-up below the ribbon as shown in the images below:
How to use Salary Sheet With Attendance Register Excel Template?
This template consists of 4 sheets:
- Employee Attendance Sheet
- Database Sheet
- Consolidation Sheet
- Salary Slip
1. Employee Attendance Sheet
In the header section, Enter the month for which you want to prepare the Attendance. You have to enter only the first date in the sheet and it will automatically display dates of the rest of the month.
Similarly, when you will enter the date, it will also automatically display the name of the day of the week. Text Function has been used here. Then you need to record the names of your employees with their employee id and their designation.
Secondly, select presence(P), leave(L) or holiday(H) from the drop-down list as shown in the figure below:
If you select P the cell will be filled with green color and when you select L it will fill Red color. For H it will fill a dark blue color.
Usually, Sunday is a Holiday. Some companies also mark Saturdays and Sundays as holidays. So need can choose H for those days.
If there are other National, Company or Religious Holidays you can also mark them with H. For example, I have marked 25th May as an extra Holiday. At the end of the sheet, you can see the total number of employees present on that particular day.
Lastly, an attendance summary has been given on the right. It will display the Total number of days in the month, Total Presence, Total Leaves, Total Working Days, Total Worked Days, Total Allowed Leaves and Toal Day off in the month.
2. Database Sheet
All the data entered in the Attendance sheet will automatically display like Names of employees, designation, and attendance summary. Here you will have to select month and year from the drop-down list. All the cells below will display the same.
Then comes the Salary Calculations. In this section, you need to enter the CTC amount of the employee and it will automatically calculate the salary as per the given formula. If you have different calculations you can either change the formulas or you can directly enter the amounts as required.
Both Gross Salary and Deductions will display the total and finally the take-home amount that is the Net Payable Salary.
Furthermore, you can provide additional information to be displayed in Salary slips at the end like gender, prefix, PF applicability, medical, signatory, etc.
3. Consolidation Sheet
The consolidation sheet will display the data payroll that you have entered in the database sheet. This sheet is only for approval from higher authorities. Just the replica of your payroll calculations with the approval section.
4. Salary Slip
Payslips or Salary slips are an important part of the Payroll process. Salary slip consists of all earnings (fixed and variable) and deductions (fixed and variable) along with the mandatory deductions.
On the right-hand side just select the name of the desired employee and it will automatically display the detail of that employee. Vlookup has been used here to fetch data from the database sheet.
Once you are done with that just click on the Print button to print the Salary Slip of your employees. The salary slip will be displayed as shown below:
That’s all you need to do to prepare the salary of your colleagues or employees. Enjoy using this simple template to manage the attendance and prepare the salary.
We have tried our best to avoid any mistakes in calculations. If you find any just let us know so that it can be rectified.
In addition to the above, if you want another salary slip formats you can check out the below article:
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries please share in the comment section below. I will be more than happy to assist you.