How to Generate Attendance, Calculate Salary & Prepare Pay Slips in Excel

Hello Friends! Today I 'am sharing with you an excel file which will be very helpful for you for your HR & Payroll needs. This excel file is an all-in-one solution for Attendance tracking, Leave tracking, Salary Calculation including PF & ESIC as well as Salary Slip. You just have to feed data to the blank cells and all calculations will be done automatically.

So let me explain you how to use that excel file for the same. Below is the blank screenshot please refer:

So, This is the very first sheet of the excel, here you should first type your company name in place of NAME OF YOUR COMPANY. After that please select the month from the drop down list and type the year for which you are preparing your attendance. Please note that you have to type the number of days for the month so that the calculation will be made accordingly. 

Now, Please fill the serial no & employee names. Just after that you can see that there are headings like CL, SL, EL & OL the full form of the same can be seen on the right side of the sheet on the screenshot. I have filled the same with attendance for your reference you can see the same below in the screenshot:

 

Here, you can see that I have credited the CL, SL, and EL to all the employees and marked their attendance with the key assigned. You can also highlight the holiday by changing W to H below the dates. It will highlight the entire row. 

Now, we will move to the second sheet of the file which is Salary Calculation sheet. below is the screenshot which you need to fill only. Don't alter other filled rows or columns as each contain the formulas and linked to other cell. It will effect the data validation. So please go through the screenshot below which you need to fill out:

Please note, you don't have to write employee names and serial number again as it is linked to the first sheet. It will be filled automatically. You need to fill the designation and CTC part and after that employee credentials only. The salary will be automatically calculated along with deductions of PF and ESIC. I have filled the same for your reference you can check below:

Now, you have done with your data entries. You don't have to do again and again. Just make a copy of the file and copy the closing balance of the leaves from the old sheet to new sheet and just mark the attendance, your calculations will be done automatically and immediately.   

Now, you can also take out the print out of salary slips from the third sheet Salary Slip. You just have to select employee name from the right side drop down list and salary slip of that employee will be generated automatically. You can also add your personalised logo of your company for aesthetic look. Screenshot of the print preview of the salary slip is shown below:

So Friends, I hope you like the post and hope it will be very helpful for your personal or professional work. If you have any question regarding the post please comment below.

Link to download the excel file:

https://docs.google.com/spreadsheets/d/1qCBuSoZjYBl6qjW47jfFjlrScwZjjVc_/edit?usp=sharing&ouid=115965330529719153777&rtpof=true&sd=true

If you want to view the steps practically, please visit our YouTube Channel @CueTricks link for the video given below:

https://youtu.be/ZApc-z1cv_Y

Please don't forget to like, share and subscribe our YouTube channel for future updates and support. Thank You!