Most Indian companies start on Excel and stay longer than they should. The payroll sheet grows. Someone adds a column no one understands. A formula breaks and nobody notices for two months. By the time the decision is made to move to proper software, the Excel file is a 12-tab monster that three people have edited over four years, with salary data, LOP records, PF working, and attendance all mixed together, all fragile.
Moving off Excel isn’t technically hard. The hard part is cleaning up the data before it goes into the new system. Everything else is straightforward.
Step 1 Pick the Right Time to Switch
The cleanest cut is 1 April start of the new financial year. You close out March in Excel, do the year-end calculations, and April’s payroll runs on the new system. No mid-year YTD data to carry across. No partial-year TDS calculations to reconcile. Everything starts fresh.
If April isn’t possible, the next cleanest option is the first of any month. Never mid-month. A mid-month migration means prorating the first payroll run on the new system, which creates confusion and leaves the door open for errors.
If you’re migrating mid-year: you need to carry YTD figures into the new system gross salary paid month by month, TDS deducted, PF contributions, ESI contributions, and LOP days for every employee, from April to the migration month. Without these, the system will recalculate TDS from zero and employees will get incorrect tax deductions for the rest of the year. This is the step most Excel migrations miss.
Step 2 Audit the Excel Data Before Moving Anything
This is where the real work is. Excel data collected over years has errors and those errors are invisible until they cause a problem in the new system. Run through these before doing anything else:
Headcount check. How many active employees are in the payroll sheet? Compare against your actual headcount. It’s common to find people who left 18 months ago still in the sheet as inactive rows that somehow didn’t get deleted, and occasionally to find someone missing entirely.
Duplicate UAN numbers. More common than you’d expect. If two employees show the same UAN, one of them is wrong. An incorrect UAN means PF contributions are going to the wrong account which is a serious compliance error that the employee will discover when they check their EPFO passbook.
PAN format verification. Open the PAN column, filter for entries that aren’t exactly 10 characters or don’t match the format (5 letters + 4 digits + 1 letter). Any employee with a missing or invalid PAN gets TDS deducted at 20% much higher than the standard rate. Check before importing.
Salary structure consistency. In Excel, someone often lumps ‘special allowance’ and ‘other payments’ into one cell. HRMS tools require each salary component separately basic, HRA, special allowance, conveyance, and so on. You’ll need to split these before the import.
Step 3 Clean the Data
| Field | Common Excel Problem | How to Clean It |
|---|---|---|
| Employee name | Multiple spellings of the same name (‘Rahul Sharma’ vs ‘Rahul kumar Sharma’) | Standardise to name as per PAN card that’s the legal name for TDS |
| PAN number | Missing for some employees, format errors (lowercase, spaces) | Format: 5 uppercase letters + 4 digits + 1 uppercase letter. Blank PAN means TDS at 20%. |
| Date of birth / joining | Mixed formats (01-Jan-2019 vs 01/01/2019 vs 1.1.2019) | Convert all to DD/MM/YYYY. Verify DoB for employees near PF age limits. |
| Bank account + IFSC | Old account numbers, merged bank IFSC codes (e.g. ING Vysya → Kotak) | Collect fresh cancelled cheques or bank letters. IFSC lookup via RBI website. |
| UAN number | Blank or wrong employee changed jobs and got a new UAN | Pull from EPFO member portal (passbook.epfo.gov.in). One UAN per person for life. |
| Salary components | ‘Basic + HRA + Other’ with no breakdown | Each component needs its own column. HRMS won’t accept a single merged figure. |
| Leave balances | Approximate or not maintained | Get accurate opening balance from the last register / approval records before migration date. |
Don’t skip the IFSC check. Several bank mergers in recent years (ING Vysya into Kotak, Lakshmi Vilas Bank into DBS, Yes Bank restructuring) mean some employees still have old IFSC codes in the payroll sheet that no longer work. A salary transfer bounced on the first payroll run on the new system is not a good start.
Step 4 Set Up the HRMS Before Importing Any Data
Configure the system before bringing data in. A common mistake is importing employee records into a default setup and then trying to fix the salary structures afterwards which requires re-importing. Get the configuration right first.
What to configure before import:
Company and pay schedule: financial year, salary payment date, monthly or twice-monthly pay cycle.
Salary components: create each component basic, HRA, special allowance, conveyance, LTA with the correct PF inclusion settings. Basic and DA are in PF. HRA and most allowances are not. Get this wrong and PF calculations will be off from the first run.
Professional Tax slabs: configure per state for each state where you have employees. PT slabs vary and update; verify current rates before configuring. Minimum Wage and PT reference.
Leave types and accrual rules: set up casual leave, earned leave, sick leave with the correct annual entitlement and accrual frequency before importing leave balances.
PF and ESI settings: employer and employee contribution rates, wage ceilings (PF: ₹15,000; ESI: ₹21,000 gross), admin and EDLI rates.
Step 5 Import in the Right Order
Employee master data first, then salary structures, then leave balances, then YTD figures if mid-year. Each step depends on the one before it being correct.
Most HRMS tools provide an import template, a CSV or Excel file with the exact column headers the system expects. Use that template, don’t create your own. Map your cleaned Excel data into the template column by column. Double-check the mandatory fields: employee code, name, date of joining, PAN, UAN, bank account, IFSC, basic salary.
After importing, verify the count. The number of active employees in the HRMS should exactly match your headcount. If it’s off by even one, find the discrepancy before proceeding. An employee who didn’t import correctly won’t be in the payroll run.
Step 6 Run a Test Payroll Before Going Live
Don’t run the first live payroll without testing. Select 10 employees across different salary structures someone with a salary revision this month, someone with LOP, someone on ESI, someone above the PF ceiling, a mid-month joiner. Run payroll for them in the new system and compare the output line by line against what the Excel calculation would have produced.
If the numbers match: run the full payroll with confidence.
If there’s a discrepancy: trace it back to configuration before it affects 80 people’s salaries. Common issues wrong divisor (26 vs 30), PF ceiling incorrectly set, HRA exemption calculation logic different from what Excel was doing, PT slab misconfigured for one state.
Step 7 Don’t Delete the Excel File
Keep it. Archive it, but keep it. For at least 3 years which is the minimum wage register retention requirement under the Code on Wages. You’ll need to look things up: an employee who left before migration asks for salary data from two years ago, a labour inspector asks for attendance records from the year before you switched, someone’s Form 16 from before migration needs to be reprinted.
Store the file in a named, dated folder with access limited to HR and finance. It’s not your active payroll system anymore but it’s your historical record.
For companies switching to Waggex: the onboarding team provides an import template and helps configure salary structures and PF/ESI settings. The dedicated account manager runs the first payroll alongside your team, so the first month is a guided process rather than a solo effort. Free for up to 10 employees start here.
For a complete checklist of what a payroll migration involves beyond the Excel specifics: Payroll Migration Checklist Switching Software Without Errors.