• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Download Salary Sheet With Attendance Register In Single Excel Template

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.

Table of Contents

  • What is Salary Sheet?
    • Components of Salary
  • What is the Attendance Sheet?
    • Types of Leaves
  • Salary Sheet With Attendance Template
  • How to use Salary Sheet With Attendance Template?
    • Employee Attendance Sheet
    • Database Sheet
    • Consolidation Sheet
    • Salary Slip
  • Frequently Asked Questions

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:

Salary Sheet With Attendance Register

Salary Sheet With Attendance Register

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:

Salary Sheet With Attendance Register

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.

Salary Sheet With Attendance Register

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.

Salary Sheet With Attendance Register

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.

Salary Sheet With Attendance Register

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.

Salary Sheet With Attendance Register

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.

Salary Sheet With Attendance Register

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 Sheet With Attendance Register

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.

Salary Sheet With Attendance Register

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:

Salary Sheet With Attendance Register

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

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.

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.
« 9 Ready-To-Use Salary Slip Excel Templates
Download Weekly Timesheet With Payslip Excel Template »

Filed Under: HR Templates

You are Here: Home / Excel Templates / HR Templates / Download Salary Sheet With Attendance Register In Single Excel Template

About Shabbir Bhimani

I have worked in Excel and like to share functional excel templates at ExcelDataPro.

Comments

  1. yash says

    June 27, 2018 at 5:12 am

    SALARY SLIP FORMAT IN EXCEL

    Reply
    • Fahim Lashkaria says

      June 27, 2018 at 7:20 am

      You can find 9 ready to use different types of Salary Slips in the below article:
      https://exceldatapro.com/salary-slip-templates/

      Reply
  2. suhail says

    July 9, 2018 at 6:37 am

    Dear Sir
    When iselect June month it displays 1july also with 31 days of month.
    why it is displaying 1 july in the month of June.

    Reply
    • Fahim Lashkaria says

      July 9, 2018 at 2:33 pm

      Dear Suhail, Thank you for contacting us.
      1. First of all, you need to enter the number of working days manually in the database sheet. Not only in the first cell but in all cells in “Total Days of the Month” column.
      2. IF still, the problem persists then you might have deleted some formulas. Try downloading the sheet again, please.

      Reply
  3. suhail says

    July 9, 2018 at 6:40 am

    displaying total day of month 31 in february month.

    Reply
    • Fahim Lashkaria says

      July 9, 2018 at 2:33 pm

      Dear Suhail, Thank you for contacting us.
      1. First of all, you need to enter the number of working days manually in the database sheet. Not only in the first cell but in all cells in “Total Days of the Month” column.
      2. IF still, the problem persists then you might have deleted some formulas. Try downloading the sheet again, please.

      Reply
  4. suhail says

    July 9, 2018 at 9:04 am

    Dear Sir
    Facing problem while making salary for the month of June or any other month which have less than 31 days
    excel sheet is adding dates of next month.
    salary is calculating for 31 days instead of 30 days
    for this i have to change the cell reference every month

    Reply
    • Fahim Lashkaria says

      July 9, 2018 at 2:33 pm

      Dear Suhail, Thank you for contacting us.
      1. First of all, you need to enter the number of working days manually in the database sheet. Not only in the first cell but in all cells in “Total Days of the Month” column.
      2. IF still, the problem persists then you might have deleted some formulas. Try downloading the sheet again, please.

      Reply
  5. Ram says

    October 3, 2018 at 8:10 am

    Dear Sir,

    Wow! This is very helpful, I’m lucky to have found this templates, thank you so much for this… just one question how to edit the total number of working days excluding Fridays and Saturdays each month.

    Thank you again.

    Reply
    • Fahim Lashkaria says

      October 6, 2018 at 5:38 am

      Thanks for the appreciation. You can change formulas for Saturdays and Fridays.

      Reply
    • Muskan mangar says

      December 11, 2019 at 10:08 am

      My name muskan mangar Oct & Nov salary15000 slip give me my company aaradhana

      Reply
      • Fahim Lashkaria says

        December 11, 2019 at 11:06 am

        You can download the excel file and print your own salary slip or ask your company to do it. We are not authorized to do this.

        Reply
        • Mohibullah rasooli says

          March 21, 2021 at 2:56 am

          In western country every small,big or normal business has registered number of tax.

          so , it is for those who has small shop, normal market etc.

          Reply
  6. shadi says

    January 2, 2019 at 5:12 am

    Al Salamu Alikum Fahim,
    Thank you for your effort and the excellent work .. I have a note :
    The Number of (Worked Days) in Attendance template not matched with Database – since the data should be filled Auto in Database from the Attendance template.
    In the Attendance template the formula ignored the (Allowed Leave) but in Database it’s been added to the formula which gave different calculations.
    Regards,

    Reply
    • Fahim Lashkaria says

      January 2, 2019 at 8:58 am

      Dear Shahi, W salam.
      Thanks for notifying. The issue will soon be rectified and informed to you.

      Reply
  7. J N Nteh says

    June 15, 2019 at 12:42 pm

    Great job Sir, I found your work very interesting and helpful. Is there any way I can support you?
    Is there also a template for the invoice, for a company who takes a contract from many companies and need to sent them Bill every month?

    Reply
    • Fahim Lashkaria says

      June 16, 2019 at 1:43 pm

      Thanks for the appreciation. Glad to know that it was helpful to you. You can surely help us by sharing our templates on the timeline of different social media platforms and by sharing the story of how our templates were helpful to you. That’s it.

      Check out different invoice templates and download that best suits your needs:
      1. General Invoice – https://exceldatapro.com/invoice-template/
      2. Goods and Service Tax Invoice (GST)- https://exceldatapro.com/gst-invoice-template/
      3. Value Added Tax Invoice (VAT) – https://exceldatapro.com/uae-vat-tax-invoice/

      You can also look for other templates like Accounting, Financial Analysis, HR (Human Resources), etc on our website which can be helpful to you.

      Thanks and Regards

      Reply
  8. ZIN MAR AYE says

    August 28, 2019 at 12:15 pm

    Thank you for yours.
    I wanna know how to match basic pay, allowance, overtime hours, presence and leave or absent with income tax and other deductions for payslip no need to salary information.
    Please explain and match them only payslip with excel format.
    Thanks for your time.
    Best Regards,

    Reply
    • Fahim Lashkaria says

      August 28, 2019 at 12:30 pm

      I am unable to understand your query. Please describe clearly. thanks

      Reply
  9. ZIN MAR AYE says

    August 28, 2019 at 12:17 pm

    Thanks for your effort.

    Reply
  10. Aditya says

    December 6, 2019 at 4:11 pm

    Hi,
    Getting error on opening the sheet, unable to click on any button and also select the employee names from drop down. Plz help

    Reply
    • Fahim Lashkaria says

      December 7, 2019 at 12:05 pm

      This file consists of a macro and VBA code. When you download and open the file please select “Enable this content” from the security warning as mentioned in the article. I have checked the sheet it is completely working fine. You can download the sheet again and try.

      Reply
  11. Wajahat Sipra says

    January 21, 2020 at 3:55 pm

    Dear Sir,
    I want the excell sheet in I may cover attendance with time with database, consolidation and Salary Slip also, please

    Reply
    • Fahim Lashkaria says

      January 21, 2020 at 5:35 pm

      Attendance, Salary database, and consolidation are in this sheet. FOr time you will have to use another sheet.

      Reply
  12. vasant says

    February 4, 2020 at 10:57 am

    There is no any option to mark “Half leave”

    Reply
    • Fahim Lashkaria says

      February 4, 2020 at 5:06 pm

      No, it doesn’t have half-leave provision.

      Reply
  13. mimi says

    March 24, 2020 at 2:06 pm

    hello
    can i have the formula you used on the salary slip i am trying to customise my own but copying from your salary slip

    Reply
    • Fahim Lashkaria says

      March 24, 2020 at 4:37 pm

      There are many formulas. Which one do you want?

      Reply
  14. Joe says

    March 26, 2020 at 9:36 pm

    Sir,
    Please, do you have this excel teaching step by step process on YouTube? Please, I will like to learn this wise wisdom from you to enable me localize/customize

    Reply
    • Fahim Lashkaria says

      March 27, 2020 at 12:24 am

      Currently, we don’t have any such arrangements.

      Reply
      • Joe says

        March 27, 2020 at 5:50 pm

        Please, how can this be arranged for me to take this lessons?

        Reply
        • Fahim Lashkaria says

          March 28, 2020 at 8:18 pm

          I Will let you know in a week.

          Reply
  15. kathy says

    June 8, 2021 at 1:52 am

    Totally awesome!

    Reply
  16. Mohammad Asim says

    November 18, 2021 at 1:29 pm

    Dear Sir / Madam :
    I need format of Purchase , Sale and Profit I need please send my .

    Best Wishes .
    M.Asim

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

HR Templates

  • Salary Sheet Excel Template
  • Daily Work Schedule Excel Template
  • Weekly Timesheet Excel Template
  • Corporate Salary Slip Excel Template
  • Employee Attendance Sheet Excel Template
  • Employee Performance Evaluation Excel Template
  • Employee Provident Fund Calculator Excel Template
  • Job Candidate Tracker Excel Template
  • Employee Resignation Schedule Excel Template
  • Employee TA Reimbursement Excel Template
  • Employee Contract Renewal Schedule Excel Template
  • Employee Overtime Calculator Excel Template
  • Simple Age Calculator Excel Template
  • Employee Mileage Log Excel Template
  • Multiple Employees Weekly Timesheet Excel Template
  • 9 Ready-To-Use Salary Slip Excel Templates
  • Salary Sheet With Attendance Register In Single Excel Template
  • Weekly Timesheet With Payslip Excel Template
  • Salary Breakup Report Excel Template
  • Gratuity Calculator (India) Excel Template
  • Taxable Gratuity Calculator Excel Template
  • Employee Training Log Excel Template
  • Employee Vacation Tracker Excel Template
  • Salary Arrears Calculator Excel Template
  • Resume/Cover Letter Excel Template
  • Professional Resignation Letter Excel Template
  • Employee Termination Letter Excel Template
  • Employee Promotion Letter Excel Template
  • Salary Certificate Format Excel Template
  • Absconding Letter Excel Template
  • Employee Disciplinary Memo Excel Template
  • Casual Leave Application Excel Template
  • Employee Transfer Letter Excel Template
  • Employee Show Cause Notice Excel Template
  • Promotion Request Letter Excel Template
  • Transfer Request Letter Excel Template
  • Job Rejection Letter Excel Template
  • Promotion Rejection Letter Excel Template
  • Transfer Refusal Letter Excel Template
  • Transfer Rejection Letter Excel Template
  • Employee Welcome Letter Excel Template
  • Employee Thank You Letter Excel Template
  • Layoff Notice Letter Excel Template
  • Employment Verification Letter Excel Template
  • Job Offer Letter Excel Template
  • Accommodation Request Letter Excel Template
  • Loan Application Letter Excel Template
  • Workplace Harassment Complaint Letter Excel Template
  • Internship Request Letter Excel Template
  • Additional Facility Request Letter Excel Template
  • Salary Increment Request Letter Excel Template
  • Employee Of The Month Certificate Excel Template

ExcelDataPro

Free Excel Pro Templates

  • Glossary
    • Accounting Glossary
    • HR & Payroll Glossary
    • Tax Glossary
  • Templates
    • Accounting
      • Financial Analysis
      • Financial Statement
    • Educational
    • Taxation
      • Federal Income Tax
      • GST
      • UAE VAT
      • UK VAT
    • Human Resources
      • HR & Payroll
      • HR Metrics
    • School Management
    • Social Media
    • Sales-Marketing
    • Personal Finance
    • Other
      • Health & Fitness
  • Functions
  • FAQ’s
  • Blog
  • Home
  • Contact
  • Privacy
  • Terms

© ExcelDataPro 2023. Content Licensed Under Creative Commons with Attribution Required

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT