VASCOM TECHNOLOGY AUTOMATION CO., LTD
Home › News › 3 Excel Formulas to Cross-Check Payroll Before Finalizing

3 Excel Formulas to Cross-Check Payroll Before Finalizing

Apply three Excel cross-checks to reconcile attendance, headcount, and accounting entries, flag duplicate or missing payroll rows, and resolve discrepancies before finalizing payroll and issuing payslips.

Finalizing payroll by looking only at the total at the bottom of the page can easily leave discrepancies undetected, because a missing row can be masked by a row counted twice. The three cross-checks below help review timekeeping data, the employee roster, and accounting entries before payslips are issued. Each check returns either a variance figure or a warning label, so payroll staff do not have to inspect every cell manually. Run these checks on a copy of the data and keep the original file unchanged for traceability when needed.

Prepare the data before entering formulas

The three tables required are the payroll table, the timesheet, and the journal entry table. All three must cover the same payroll period, include the same group of employees, and use consistent employee IDs. Do not reconcile records by full name, because duplicate names, extra spaces, or differences in spelling can all produce incorrect results.

  • Standardize employee IDs to the same data type and remove leading and trailing spaces; do not use merged cells within the data range.
  • Keep pay based on days worked, allowances, bonuses, deductions, and net pay separate; do not combine these items into a single column.
  • Remove total rows, repeated header rows, and draft entries from the calculation range; specify the payroll period in a separate reference cell.

If an employee has multiple contracts, positions, or pay sources in the same period, determine the appropriate check key in advance. In that case, the employee ID alone is not sufficient; combine it with the contract ID or pay item ID to avoid false duplicate warnings.

Formula one: reconcile total pay with the timesheet

Total pay cannot be subtracted directly from total days worked. The first item to reconcile is the number of days used to calculate pay on each row. In the payroll table, add a “Reconciled days” column and use a conditional sum function to retrieve data from the timesheet:

=SUMIFS(ChamCong[CongHuongLuong];ChamCong[MaNV];[@MaNV];ChamCong[Ky];KyLuong)

Then create a “Days worked variance” column by subtracting the days just retrieved from the days in the payroll table. Only rows with a result of 0 should proceed to the pay-based-on-days-worked check. For pay calculated using standard working days, the following formula can be added:

=ROUND([@LuongTheoCong]-[@LuongCoBan]/[@CongChuan]*[@CongDaDoiChieu];2)

A result other than 0 usually indicates an incorrect standard day count, an outdated base salary, omitted paid leave, or a formula referencing the wrong period. Overtime, night work, and hourly amounts should be checked separately rather than included with regular days worked. Once each row reconciles, add up the columns for pay based on days worked, allowances, bonuses, and deductions again and compare them with total pay; this helps determine whether the discrepancy comes from days worked or from a specific pay item.

Formula two: find duplicate and missing rows with count functions

To find duplicate rows in the payroll table, add a check column using the employee ID and payroll period as conditions:

=IF(COUNTIFS(BangLuong[MaNV];[@MaNV];BangLuong[Ky];KyLuong)>1;”Duplicate check”;””)

Do not delete the flagged rows immediately. First determine whether they result from duplicate entry or a valid case, such as one person having two independent pay items. If business rules allow multiple rows, add the pay item ID to both conditions of the count function.

To find missing rows, place the formula on the list of employees due to be paid or the list of employees with days worked in the period, rather than on the payroll table itself:

=IF(COUNTIFS(BangLuong[MaNV];[@MaNV];BangLuong[Ky];KyLuong)=0;”Missing from payroll table”;”Present”)

Also run the check in the reverse direction: count each ID from the payroll table in the employee roster. This identifies people who have left the company, incorrect IDs, or records outside the period’s scope that still appear in the table. Before drawing conclusions, remove blank rows and check cases that are allowed to have no pay, such as unpaid leave for the entire period.

Formula three: compare the table total with the journal entry total

The final comparison must use the same measure on both sides. If the journal entry records payroll payable, compare it with the payroll table’s total payable; do not use net pay if the entry has not yet deducted insurance, taxes, and other withholdings. Also select the correct payroll recognition entry, and do not include payment entries or unrelated adjustments.

=ROUND(SUM(BangLuong[LuongPhaiTra])-SUMIFS(ButToan[SoTien];ButToan[Ky];KyLuong;ButToan[MaNghiepVu];”GhiNhanLuong”;ButToan[TaiKhoan];”334″);2)

A result of 0 shows that the totals on both sides match within the selected scope. If the result is not 0, check the accounting period, posting status, employee roster, reversing entries, and rounding in sequence. When entries are split by department, compare each department first and then add the results; an amount posted to the wrong department can still leave the company-wide total matched while the costs for individual departments are incorrect.

What to do now

  1. Add three check columns: days worked variance, duplicate rows, and missing rows.
  2. Create a variance cell between the payroll total and the journal entry total using the same measure.
  3. Finalize payroll only after all warnings have been explained and the adjustments have been saved.

Keep these three formulas fixed as a reusable template, with notes on the scope and measures being reconciled. VASCOM recommends retaining both the check results and the data change log, so the next period can be reviewed quickly instead of rebuilding the process from scratch.

Tips and how-to Talk to VASCOM
More news

Keep reading

Zalo ☎
VASCOM
Call 0822.577.579 Get a consultation