Blog · Data quality and reconciliation · Finance and FP&A teams
Check a business workbook for missing fields, inconsistent identifiers, invalid dates and repeated records. Follow a worked invoice export through source-total reconciliation and an evidence-based exception review.
Business spreadsheet data quality is the set of checks that establishes whether your workbook can support the question you want to answer. Check the population, required fields, identifiers, dates, repeated records and control totals before calculating a business measure. A workbook can reconcile in dollars while still being unusable for a customer or monthly breakdown.
Start with the export you already have. Write down the legal entity, currency, reporting period, source system and export time. Keep an unchanged copy and a source row reference so every exception can be found again.
Define the grain in business terms. For an invoice register, one row might mean one invoice line rather than one invoice. An entity, document number and line number can identify that record; a customer name cannot. Preserve leading zeros in account and customer codes, and compare identifiers with the approved account list rather than guessing that similar names belong together.
The checks below use Google Sheets with a US locale and real spreadsheet dates. Covirage is not affiliated with Google. For the broader proof that grouped detail must still match its source, see the roll-up identity and reconciliation.
An illustrative US distributor receives this September 2026 export. Put the headers in A1:G1 and the records in rows 2 through 7. A dash represents an empty cell; leave it empty when recreating the workbook. Amounts are signed USD: credit memos reduce revenue.
| Source row | Entity | Document | Line | Customer | Posting date | Amount (USD) |
|---|---|---|---|---|---|---|
| R001 | US-01 | INV-101 | 1 | C001 | September 1, 2026 | 12,000 |
| R002 | US-01 | INV-101 | 1 | C001 | September 1, 2026 | 12,000 |
| R003 | US-01 | INV-102 | 1 | C002 | September 2, 2026 | 8,000 |
| R004 | US-01 | INV-103 | 1 | — | September 3, 2026 | 5,000 |
| R005 | US-01 | CM-104 | 1 | C004 | September 4, 2026 | -1,000 |
| R006 | US-01 | INV-105 | 1 | C005 | — | 4,000 |
The independent September source report states net revenue of $28,000. The imported amount column totals $40,000, giving an unexplained $12,000 difference. Record both totals before excluding anything; the comparison is not permission to remove a row that happens to match the gap.
In H2, flag missing entity, document, line, customer or posting-date fields, then fill down:
=IF(OR(B2="",C2="",D2="",E2="",F2=""),"Missing required field","Present")
This flags R004 and R006. A populated field is only the first check. Match the customer code to the customer register, and test that each date is a numeric spreadsheet date inside the reporting period. In I2:
=IF(F2="","Missing date",IF(NOT(ISNUMBER(F2)),"Text date",IF(AND(F2>=DATE(2026,9,1),F2<DATE(2026,10,1)),"In period","Outside period")))
Test amounts separately with =ISNUMBER(G2). A numeric-looking text value can be ignored by a sum without looking unusual on screen. Do not use a general error handler to replace every failed conversion with zero; retain the exception and ask for a corrected source value.
Required fields depend on the business question. A revenue total and a customer margin report need different supporting columns. The loss-reason completeness example illustrates why completeness should be measured against the records where a field is actually required.
In J2, count occurrences of the entity, document and line combination, then fill down:
=COUNTIFS($B$2:$B$7,B2,$C$2:$C$7,C2,$D$2:$D$7,D2)
Google documents COUNTIFS as counting records that satisfy multiple criteria, with equally sized criteria ranges. R001 and R002 each return 2; every other record returns 1. That means two flagged rows in one repeated group, not two excess copies.
Check the original transaction evidence. Here, the source owner confirms that R002 repeats the same invoice line and is an import copy. Enter Keep in K for all other records and Exclude for R002. Preserve R002 and the reason in the review sheet. In a different workbook, repeated keys could identify valid split postings or an incomplete key.
To reproduce the baseline by entity:
=SUMIFS($G$2:$G$7,$B$2:$B$7,"US-01")
To sum only the reviewed records:
=SUMIFS($G$2:$G$7,$B$2:$B$7,"US-01",$K$2:$K$7,"Keep")
Google's SUMIFS documentation explains the sum range and the criteria pairs used here. Extend every range together when importing more rows.
| Control | Calculation from the export | Result (USD) |
|---|---|---|
| Raw imported amount | 12,000 + 12,000 + 8,000 + 5,000 - 1,000 + 4,000 | 40,000 |
| Confirmed extra copy | R002 | 12,000 |
| Reviewed amount | 40,000 - 12,000 | 28,000 |
| Independent source total | September source report | 28,000 |
| Remaining difference | 28,000 - 28,000 | 0 |
All amounts are exact to whole dollars; no rounding is needed. The arithmetic now ties, but the missing customer and posting date remain unresolved. Get those values from source evidence before approving a customer or period analysis. The financial data reconciliation guide covers differences between separate financial sources in more detail.
Assign each unresolved item an owner and evidence reference. Use consistent business measure definitions so the reviewed population means the same thing in the workbook and the management report.
Bring a file or Google Sheet with the original identifiers, dates, amounts and source control 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 how Covirage supports finance teams.
Start with the population: entity, period, currency and what each row represents. Record the source row count and signed amount total before changing anything. Otherwise, later checks may be accurate for a file that already excludes relevant records.
No. An invoice can contain several product lines, and different entities can reuse document numbers. Define a key that includes the entity, document and line identifier, then inspect source evidence before deciding that a repeated business record is an extra copy.
Yes. Missing customer identifiers can prevent a reliable customer analysis even when revenue agrees with the ledger. Offset errors can also cancel. Check field completeness, individual record groups and source totals separately, and describe which questions the remaining exceptions prevent you from answering.
Only when the source owner confirms that zero is the actual business value. A blank can mean missing, not applicable or not yet received. Keep those meanings distinct so that a spreadsheet formula does not quietly turn an unknown amount into a measured result.