Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (2023)

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (1)

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.

Table of Contents

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


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 forpayrollor salary purposes.

Types of Leaves

Generally, there are 8 types of leaves as follows:


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.

(Video) How To Make Attendance Sheet With Salary In Ms Excel || How to Make Attendance Sheet in Excel Hindi

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

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.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (2)

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:

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (3)

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (4)

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:

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (5)

(Video) Create your own Employee Attendance Template using Excel 365

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.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (6)

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.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (7)

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.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (8)

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.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (9)

Both Gross Salary and Deductions will display the total and finally the take-homeamount 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.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (10)

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 payrollcalculations with the approval section.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (11)

Salary Slip

Payslips or Salary slipsare 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.

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (12)

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:

(Video) Salary Sheet Limited Company For Microsoft excel Advance Formula

Download Salary Sheet With Attendance Register In Single Excel Template - ExcelDataPro (13)

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-UseSalary 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

How many sick leaves can an employee avail in a month?

Usually, an employee can take 12 sick leave in a calendar as per the shops and establishment act. Moreover, such leaves rules are regulated as per the concerned State’s Shops and Establishment Act or Factories Act.

How to calculate annual leave as per Indian Labour Law?

An employee has a minimum annual leave of three times his working days. For example, if an employee works 6 days a week he is entitled to annual leave of 18 days each year. That is 6 x 3 = 18.

How to calculate leave pay?

Leave encashment regulations are different from company to company. Generally, the amount is calculated on Basic Pay. Divide the monthly Basic day to per day basic amount and then multiply it by the number of paid days to be encashed.

How many days of leave can be availed for marriage in India?

In general, full-time employees are entitled to 3 days of leave for marriage. It is only applicable for the first legal marriage and mandatory to take within the first six months from the date of marriage.

(Video) How To Create Salary Chart In Microsoft Excel | Salary Sheet in Excel

How many hours an employer can make an employee work continuously?

According to the Minimum Wages Act, for an adult worker, the working day must be set in such a way that it should not exceed 12 hours inclusive of 1-hour of rest.

How to calculate OT Pay?

According to the Minimum Wages Act, divide the wage rate by 26 to calculate per day salary that is for 8 hours. Furthermore, divide it by 8 to calculate the per hour salary. Multiply it with 2 or 1.5 and then multiply it with the number of overtime hours worked.

What is the rule of notice period?

The minimum notice period as per the law is one week for every year worked. The rule for notice is one pay period. Thus, one week if the employee is paid weekly and one month if paid monthly.

What type of deductions can an employer make your salary?

An employer can deduct the following:

  • Company fines as per the company rules and regulations.
  • Pay for leave of absence.
  • Deduction against damage or loss of goods entrusted.
  • House Rent in case the accommodation is provided by the company.
  • Deduction against additional amenities and service provided by the employer or contract.
  • Deduction for recovery of advances and interest, and adjustment of overpayment;
  • Recovery of loans lent by the company.
  • Income tax deduction in the form of TDS.
  • Deduction against orders of a court or other legal authority.
  • Repayment of advance from PF.
  • Payments to outer cooperative societies if it is agreed between the employer and the employee.
  • LIC premium against authorization by the employee.
  • Post Office Saving Schemes against authorization.


1. How To Make Automatic student fee collection and status system in excel
(Shahab Islam)
2. Vlook with Data Velidation List
(Creative Soft)
3. Fully Automatic Invoice in Excel | How to Create Invoice in Excel | Bill in Excel
4. Learn How To Create & Print Student ID Cards In Excel And Protect Passwords [School Manager Pt. 4]
(Excel For Freelancers)
5. How to make time table in Microsoft Excel 2019 || School Time table in Excel Sheet Download
(YadOnline training academy)
6. Excel में बनायें Data Entry Form बिना Visual Basic जाने 😀
Top Articles
Latest Posts
Article information

Author: Golda Nolan II

Last Updated: 01/27/2023

Views: 6218

Rating: 4.8 / 5 (58 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Golda Nolan II

Birthday: 1998-05-14

Address: Suite 369 9754 Roberts Pines, West Benitaburgh, NM 69180-7958

Phone: +522993866487

Job: Sales Executive

Hobby: Worldbuilding, Shopping, Quilting, Cooking, Homebrewing, Leather crafting, Pet

Introduction: My name is Golda Nolan II, I am a thoughtful, clever, cute, jolly, brave, powerful, splendid person who loves writing and wants to share my knowledge and understanding with you.