Prepare at a time 50 Employees Form 16 Part A and B in Excel for FY 2025-26

Employers streamline tax compliance by generating Form 16 certificates in bulk using Excel. This approach saves time for preparing documents for 50 employees during FY 2025-26 (AY 2026-27).

What is Form 16?

Form 16 certifies Tax Deducted at Source (TDS) on salaries under Section 203 of the Income Tax Act. Consequently, employees rely on it for accurate ITR filing. Moreover, it is split into Part A and Part B for comprehensive details.

Part A summarises quarterly TDS deductions and deposits from the TRACES portal. In contrast, Part B details salary breakdowns, exemptions, and deductions. Therefore, both parts together provide complete proof of income and tax paid.

Key Differences: Part A vs Part B

This table clarifies distinctions, so users quickly grasp each part’s role. As a result, preparation becomes targeted.​

Why Bulk Preparation Matters

Handling 50 employees manually wastes hours on repetitive entries. However, Excel automation with formulas and templates handles bulk tasks efficiently. For instance, HR teams reduce errors and meet the June 15 deadline for issuing Form 16.​

Additionally, accurate bulk Forms 16 ensure compliance with the latest tax regimes (old/new). Thus, employees file ITRs seamlessly.​

Gather Essential Data First

Collect employee master data, including names, PANs, gross salaries, HRA, allowances, and deductions. Then, compile payroll registers for FY 2025-26 salaries.​

Moreover, verify TAN/PAN accuracy upfront to avoid reworks. Consequently, this step prevents common pitfalls like mismatched details.

Set Up Excel Workbook

Create a new Excel file with multiple sheets: “Employee Data”, “Part A Template”, “PartB_Template”, and “Master_Output”. Importantly, structure “EmployeeData” with columns for Name, PAN, Gross Salary, Deductions, TDS Amount.

For example, use Row 1 headers: A=Employee Name, B=PAN, C=Basic Pay, D=HRA, E=Total Allowances, F=Section 80C, G=Taxable Income, H=TDS. This layout supports 50+ rows easily.​

Automate with Formulas and VLOOKUP

Enter formulas to calculate totals. For instance, in Column G (Taxable Income): =C2+D2+E2-F2-G2, assuming the standard deduction applies.​

Furthermore, use VLOOKUP to pull Part A TDS data: =VLOOKUP(B2, ‘PartA_Template’!A: H, 8, FALSE) fetches matching TDS for each PAN. As a result, the bulk population happens instantly for 50 employees.​

Additionally, apply SUMIF for quarterly TDS summaries: =SUMIF(‘PartA_Template’!B:B, B2, ‘PartA_Template’!H:H). Therefore, dynamic updates occur as data changes.​

Design Part A Template

Copy TRACES Part A format into “PartA_Template” sheet. Include employer details (Name, TAN, Address), employee PAN, employment period (01-Apr-2025 to 31-Mar-2026), and Q1-Q4 TDS tables.

Then, link to EmployeeData using INDEX-MATCH for scalability: =INDEX(‘EmployeeData’!H: H, MATCH(B2, ‘EmployeeData’!B:B, 0)). Moreover, format with borders and bold headers for an official look.

Build Part B Template

Replicate official Part B layout with salary breakup: Basic Salary, Dearness Allowance, HRA (exempt/non-exempt), Perquisites, Profits in Lieu, Deductions (80C up to ₹1.5L, 80D), and Tax Payable.

Use dynamic formulas like IF for exemptions: =IF(D2<=500000, D2*0.4, “Calculate Actual”). Consequently, it adapts to new/old regimes. Also, compute final TDS: =G2 * TaxSlabRate – Rebates.​

For bulk, reference the EmployeeData row-by-row. Thus, generate 50 individualised Part Bs via copy-paste or VBA.​

Handle 50 Employees in Bulk

Populate the EmployeeData sheet with all 50 records first. Then, create a macro-enabled button: Press to loop through rows and populate Part A/B sheets individually.​

Alternatively, use Power Query to merge data dynamically. For example, load payroll CSV, transform columns, and output 50 PDFs. However, simple drag-fill formulas suffice for starters.​

In addition, protect sheets with passwords after setup. Therefore, data integrity remains intact during reviews.

Verify and Validate Outputs

Cross-check totals against payroll summaries using SUM formulas. Next, validate PANs via Excel’s Data Validation or online tools. Moreover, ensure TDS matches TRACES deposits quarterly.​

Commonly, errors arise from formula mismatches, so test on 5 sample employees first. As a result, the full bulk run proceeds confidently.

Convert and Distribute Securely

Save each Form 16 as PDF: Select range, File > Export > PDF, password-protect with employee PAN. Finally, email individually or upload to the HR portal by June 15, 2026.

Additionally, maintain a log sheet tracking issuance dates. Thus, audit trails stay complete.

Best Practices and Tips

Always update templates for FY 2025-26 slabs (e.g., new regime standard deduction ₹75,000). Furthermore, back up files before bulk runs. In summary, this method empowers HR to prepare 50 Forms 16 swiftly and error-free.

Download and prepare at a time 50 Employees Form 16 Part A and B in Excel for FY 2025-26

Features of this Excel Utility Are:-

Preparing Form 16 Part A and Part B for 50 employees at one time can feel challenging; however, with Excel, the process becomes simple and well-organised. Instead of struggling with repetitive manual work, employers and HR professionals can rely on Excel to manage large datasets efficiently.

• Firstly, Excel allows you to enter employee details in a structured format, which creates a strong foundation for error-free preparation.
• Secondly, once formulas are applied, tax calculations update automatically, saving valuable time.
• Moreover, Excel ensures consistency across all employee records, so every Form 16 follows the same format.
• In addition, handling salary components, deductions, and exemptions becomes easier because everything stays visible on a single sheet. As a result, the chances of calculation mistakes reduce significantly.
• Meanwhile, verification also becomes faster, as totals and tax figures can be cross-checked instantly.
• Furthermore, generating Form 16 individually from bulk data is effortless using filters and templates.
• Likewise, updates for FY 2025-26 can be applied centrally without reworking each file. Consequently, employers meet compliance deadlines comfortably. Therefore, Excel acts like a smart assistant that works silently in the background.
• Ultimately, preparing Form 16 for 50 employees at a time in Excel brings speed, accuracy, and peace of mind to the entire process.