How to Clean Messy Excel Data Before Analysis

Research on spreadsheet errors has found for decades that most real-world spreadsheets contain mistakes — and the most common ones aren't formula bugs. They're data problems: a total that quietly excludes rows because they're stored as text, a pivot that double-counts a "Total" row, a date column where half the cells are strings.

Cleaning is unglamorous, but it's where analysis is won or lost. This is a checklist of the seven problems that come up in almost every file, in the order you should look for them, with the fastest fix for each. It applies whether you're building formulas yourself or handing the file to an AI assistant.

1. The header isn't on row 1

Symptom: The sheet opens with a title ("Q3 Sales Report"), a blank row, maybe a logo, and the real column names on row 5.

Why it matters: Every tool — pivot tables, Power Query, AI assistants — has to guess which row holds the names. Guess wrong and "Q3 Sales Report" becomes your first column header.

Fix: Delete the rows above the header, or copy the data block to a new sheet starting at A1. If the file is regenerated regularly, do this once in Power Query (Home → Remove Rows → Remove Top Rows) so it repeats.

ChatExcel scans the first few rows for a header-like row (mostly text, mostly filled, short labels), so a title row or two is tolerated — but a clean row 1 is always safer.

2. Placeholders instead of blanks

Symptom: Cells containing N/A, n/a, -, --, TBD, ?, or a space.

Why it matters: =SUM() ignores text, so the total silently drops those rows. =AVERAGE() too — which means your average is computed over fewer rows than you think. Worse, COUNT and COUNTA disagree, so row counts drift depending on which someone used.

Fix: Select the column, Find & Replace each placeholder with nothing. For a column with several placeholders, a helper column: =IF(OR(A2="N/A",A2="-",A2=""),"",A2).

ChatExcel converts the common placeholders (-, --, N/A, NA, #N/A, null, empty) to blanks on load and reports how many rows a total excludes.

3. Numbers stored as text

Symptom: Numbers are left-aligned; there's a green triangle in the corner; =SUM of an obviously numeric column returns 0 or a suspiciously round figure.

Why it matters: Text that looks like a number is not a number. Formulas skip it; sorts put "10" before "9"; charts plot nothing.

Fix, fastest: Select the column → Data → Text to Columns → Finish. It re-parses every cell. For stubborn cases (a non-breaking space from a web copy-paste), =VALUE(TRIM(CLEAN(A2))) in a helper column, then paste values.

Currency and thousands separators ($2,400.50) are the same problem in disguise. =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) strips both.

4. Mixed or ambiguous dates

Symptom: A date column where some cells are right-aligned (real dates) and some left-aligned (text), or where sorting produces 01/02/2024, 01/03/2024, 02/01/2024 in an order that makes no sense.

Why it matters: 03/04/2024 is March 4 or April 3 depending on the locale that wrote it. A column that mixes the two conventions cannot be fixed by formatting — the values themselves are different dates.

Fix: Establish which convention the source uses (check a date with a day above 12, like 25/12/2024). Then =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) for day-first or =DATE(RIGHT(A2,4), LEFT(A2,2), MID(A2,4,2)) for month-first, paste values, format as date. Store as ISO (yyyy-mm-dd) if the file will be exported again — it's the only unambiguous format.

5. Total and subtotal rows inside the data

Symptom: A row labeled "Total", "Subtotal", "Grand Total", or a region name in bold with the sum beneath a group.

Why it matters: This is the single most common cause of doubled totals. A pivot or SUM over the whole column adds the subtotals to the rows they summarize.

Fix: Filter the label column for "total" (case-insensitive), delete those rows, and rebuild subtotals as a pivot or with SUBTOTAL() outside the data block. If the sheet is a report someone else maintains, don't edit it — copy the data rows to a new sheet.

AI assistants can be told to skip these — "ignore any row where Region is 'Total'" — and ChatExcel's assistant flags likely total rows when it sees them, but removing them is cleaner.

6. Duplicate rows

Symptom: The same order ID appears twice, often because an export was run twice and appended, or because a join in the source system fanned out.

Why it matters: Every count and total is inflated, and not uniformly — the duplicates are usually concentrated in one date range.

Fix: For exact duplicates, Data → Remove Duplicates with all columns selected. For duplicates on a key, select just the ID column in that dialog. To see them first: =COUNTIF(A:A, A2)>1 in a helper column, then filter for TRUE.

Ask an AI assistant "are there duplicate order IDs?" before anything else; it's a ten-second check that saves a wrong meeting.

7. Merged cells and one-value-per-block layouts

Symptom: A region name appears once, merged across five rows, with the five rows beneath it blank in that column.

Why it matters: Merged cells are a presentation feature. Analytically the blank rows have no region, so grouping by region drops them.

Fix: Unmerge (Home → Merge & Center → Unmerge), then fill down: select the column, Find & Select → Go To Special → Blanks, type = and the cell above, press Ctrl+Enter, then paste values.

The five-minute checklist

Before analyzing any file:

1. Header on row 1? (Problem 1)
2. =COUNT() and =COUNTA() agree on numeric columns? (Problems 2, 3)
3. Dates right-aligned and sort chronologically? (Problem 4)
4. Filter the label column for "total" — anything? (Problem 5)
5. Remove Duplicates preview shows zero? (Problem 6)
6. Any merged cells in the data block? (Problem 7)

If all six pass, formulas, pivots, and AI answers will agree with each other. If any fail, fix them first — the analysis will be faster and, more importantly, right.

Let the assistant find the problems

If you'd rather not hunt manually, upload the file to ChatExcel and ask: "Are there any data quality issues I should know about?" The assistant checks for blanks and non-numeric values in numeric columns, duplicate keys, out-of-range dates, and rows that look like totals, and tells you which ones affect the answers you're about to ask for.