Cleaning an Excel spreadsheet before analysis

Cleaning Excel spreadsheet data starts with preserving the export, checking its dates and identifiers, and proving what changed. Work in helper columns so the original values remain available. Resolve missing facts with the source owner before treating the workbook as a complete business record.

Preserve the source and define one row

Keep an untouched worksheet named Raw. Record the export's report name, entity, currency, reporting period and extraction date. Copy the working rows to a separate sheet and assign a source-row reference that stays with each record.

Write down what one row represents: an invoice, an invoice line or a ledger posting. That determines the identifier you will test. An invoice number can be unique in an invoice summary and repeated correctly in a line-level report.

For an illustrative US distributor, columns A through D contain these raw values. The amount for INV-504 is genuinely absent; the known subtotal is not a complete revenue total.

There are six source rows and five populated numeric amounts. In the source sheet, record both checks:

=COUNTA(A2:A7)
=COUNT(D2:D7)
=SUM(D2:D7)

The separate count prevents the known subtotal from disguising a missing amount.

Standardize keys without changing their meaning

Treat customer identifiers as text so leading zeros remain significant. Do not add or remove zeros unless the source owner confirms the identifier format. For a proposed invoice key in E2, use:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Microsoft's data cleaning guidance explains the different roles of TRIM, CLEAN and SUBSTITUTE in removing unwanted spaces and characters. Inspect changes rather than overwriting column A. Do not apply a text-normalizing rule to an identifier whose internal spaces carry meaning.

Flag missing cleaned identifiers and count repeated keys in F2 and G2:

=IF(E2="","REVIEW","OK")
=COUNTIF($E$2:$E$7,E2)

A repeated key is a review item. Here, the source owner confirms that the second INV-501 is an export duplicate. Document its source row and $1,400 amount before excluding it. Microsoft recommends reviewing duplicates and retaining the original because Remove Duplicates deletes data.

Convert dates and test the reporting period

In H2, retain a numeric date or convert recognized text, leaving failures visible:

=IF(C2="","REVIEW",IF(ISNUMBER(C2),C2,IFERROR(DATEVALUE(C2),"REVIEW")))

Apply a date display format to valid results. Microsoft states that DATEVALUE converts date text to a date serial number, and system date settings can affect interpretation. This example assumes the confirmed US month/day/year convention for text dates.

Put the reporting-period start in Controls!B2 and the exclusive period end in Controls!B3. For October 2026, enter October 1, 2026 and November 1, 2026. In I2:

=IF(ISNUMBER(H2),IF(AND(H2>=Controls!$B$2,H2<Controls!$B$3),"OK","REVIEW"),"REVIEW")

INV-502 converts to October 2. INV-503's invalid day stays under review. An out-of-period numeric date also fails the period test, even if its cell looks like a date.

Review amounts, blanks and formulas

In J2, distinguish absent amounts from conversion errors:

=IF(D2="","REVIEW",IFERROR(VALUE(SUBSTITUTE(D2,",","")),"REVIEW"))

This assumes the source uses US numeric punctuation and the column contains one confirmed currency. Confirm the convention before applying it. A genuine zero remains numeric; INV-504 remains REVIEW.

If the working file contains formulas, inspect errors, inconsistent formulas within a calculation column, and references that stop before the final source row. Keep an error visible until resolved. Replacing every error with zero can make a broken calculation look like a legitimate business result.

For recurring preparation, the Power Query guide covers the refresh process. Use the Excel data cleaning tools comparison when choosing how to maintain the checklist.

Reconcile accepted rows and exceptions

After the duplicate decision, retain five invoices. Hold INV-503 for its date and INV-504 for its missing amount. The other three invoices are ready for this period's analysis.

The known-amount bridge is $4,750 minus $1,400 equals $3,350; $2,750 ready plus $600 held also equals $3,350. The row bridge is six minus one equals five; three ready plus two held equals five. All displayed amounts use whole dollars without rounding.

The missing amount is still unknown, so $3,350 is a known subtotal rather than a certified total. Correct the source and rebuild these controls before reporting complete revenue. Use data comparison in Excel when checking the corrected export against the original.

Where it goes wrong

• Formatting instead of converting. A date-like display does not establish a valid date or the right period.
• Deleting repeated invoice numbers blindly. A line-level file needs a line-level key.
• Filling all blanks with zero. Unknown amounts then disappear into apparently complete totals.
• Sorting only one column. Identifiers and amounts can become detached. Operate on the complete row set.

Covirage is not affiliated with Microsoft.

Check your reviewed file in Covirage

Upload the reviewed workbook with its business identifiers and clearly identified exceptions. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains them 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 reporting context.