Fully Automatic Payroll Management System in Excel 🔥 | Attendance + Salary + Salary Slip, by DATAMENTIX on YouTube. 38 min 47 s.
- Video length
- 38 min 47 s
- Time to do it
- 1 hour
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Create a workbook with three sheets named Attendance, Salary and Salary Slip. Keep one employee list with ID, name and basic salary on the salary sheet.
On the attendance sheet, list employee IDs down column A and put each working date across row 1 as a column header, so one row covers a full month.
Select the day cells and open Data > Data Validation. Allow a list with the values P, A and H so you can mark present, absent and half day from a dropdown.
Count the month with COUNTIF. Use =COUNTIF(B2:AF2,"P") for present days, then repeat with "A" and "H" to get absent and half days for each employee.
Add conditional formatting to the attendance grid so P turns green and A turns red. Use Home > Conditional Formatting > Highlight Cells Rules > Equal To.
On the salary sheet, pull each person's basic pay with VLOOKUP on the employee ID, for example =VLOOKUP(A2,Employees!$A$2:$C$50,3,0).
Work out pay per day by dividing basic salary by the number of days in the month. Multiply it by present days plus half of the half days, using IF to handle each case.
Add PF and ESI columns. PF is usually 12 percent of basic and the employee ESI share about 0.75 percent of gross pay, so check your local rates before you rely on them.
Build the salary slip on its own sheet. Pick an employee from a dropdown and use VLOOKUP to fill name, days, earnings, deductions and net pay from the salary sheet.
Test the system by changing a few attendance marks. The counts, salary figures and slip should all update without any manual editing.
Tips and common mistakes
Use absolute references like $A$2:$C$50 in VLOOKUP, or the range shifts when you copy the formula down.
Protect the sheet with Review > Protect Sheet after testing so nobody overwrites the formulas by accident.
Keep one row per employee with no blank rows in between, or COUNTIF and VLOOKUP will miss records.
Enter dates as real dates rather than typed text, so the day count for each month stays correct.
Questions
Can I build this in Google Sheets?
Yes. COUNTIF, VLOOKUP, IF, data validation and conditional formatting all work there, though the menu names sit in slightly different places.
Why does my VLOOKUP return #N/A?
The employee ID on the salary sheet does not match the ID in the employee list. Check for extra spaces and make sure both are stored the same way, as text or as numbers.
How do I add overtime or a bonus?
Add one column for overtime hours and one for the rate, then add that amount to gross pay before you calculate PF and ESI.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
