Ravindra BagaleCourses & study guides मराठी Track your progress

21. Real-World Project Briefs

21.11 Salary Slip Creation

By Last updated:

Example first. Shahrukh in HR has the employee master and the November attendance from 21.4 HR Attendance and Payroll Sheet. He wants one printable salary slip: pick an Emp ID from a drop-down and the whole slip fills itself. For Salman (EMP-103) the net pay is ₹11,568.

Illustrative rates, not legal advice

The PF rate (12% of basic, on basic up to ₹25,000), the professional tax (₹200 when gross is above ₹10,000) and the salary split used here are practice inputs for this class only. Statutory rules change and differ by state and company. Always take the current rates from your HR, payroll or CA team before you pay anyone.

Salary slip with XLOOKUP — in 30 seconds

What: A salary slip is a one-page layout. One drop-down cell holds the Emp ID, and every other line is an XLOOKUP into the master and attendance Tables plus a little arithmetic.

Why: Typing 50 slips by hand guarantees mistakes. One template with lookups gives the same layout every month, and printing a slip is just choosing an Emp ID.

Tiny example: Choose EMP-103 in C4. Paid days 22 of 26. Basic earned =ROUND(7800*22/26,0) = ₹6,600. Gross ₹13,560 − deductions ₹1,992 = net ₹11,568.

First look at the data (before → after):

Salary slip with XLOOKUP: before and after tables, fictional Blinkit Maharashtra data

Expected result — Salary slip for EMP-103 (November 2026)

Earnings ₹ Deductions ₹
Basic 6,600 PF (12% of basic) 792
HRA 2,640 Professional tax 200
Special allowance 2,945 Advance recovery 1,000
Conveyance 1,015 — —
Overtime 360 — —
Gross earnings 13,560 Total deductions 1,992
Net pay 11,568 — —
Before table and formula as text

Before — Sheet Master: Table tblEmp (monthly full amounts, ₹)

Emp ID Name Designation Basic HRA Special allow. Conveyance
EMP-101 Raja Rider 7,800 3,120 3,480 1,200
EMP-102 Rani Picker 6,500 2,600 2,700 1,200
EMP-103 Salman Rider 7,800 3,120 3,480 1,200
EMP-104 Ravina Store lead 11,700 4,680 5,820 1,200
EMP-105 Zoya Picker 6,500 2,600 2,700 1,200

Before — Sheet Attendance: Table tblAtt (November, working days = 26)

Emp ID Paid days OT hours OT rate (₹/hr) Advance (₹)
EMP-101 26 10 60 0
EMP-102 24 0 50 0
EMP-103 22 6 60 1000
EMP-104 26 4 90 0
EMP-105 25 8 50 500

Formula / click path

C4: drop-down (Data Validation › List › =EmpIDs)
C5: =XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Name])
C8: =ROUND(XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Basic])*$C$7/WorkDays,0)
F8: =ROUND(MIN(C8,25000)*12%,0)   F9: =IF(C13>10000,200,0)

Do it step by step:

  1. Put the master in a Table tblEmp and the attendance in a Table tblAtt.
  2. Name cell Settings!B1 (value 26) WorkDays, and name the Emp ID column: Formulas › Name Manager › New, Name EmpIDs, Refers to =tblEmp[Emp ID].
  3. On the Slip sheet, select C4 and add Data › Data Validation › List with Source =EmpIDs.
  4. In C5 type =XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Name]). Do the same for Designation and Paid days.
  5. In C8 type =ROUND(XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Basic])*$C$7/WorkDays,0) and repeat for HRA, Special allowance and Conveyance.
  6. Add the deductions, then Net pay =C13-F13. Choose another Emp ID in C4 and check that every line changes.

The slip layout (sheet Slip)

Cell Label Formula
C4 Emp ID drop-down, Source =EmpIDs
C5 Name =XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Name])
C6 Designation =XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Designation])
C7 Paid days =XLOOKUP($C$4,tblAtt[Emp ID],tblAtt[Paid days])
C8 Basic =ROUND(XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Basic])*$C$7/WorkDays,0)
C9 HRA =ROUND(XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[HRA])*$C$7/WorkDays,0)
C10 Special allowance =ROUND(XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Special allow.])*$C$7/WorkDays,0)
C11 Conveyance =ROUND(XLOOKUP($C$4,tblEmp[Emp ID],tblEmp[Conveyance])*$C$7/WorkDays,0)
C12 Overtime =XLOOKUP($C$4,tblAtt[Emp ID],tblAtt[OT hours])*XLOOKUP($C$4,tblAtt[Emp ID],tblAtt[OT rate (₹/hr)])
C13 Gross earnings =SUM(C8:C12)
F8 PF (illustrative 12% of basic, basic capped at ₹25,000) =ROUND(MIN(C8,25000)*12%,0)
F9 Professional tax (illustrative) =IF(C13>10000,200,0)
F10 Advance recovery =XLOOKUP($C$4,tblAtt[Emp ID],tblAtt[Advance (₹)])
F13 Total deductions =SUM(F8:F10)
C15 Net pay =C13-F13

Net pay for every employee (check your slip by changing C4):

Emp ID Name Paid days Gross (₹) PF (₹) Prof. tax (₹) Advance (₹) Net pay (₹)
EMP-101 Raja 26 16,200 936 200 0 15,064
EMP-102 Rani 24 12,000 720 200 0 11,080
EMP-103 Salman 22 13,560 792 200 1,000 11,568
EMP-104 Ravina 26 23,760 1,404 200 0 22,156
EMP-105 Zoya 25 12,900 750 200 500 11,450

Total net pay for the five slips: ₹71,318.

Make it print well (one slip per page)

  1. Select the slip area, for example B2:F17, and click Page Layout › Print Area › Set Print Area.
  2. Click Page Layout › Orientation › Portrait and Page Layout › Size › A4.
  3. In Page Layout › Scale to Fit, set Width: 1 page and Height: 1 page.
  4. Open Page Layout › Margins › Custom Margins and tick Center on page: Horizontally.
  5. Add the company name and "Salary slip for November 2026 (practice)" in Insert › Header & Footer.
  6. Click View › Page Break Preview to check that the slip is on one page, then File › Print to see the preview.

To print all five slips, change C4 and print each one. In Module 12 you can record a small macro that loops through EmpIDs and prints each slip.

Practice task (try it before you open the answer):

  1. Build the master, attendance and slip sheets above.
  2. Choose EMP-105 (Zoya) in C4.
  3. Read gross, PF, professional tax, advance and net pay.
  4. Change Zoya's paid days to 26 in tblAtt and read the net pay again.
Answer

EMP-105 with 25 paid days: gross ₹12,900, PF ₹750, professional tax ₹200, advance ₹500, net pay ₹11,450. With 26 paid days the gross becomes ₹13,400 and the net pay ₹11,920.

Ravindra Bagale's Tip

Never type a person's name on the slip. Only the Emp ID is chosen; everything else is looked up. Two people with the same name is the most common payroll mix-up.

Quick recap

  • Read the ask twice and list the deliverables before you type a formula.
  • Put raw data in a Table, calculations in new columns, and the answer on a separate sheet.
  • Totals of ratios are always SUM ÷ SUM, never the average of percentages.
  • Write the checks down and do them every month; they are part of the deliverable.

Loading practice tasks...

  1. Total leave days (Easy): Add up Leave days in D2:D11.
  2. Average salary (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Sales (Easy): How many rows have Department "Sales"?
  4. Largest leave days (Easy): The largest value in D2:D11.
  5. Leave days for Sales (Medium): Total of D2:D11 where B is "Sales".
  6. Sales in Denver (Medium): Total of D2:D11 where B is "Sales" and C is "Denver".
  7. Best in Sales (Medium): Largest E2:E11 value where B is "Sales".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Noah Wilson (Medium): The Leave days for "Noah Wilson" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Sales (Hard): Share of D2:D11 that is "Sales", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →