Financial data integrity checks before reporting

Financial data integrity checks test whether your reporting workbook includes the right periods and records, uses valid classifications and reconciles to the finance sources. Run them before calculating performance measures. An internally balanced file can still omit a whole month or put an expense in the wrong place.

Freeze the scope and preserve the export

Write down the entities, reporting periods, currency and ledger status expected in the report. Preserve the original export and perform checks on a working copy. Keep an exception register with the failed check, source row, amount, responsible owner and resolution evidence.

For this example, US01's workbook should cover July through September 2026 in USD. It contains journal-line detail, not complete financial statements. That distinction determines which tests can run immediately and which need additional reports.

Place these columns in A:H on a sheet named Ledger. Period cells are actual spreadsheet dates representing the first day of the month, displayed below in US date format. Row numbers are spreadsheet row references, outside the A:H data.

These illustrative rows contain separate completeness, duplication and mapping problems. Fixing one does not close the others.

Check required fields and period coverage

In I2, test identifiers, date type and numeric debit-credit values, then fill down:

=IF(OR(A2="",NOT(ISNUMBER(B2)),C2="",D2="",E2="",F2="",NOT(ISNUMBER(G2)),NOT(ISNUMBER(H2))),"REVIEW","OK")

This avoids treating text amounts as usable numbers. Also inspect whether the dates belong to approved fiscal periods and whether the entity and currency match the scope. A populated value is not necessarily a valid value.

On a separate Coverage sheet, enter July 1, August 1 and September 1, 2026 in A2:A4 as dates. In B2, fill down:

=COUNTIFS(Ledger!$A$2:$A$8,"US01",Ledger!$B$2:$B$8,A2)

The raw row counts are July 2, August 0 and September 5. August needs investigation: zero records means no evidence of coverage, not proof of no activity. Build the expected list from the reporting calendar rather than from the export; otherwise an absent month never appears in the test.

Test the complete journal-line key

The source owner confirms that entity, period, journal and line form the unique key. In J2, fill down:

=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2,$D$2:$D$8,D2)

Rows 4 and 6 both return 2. That flags a repeated key; it does not authorize deletion. Compare the source export, confirm whether the line was copied twice, and preserve the explanation for any exclusion.

Microsoft documents COUNTIFS as counting rows where all supplied criteria match. The ranges must have the same dimensions. Avoid using amount alone as a duplicate test: equal amounts occur in legitimate transactions.

Check account mappings and journal balance

Put the approved account map in Map!A2:B4: 1100 is trade receivables, 2000 is trade payables and 4000 is revenue. Keep account identifiers consistently formatted in both sheets. In K2, fill down:

=IF(COUNTIF(Map!$A$2:$A$4,E2)=1,"OK","REVIEW")

Account 5999 fails because it is absent. A count above 1 would also fail because the map gives the same account multiple destinations. The controller must approve its classification; matching totals will not reveal an expense assigned to revenue.

For journal balance in L2, fill down:

=ROUND(SUMIFS($G$2:$G$8,$A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2,$F$2:$F$8,F2)-SUMIFS($H$2:$H$8,$A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2,$F$2:$F$8,F2),2)

This tests each entity-period-journal-currency group, rounded to cents. Microsoft's SUMIFS documentation explains summing only values that meet all conditions. Here J200 is out by $12,000; J100 and J300 balance.

Reconcile the correction, then check statements

Suppose the source owner confirms row 6 is an extra copy. Excluding only that confirmed duplicate produces:

The corrected debit total is $10,000 + $12,000 + $3,000 = $25,000; credits are the same three amounts. The arithmetic now balances, but August and account 5999 remain unresolved. The workbook is not ready for a complete quarterly report.

For prepared statements, also tie closing cash to the reconciled cash report, receivables to the aging, and retained earnings to opening balance plus earnings and approved equity movements. The distinction between an independent source tie and an internal equation is covered in control total and identity. Use financial data reconciliation for investigating the differences, rather than plugging them into a balancing line.

Where it goes wrong

• Deleting repeated amounts. Similar transactions are not necessarily duplicate records; use the source-defined key.
• Replacing missing months with zero. This turns an unresolved coverage problem into a misleading performance result.
• Checking only the net balance. Omitted balanced entries and mapping errors can pass a debit-credit test.
• Losing the source trail. An edited workbook without the original file and exception log cannot explain how its rows changed.

These formulas use Excel. Covirage is not affiliated with Microsoft. For the broader review of explanations and evidence, see AI for financial analysis.

Check your finance workbook in Covirage

Bring your ledger export, approved account map and matching statement totals: Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See the approach to metrics governance.