The Panel You Run Before Trusting Any File

Ten cells, every time

Everything in this course collapses into one habit: before computing anything on a new file, build a small block of checks beside it and read it. It takes two minutes, it is the same block every time, and it catches the errors that are otherwise found by the person reading your report.

The checks

• Row count — =COUNTA(A2:A1000) against what the sender said the file contains. A mismatch means truncation, a filter left on, or a blank row cutting the block in two.
• Type integrity — =COUNTA(B2:B1000)-COUNT(B2:B1000) for every numeric column. Must be zero.
• Missingness — =COUNTBLANK(B2:B1000) per column, and as a share of rows.
• Range plausibility — =MIN(...) and =MAX(...). A negative quantity, an age of 250, a price of 0 — the extremes are where data entry errors live.
• Impossible values — =COUNTIF(B2:B1000,"<0") for anything that cannot be negative.
• Duplicate keys — =SUMPRODUCT(--(COUNTIF(A2:A1000,A2:A1000)>1)).
• Category hygiene — a small PivotTable of the text columns, or COUNTIF per expected label, to expose Zurich / Zürich / ZH.
• Totals against the source — if the sender quoted a total, reproduce it before you do anything else.

Read the extremes as data, not as noise

MIN and MAX are the cheapest quality check in Excel because errors are rarely subtle. A quantity of -12 is not an outlier to be discussed in the next course; it is a typo, a return booked as a sale, or a sign convention that changed halfway through the file. Deciding which of the three it is requires asking the person who produced the data — and that conversation is much easier before you have published a report built on it.

Write down what you changed

Keep a Cleaning log sheet: date, what you found, what you did, how many rows it affected. Two lines per fix. It costs nothing, it makes your analysis reproducible, and when the figures change between two versions of a report, it is the only document that explains why. If you cannot say which rows you removed and on what rule, you cannot defend the result — and in the courses that follow, every method assumes you can name the population your data represents.

What analysis-ready actually means

One rectangular block, one header row, one row per observation, every numeric column genuinely numeric, dates as real dates, missing values genuinely empty, keys unique and trimmed, categories spelled one way, totals outside the block, and a log of everything you touched. That is the whole checklist, and it is the entry condition for the next course, where you start summarising these columns — because a mean, a standard deviation and a quartile are only as honest as the column beneath them.