Download Salary Sheet With Attendance Register In Single Excel Template

Salary Sheet With Attendance Template is a ready-to-use in Excel, Google Sheets, and Open Office Calcpayroll sheet to maintain records of attendance and 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.

Components of Salary

There are three major components of the Salary: Basic Pay, Allowances, and Deductions.

Basic Salary

Basic Salary is fix amount that an employer pays in addition to allowances and subtraction of any deductions. It does not include bonuses, overtime, dearness allowance, etc. Usually, the basic salary is 40% or 50% of the CTC. Basic Salary is proportionate to the working days.

Allowances

Allowances are the financial benefits an employer provides above the Basic Salary to his employees. These allowances differ based on company policy, industry, job profile, and department.

• Dearness Allowance
• House Rent Allowance
• Conveyance Allowance
• Medical Allowance
• Special Allowance
• Bonus Pay
• TA – Travel Allowance
• Child Education Allowance

Deductions

Salary deductions are the amounts that an employer subtracts from an employee’s gross pay to reach net pay. These deductions include Employee Provident fund, Salary Advance, professional tax, TDS, etc.

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.

Types of Leaves

Generally, there are 8 types of leaves as follows:

Holidays

Holidays are the leaves for which an employer doesn’t deduct any amount from his/her salary. It includes national holidays, weekly day-offs, festive holidays.

Earned Leave or Privilege Leave

Earned leaves are mandatory yearly leave than an employee earns while working. A specific number of days are allowed depending on the state and act the companies follow.

Employee avails such leave without any salary deductions. An employee has to obtain prior approval from the employer. In some companies as per the employment contract, employees can accumulate and encash money against accumulated/unused Earned Leaves.

Casual Leave

An employee can avail Casual leave or CL for short durations with prior intimation to the employer. Similar to the earned leave, these leaves also differ according to the industry and state.

Sick Leave or Medical Leave

An employer allows a stipulated number of Sick leaves per year. Usually, it is 12 days per year that is one SL per month. In sick leave, it is not mandatory for an employee without any prior intimation. It is advisable to inform the employer.

Maternity Leave

Maternity leaves are especially available for those women employees who plan to deliver a baby. Usually, maternity leave is 12-26 weeks. Moreover, a female employee can extend an additional unpaid leave of 16 months.

Half Pay Leave

Half Pay leaves are usually available only to government employees who completed one year of service. An employee avails half day salary during such leaves.

Quarantine Leave

Quarantine Leaves are those an employer grants to an employee only if the employee or his family member suffers a hazardous infectious disease.

Study Leave or Sabbatical Leave

: An employer grants study or sabbatical leave to the employee to update his/her knowledge and experience. Such an employee needs to rejoin the company and use learned skills.

Salary Sheet With Attendance 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 and 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 predefined formulas for easy calculations.

Excel Google Sheets Open Office Calc

Click here to Download All HR & Payroll Excel Templates for ₹299.

Important Note: To edit and customize the Google Sheet, save the file on your Google Drive by using the “Make a Copy” option from the File menu.

You can download other HR and Payroll Templates like Employee Evaluation Template, Weekly Timesheet, Job Candidate Tracker, and much more.

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 Template?

This template consists of 4 sheets: Employee Attendance Sheet, Database Sheet, Consolidation Sheet, and Salary Slip.

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 using Text Function. 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.

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.

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.

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:

9 Ready-To-Use Salary Slip Templates In Excel

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.

Frequently Asked Questions