Clean Messy Excel and CSV Data Before Reporting

Exports from accounting systems, CRMs and ERPs rarely arrive ready for a pivot table. Numbers come in as text, dates flip between day-first and month-first, and the same customer appears under three spellings. The checklist below walks through the problems that most often break a report, in the order it makes sense to fix them, with a Power Query step and a formula alternative where both exist.

1. Keep the raw export and profile it first

Never clean the only copy. Save the original export in a read-only "raw" folder and do all cleaning in a separate workbook or query that reads from it. That way you can rerun the cleanup next month and prove what changed.

Before fixing anything, look at what you have. In the Power Query editor, turn on View > Column quality, Column distribution and Column profile. They show the share of valid, error and empty values in each column and how many distinct values it holds. By default, profiling covers only the first 1,000 rows; switch the status bar option to "Column profiling based on entire data set" for a full picture.

2. Remove title rows, blank rows and merged cells

Many exports start with a report title, a run date and a blank line before the real header. Skip those rows and promote the header:

let
    Source = Csv.Document(File.Contents("C:\Data\sales_export.csv"), [Delimiter = ",", Encoding = 65001]),
    SkipTitle = Table.Skip(Source, 3),
    Promoted = Table.PromoteHeaders(SkipTitle, [PromoteAllScalars = true])
in
    Promoted

Encoding = 65001 reads the file as UTF-8, which keeps accented names intact. If the number of title rows varies, use Table.Skip(Source, each [Column1] <> "Invoice No") to skip until the header row appears.

Merged cells in Excel sources usually arrive in Power Query as a value in the first row and nulls below it. Select the column and use Transform > Fill > Down (Table.FillDown) to repeat the group label on every row.

3. Make sure numbers are numbers and dates are dates

A column that looks numeric may be stored as text, which makes SUM ignore it. Quick worksheet checks:

• =COUNT(B2:B5000) versus =COUNTA(B2:B5000) - if the counts differ, some entries are not numbers.
• =ISNUMBER(B2) on a suspect cell returns FALSE for text that looks like a number.

The reverse also happens: account codes, ZIP codes and SKUs with leading zeros should stay text. In Power Query, set code columns to Text before any automatic type detection runs, or the leading zeros are gone for good.

4. Handle regional date and decimal formats

Is 03/04/2025 March 4 or April 3? Is 1.234,56 one thousand or one? The answer depends on the system that produced the file, not on your computer's settings.

Double-clicking a CSV lets Excel guess using your local settings. Day-first dates can then be split: values that fit month-first (day 12 or lower) are silently converted to the wrong date, while the rest stay as text. Import through Data > From Text/CSV instead, so you control the conversion.

In Power Query, use Change Type > Using Locale, or pass the source culture directly:

Typed = Table.TransformColumnTypes(
    Promoted,
    {{"InvoiceDate", type date}, {"Amount", type number}},
    "de-DE"
)

With worksheet formulas, convert text explicitly:

• =NUMBERVALUE(C2, ",", ".") reads 1.234,56 as 1234.56 (decimal separator first, group separator second).
• =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) turns the text 03/04/2025 into April 3, 2025. This only works if every value uses two-digit days and months.

5. Strip extra spaces, non-breaking spaces and hidden characters

Trailing spaces make "ACME" and "ACME " two different customers. Web and PDF sources often add non-breaking spaces (character 160), which Excel's TRIM function does not remove on its own. Combine three functions:

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

The Power Query equivalent:

Cleaned = Table.TransformColumns(
    Typed,
    {{"Customer", each Text.Trim(Text.Clean(Text.Replace(_, "#(00A0)", " "))), type text}}
)

One difference to know: Excel's TRIM also collapses double spaces inside the text, while Text.Trim only removes leading and trailing characters.

6. Standardize codes and names with a mapping table

When the same thing appears as "NY", "N.Y." and "New York", do not fix it with a chain of nested IF or Replace Values steps. Build a two-column mapping table (RawValue, CleanValue) on its own sheet, so business users can maintain it without touching the logic.

With formulas (Microsoft 365 or Excel 2021 and later):

=XLOOKUP(TRIM(A2), Map[RawValue], Map[CleanValue], "UNMAPPED")

In older versions, INDEX/MATCH does the same job. In Power Query, merge against the mapping table:

Merged = Table.NestedJoin(Cleaned, {"Region"}, RegionMap, {"RawValue"}, "Map", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Map", {"CleanValue"}, {"RegionClean"})

Power Query comparisons are case-sensitive, so convert both sides to upper case before the merge. Send any row that comes back empty to an exception list for review instead of quietly labeling it "Other."

7. Decide what a blank means

A blank, a zero, a dash and "N/A" can each mean something different: no sale, a zero sale, not applicable, or data not received yet. The choice affects your results. AVERAGE ignores blank cells but includes zeros, so the same column can produce two different averages depending on how gaps are stored.

Agree on a rule per column and write it down. Then apply it consistently, for example by turning placeholder dashes into true nulls before typing the column:

NoDashes = Table.ReplaceValue(Source, "-", null, Replacer.ReplaceValue, {"Amount"})

8. Find duplicates before you remove them

First, define what makes a row unique: invoice number alone, or invoice number plus line number? Two identical order lines can be legitimate.

• Formula flag: =COUNTIFS(A:A, A2, C:C, C2)>1 marks rows whose key appears more than once.
• Power Query: select the key columns and use Keep Rows > Keep Duplicates to review them, then Remove Rows > Remove Duplicates once you are sure.

Two cautions from Microsoft's own documentation: duplicate detection in Power Query is case-sensitive, and Remove Duplicates does not guarantee which instance of a duplicate it keeps. If it matters which one survives, sort and buffer the table first, or pick the row explicitly with a grouping step.

9. Combine files whose column names do not match

The Get Data > From Folder combine feature works when every file has the same structure. In practice, a system upgrade renames "Cust Name" to "Customer Name" halfway through the year. Standardize names inside the Transform Sample File query, before the files are appended:

Renamed = Table.RenameColumns(
    PromotedHeaders,
    {{"Cust Name", "Customer"}, {"Customer Name", "Customer"}, {"Amt", "Amount"}},
    MissingField.Ignore
)

MissingField.Ignore skips names that a given file does not contain. Columns that exist in only some files come through as nulls in the combined table, so check for unexpectedly empty columns after the combine. Keep the Source.Name column so every row can be traced back to its file.

10. Reconcile totals against the source

Cleaning is not finished until the numbers tie out. Build a small check sheet that compares the cleaned data to a figure from the source system:

• Row count per file or per period.
• Control totals, such as the sum of amounts per month, compared to the system's own summary report.
• A difference column, for example =SUMIFS(Clean[Amount], Clean[Month], E2) - F2, with a PASS/FAIL flag next to it.

If the totals do not match, the cause is usually one of the steps above: a filtered header row that was actually data, a text amount that was dropped, or duplicates removed too aggressively.

Formulas or Power Query: which should you use?

Formulas are fine for a one-off fix on a small sheet, and anyone can see how they work. For anything you repeat each week or month, Power Query is usually the better fit: the steps are recorded once, rerun with Refresh, and do not rely on someone copying formulas down far enough. For worked examples, see the Excel automation demos built on synthetic data.

Limitations and trade-offs

• Cleaning cannot fix wrong data. If the source system records the wrong region, a mapping table will faithfully map the wrong value. Fixes at the source last longer.
• Mapping tables need an owner. New codes appear every month. Someone has to review the exception list, or "UNMAPPED" rows build up unnoticed.
• Worksheet size is finite. A worksheet holds 1,048,576 rows. Larger datasets should be loaded to the Data Model from Power Query rather than to a sheet.
• Locale assumptions can break. If the vendor changes its export settings, a culture-specific type step may start producing errors. Errors are better than silent misreads, but someone needs to notice them.
• Some decisions are judgment calls. Whether two similar customer names are the same company is a business question, not a technical one.

If your cleanup feeds a recurring report, the steps above can be packaged into a refreshable workbook as part of an Excel automation project, or into a reporting model through data analytics and Power BI work.

When to bring in a specialist

If the same export takes hours to clean every cycle, or the cleanup lives in one person's head, it is worth turning it into a documented, refreshable process. You can describe your project with a sample file, and a specialist will review it and send a fixed quote.