How My Excel Sheet Simplifies Salary and Payslip Management – Your Feedback Needed!

Dear All,

I am very glad to post my first assignment on CiteHR. I have been using this site for more than 4 months, and I am very happy with the information it provides on Admin, HR, Accounts, and Management. Especially, I enjoy the content related to management, personality development, and motivating presentations.

Now, it is my turn. I am attaching this Excel sheet. I am confident that you, good people, will really admire my work.

I have created this Excel sheet using VLOOKUP command and other functions. I assure you that these functions will definitely help you understand how to use the VLOOKUP command in Excel and its benefits.

I have highlighted some cells in yellow; you just need to input the necessary information in those cells.

Simply input the attendance in the Attendance sheet, and it will generate the Salary Sheet, Payslip, Bank List, PF ESIC Deduction List, and PT summary.

I am eagerly awaiting your responses and suggestions. I am also looking forward to new assignments from you.

I will update this Excel file regularly and post it on this site.

PASSWORD FOR FILE IS - 090909

Thanks & Regards,
(My email ID is sandeepmore_finance@rediffmail.com)

Dear Sandeep,

I downloaded the Excel file, but it says the Excel sheet is protected and asks for a password. Please help me.

Regards,
Voichal

Hi Sandeep,

I really appreciate your great work. It's truly helpful for processing salaries and statutory requirements.

Keep in touch...

Regards,
Manjunath
Human Resources
PSK Engineering Pvt. Ltd
Bangalore

Hi Sandeep,

This is really good work, and thank you very much for your effort. I believe it needs some improvement, such as if an employee's salary attendance is not for a full month, then we need to calculate their salary manually and enter it into the sheet. It would be extremely beneficial if the number of days entered in the attendance sheet could be calculated automatically.

Overall, the work is excellent.

Thank you.

Hi,

The posting is very useful. In fact, I was looking for something like this. It would be a great help if you could prepare Form-16 as well as the full and final statement in this Excel sheet (it's just a suggestion).

Thank you.

Hi Vishal,

Could you please clarify if the TDS is deducted every month from the employee's salary or if it is deducted once a year? Additionally, could you describe the process of calculating TDS from an employee's salary in an industrial company?

Regards,
Vishal Harjani

Hi Sandeep,

Really, it is good work. I appreciate your hard work. Keep it up. Please clarify the following:
1. In the yellow-colored marked area, can we create any rows or columns?
2. Professional Tax has more slabs in Andhra Pradesh, but your files say only 3. Can we meet this requirement?
3. Provide some options for insertion, such as adding more columns in the salary breakup and deduction areas, etc.

Regards,
Sreedhar

Master, thank you for your efforts; they are appreciated by all. Well done. If I want to increase the list of employees, how can I do that? Can I copy the rows in all sheets, and will it work? If you have time, please reply. Thank you.

Ilayahkumar
Nigeria

Dear Sir,

I believe that when we input attendance days into the attendance sheet, it has an impact on the salary sheet.

Regards,
Sandeep

Tremendous amount of labor and effort must have been put in. Really a good one.

My question to you: We color a particular cell. If we want the color to change when we input the data to help us identify which cells are still empty and where we need to enter the data. Can you explore and let me know?

Thanks & Regards

Hi Sandeep,

Amazing, very nicely prepared. I am surprised that we both work in the same office and you are sitting next to my table, yet I was not aware that you are posting wonderful stuff. It will be helpful not only for those handling payroll but also to understand Excel commands. Keep it up and please keep on posting more.

Thanks & Regards,
Rajesh Auchare

Dear Sandeep,

I must appreciate the good job. Let me enhance your salary sheet to make it more attractive, similar to how payroll software appears. I will provide you with the updated version by the end of September 16th, 2009.

Regards,
Shilpi Kakkar
Human Resources Manager
APS Technology Pvt. Ltd.

Dear Mr. Sandeep,

I am very impressed by your Excel sheet wherein we can access various formats such as payslips, ESI, PF, etc.

Thank you for providing such a useful tool.

Warm regards

Hi Sandeep,

This is a great master sheet. You have really prepared a multipurpose sheet, but one thing is, could you add an option for automatic changes in months and years? This would make calculations easier, as otherwise, you would have to do the same exercise every month, which is time-consuming.

Another suggestion is to include different types of leaves such as cl, sl, el, and holidays in the attendance section for monthly calculations.

I hope that what I am writing will be understood correctly, and I appreciate your consideration.

Thank You

Hi,

Thank you for your appreciation and valuable suggestions. I am updating this Excel sheet based on your good people's suggestions. One day, we will have all the information regarding salaries and various reports generated from it. I really want suggestions from you to update this Excel sheet. Additionally, you good people can also make changes as per requirements.

Please continue following this thread. I will post the updated Excel sheet on this thread very soon.

Thanks & Regards,
Sandeep

Dear Sanjeev,

Thanks for your appreciation. Please find attached herewith the formal memo for later coming.

Regards,
Sandeep

Dear Girish,

Thanks for your appreciation. I am always here to discuss the problems you have faced in understanding some VLOOKUP formulas. Please reply with your queries.

Thanks and Regards,
Sandeep

Can anyone tell me the meanings of GLS on a salary slip? Is GLS received every month? What are the criteria for FMCG in GLS? Can anyone explain it to me briefly?

Hi Sandeep,

I will be using this for our attendance timesheets at work. Thank you. I will let you know if any amendments are required. Keep up the innovation.

Regards,
Alex

Hi, I am Suresh Kumar. I am working in a pharmacy company. Could you please provide me with the format for the salary sheet, muster, pay slip, and all salary-related information? Thank you.

Dear Sir, I am writing to inform you that there is a significant issue with opening attachments of any answers. When I first joined citiHR, everything was very up to date, but nowadays, I am facing problems opening any attachments despite being a registered user. Could you please investigate why this is happening and resolve the issue? Even when I try to open attachments with a waiting caption, it does not open, whether it's after 60 seconds or even 100 seconds. It simply does not open.

Thank you,

Regards,
Divya Shukla
[Email Removed For Privacy Reasons]

Dear Sandeep Jee,

Excellent! It is really helpful for everyone. Please provide us with the same idea in Accounts (trading, PL a/c, Balance Sheet, TDS, TCS, PF, ESI, etc).

With regards,
Rajan Mishra

Yes, excellent work. I want to use it, but the problem is I am not so good with Excel sheets and don't know how to make formulas. Can anyone suggest how I can learn Excel sheets? Any book or something.

Sandeep,

The sheet is fabulous. I am actually a starter in General HR and hope it will support me a lot. I believe it is one of the best structures.

Regards,
Soujanya Hegde

Hi Sandeep, I really appreciate your work. I am also using it and have prepared my sheet for company employees for the first time based on your template. Could you please explain the rules for HRA and LTC? Employees are requesting to include those categories that are exempt from income tax so that they can invest less and pay lower taxes.

I have already availed meal vouchers up to ₹15,000 per annum, medical reimbursement of ₹15,000 per annum, ₹9,600 for conveyance, and ₹2,400 for children's allowance. Can you please assist me in understanding HRA and LTC better?

Thank you.

Dear Sandeep, the information you have posted is really important, but you have given a password to the Excel sheet. Please post it again without a password.

Regards,
Asmita

Hi Pratima, Following is the information regarding HRA and LTA:

HRA Exemption

HRA exemption = minimum of (40% (50% for metros) of Basic+DA or HRA or rent paid - 10% of Basic+DA).

LTA Exemption

LTA is exempt to the tune of economy class airfare for the family to any destination in India, by the shortest route. LTA can be claimed twice in a block of 4 calendar years. The current block is from Jan 2010 to Dec 2013.

Regards, Pratima Jagtap

Hi Sandeep, I really appreciate your work. I am also using it and prepared my sheet for company employees keeping your base. Can you explain the rules for HRA and LTC? Employees are demanding to include those heads which are exempted in income tax so that they can have the benefit of investing less and paying less tax. I have already taken meal vouchers up to 15000 p.a, medical reimbursement-15000 p.a, 9600 conveyance, and 2400 children allowance. Can you please help me to provide information on HRA and LTC?

Dear all, herewith, I am attaching the copy of the Gujarat Professional Tax Form 5, which we have to submit with the professional tax challan. I have created this form with a lot of searching and hard work. I believe it will be beneficial not only for Gujarati HR professionals but also as a guide for professionals in other states to create such descriptive and ready-to-use forms for freshers.

Thank you.

It's good, but please check that ESI has been deducted from those employees who are drawing 10,000 and below. According to the amendment, it was raised to 15,000. Please check. The work is quite appreciable.

The sheet you posted is really very useful. Great work!

Dear Sandeep,

I appreciate your work, but I was looking for a sheet in Excel so I can see the whole year's salary of all employees in one sheet.

Thanks,
Gaurav

Hi Sandeep,

I appreciate your efforts. The salary sheet is protected. Please provide the password.

Regards,
RAJENDRAN

Very good efforts you have taken to explain the role of an HR Generalist. Your explanation is clear and concise, making it easy to understand the responsibilities involved in this position. Thank you for sharing such valuable insights.