Blog · Data quality and reconciliation · Finance and FP&A teams
Compare Excel data cleaning tools for missing values, inconsistent names and duplicate business records. Use a small invoice export to choose a method and prove what changed.
Excel data cleaning tools work best when you match the method to the problem: text formulas for inconsistent labels, duplicate review for repeated records, and repeatable preparation for recurring exports. Start with a control total and keep the original rows. A cleaner-looking workbook is useful only if you can explain every change to its amounts and record counts.
A finance team cleaning an invoice export needs more than consistent customer names. It needs to know whether an amount is missing, whether a repeated invoice is legitimate, and whether the file still represents the same reporting period.
Compare methods against those jobs rather than searching for a single button that fixes every workbook.
| Method | Best use | Evidence to retain | Business decision still required |
|---|---|---|---|
| Worksheet formulas | Visible corrections to text and labels | Original and proposed values side by side | Whether two labels mean the same customer |
| Duplicate highlighting and review | Investigating repeated identifiers | Repeated rows, selected key and disposition | Whether repetition is an error or a valid line |
| Power Query preparation | Applying an agreed process to recurring exports | Preparation steps and before-and-after controls | Whether the agreed rules still fit the new export |
| Source-owner correction | Resolving missing or conflicting facts | Corrected export or documented confirmation | What the transaction should actually contain |
These methods can work together. A controller may approve a customer mapping, an analyst may apply it, and a reviewer may reconcile the resulting workbook. The choice should reflect who owns the rule and who can check it next month.
For the operational sequence, use the separate spreadsheet cleaning checklist. This comparison focuses on choosing the method.
Consider this illustrative export from a US distributor. Each row is supposed to represent one invoice. Brackets describe source defects and are not literal customer names.
| Source row | Invoice ID | Customer label | Region | Amount (USD) |
|---|---|---|---|---|
| A | INV-201 | Acme Supply [trailing space] | East | 1,200 |
| B | INV-202 | ACME SUPPLY | East | 800 |
| C | INV-201 | Acme Supply [trailing space] | East | 1,200 |
| D | INV-203 | Harbor Parts | [blank] | 500 |
| E | INV-204 | Harbor Parts | West | 300 |
| F | INV-205 | Mesa Industrial | West | 250 |
| Raw total | 4,250 |
The source owner confirms that row C repeats row A and should be excluded. The owner also confirms that Acme Supply and ACME SUPPLY refer to the same customer. The region for INV-203 remains unresolved.
Those confirmations matter more than the interface used to apply them. Without them, changing names or removing rows would be an assumption about the business.
Put a proposed normalized label beside the original customer field. If that field is C2, a starting formula is:
=UPPER(TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160)," "))))
Microsoft describes using TRIM, CLEAN and SUBSTITUTE together for unwanted spaces and nonprinting characters. The formula above also applies a consistent case. Keep that proposed value in a helper column so reviewers can see what changed.
Use this approach when the correction is narrow and explainable. It can make the two Acme labels consistent without changing their invoice amounts. It cannot establish that a similarly named legal entity is the same customer. Use a reviewed customer-ID mapping for that decision.
After normalization, Acme's retained invoices total $2,000: $1,200 plus $800. That customer subtotal should be traceable to those invoices, rather than to a manually typed replacement total.
Microsoft documents conditional formatting for finding duplicates and Remove Duplicates for deleting them. The deletion acts on the selected duplicate criteria, so choose those criteria from the export's intended row definition.
For this example, the confirmed invoice-level key is Invoice ID. On a line-level export, it might instead be Invoice ID plus Line ID. A customer name is not sufficient: several valid invoices can belong to one customer.
Highlight the repeated key, compare the complete records and assign a disposition before excluding anything. Keep the original row reference and the reason. Here, the approved exclusion is row C for $1,200. The other Acme invoice stays because INV-202 is a different transaction.
When two exports disagree rather than repeat themselves, use data comparison in Excel to investigate the missing and changed records.
Power Query's data profiling tools show column quality, distribution and profile information. Microsoft notes that profiling initially covers the first 1,000 rows unless you switch to the entire dataset. That is a setting to check before using a profile as evidence about a complete file.
This approach fits a recurring export when the business has already agreed on the preparation rules. Decide which columns must exist, which types they should contain, and which exceptions must stop the analysis. A missing region should remain an exception until someone supplies the fact.
The detailed Power Query in Excel guide owns the preparation and refresh walkthrough. Whatever method you use, bring the same row and amount controls forward each time; repeating a process does not prove the rules are still appropriate.
The accepted changes reconcile as follows. All amounts are whole dollars, so no rounding is required.
| Control | Rows | Amount (USD) |
|---|---|---|
| Original export | 6 | 4,250 |
| Confirmed duplicate excluded | 1 | 1,200 |
| Retained invoices | 5 | 3,050 |
| Retained invoice awaiting region | 1 | 500 |
| Invoices ready for regional analysis | 4 | 2,550 |
The amount proof is $4,250 minus $1,200 equals $3,050. The regional split also reconciles: $2,550 assigned plus $500 unresolved equals $3,050. The row proof is six original rows minus one duplicate equals five retained invoices.
The missing region does not justify discarding $500 from company revenue. It means a regional analysis needs an explicit unresolved bucket or must wait for correction. A tool that produces a neat regional chart without exposing that limitation has not answered the finance question completely.
Once those controls pass, visualizing data in Excel can help communicate the cleaned results. Chart choice should follow the approved population and definitions.
Covirage is not affiliated with Microsoft.
Bring your reviewed invoice workbook, including its identifiers, amounts and unresolved records, to Covirage. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for finance teams for the business context.
Use formulas when a reviewer needs to inspect each correction beside the original value. Use duplicate highlighting when repeated keys need investigation. Consider Power Query for a recurring preparation process. The best fit is the method whose output you can reconcile and whose rules the business owner can explain.
Excel can remove duplicates based on selected columns, but safety depends on choosing the correct record key. Repeated invoice numbers may represent separate lines or a duplicated export. Keep the original file, review the repeated records and document the amount removed before accepting the result.
Only when the source owner confirms that zero is the correct business value. An empty amount can mean an incomplete export, an unposted transaction or a field that does not apply. Hold unresolved records separately and disclose their effect on the completeness of the analysis.