Raw stays raw, and the table should be tidy

In Asking a question data can answer you wrote an analysis plan for "East city sales are down. Why?" The plan named a dataset: daily sales by store and category for East city's 54 stores, for the 8 weeks to Sunday 18 October 2026. Tomás, Mangrove's data analyst, now sends you that export as a CSV file. Before you look at a single number, you set up your workbook in three tabs.

• raw: the export, exactly as it arrived. You never type in it, sort it or delete from it. If you break something later, you start again from here.
• work: helper columns with formulas that read from raw and produce clean values next to the original ones, so you can compare them row by row.
• clean: the final table, pasted as values from work, plus a log tab that lists every change.

Why so careful? Because a spreadsheet does not remember what you did. If you overwrite a column and save, the old values are gone, and nobody, including you next week, can tell what the file said before. A formula next to the original keeps both.

The clean table should be tidy. Hadley Wickham's 2014 paper Tidy Data gives three rules: each variable is a column, each observation is a row, and each type of thing you observe is its own table. For our question, one observation is one store's sales of one category on one day. That is the table's grain, the word you met in da-01. The variables are store, date, category, sales and transactions.

Many files are built for reading on screen. A store manager's weekly report has a column per week, total rows and merged headers. A pivot table or a formula cannot use it: the week, a variable, is hidden in the column headers, and the total rows are a different kind of thing mixed in with the observations.

Turning columns into rows is called unpivoting. In Excel, Power Query has an Unpivot Columns command; in any tool you can also rebuild the table with formulas, and later in this course you will do it in a few lines of Python (The same analysis in Python). Our East export is already long, one row per store, date and category, but it has a grand total row at the bottom. A total row breaks tidiness the same way: sum the sales column and you count every sale twice.

Types: numbers stored as text, and dates read the wrong way

Every cell has a data type. A number can be added, a date can be grouped by week, and text can only be compared and searched. The most common cleaning problem is a value that looks like a number or a date but is stored as text. Formulas then skip it without any warning.

Numbers stored as text. SUM and AVERAGE add only real numbers. If a range holds "USD 1204.50" as text, SUM leaves that cell out and still shows a confident total. Four quick ways to spot the problem:

• Alignment: with default formatting, numbers sit on the right of the cell and text on the left.
• Excel marks many numbers stored as text with a small green triangle in the corner of the cell.
• =ISNUMBER(E2) returns TRUE for a real number and FALSE for text.
• Compare =COUNT(E2:E24666), which counts numbers only, with =COUNTA(E2:E24666), which counts every non-empty cell. The difference is the number of text cells in the column.

The fix is a helper column that removes the extra characters and converts the rest: =VALUE(SUBSTITUTE(TRIM(E2),"USD ","")). TRIM removes spaces at the start and end and turns runs of spaces inside the text into one. SUBSTITUTE swaps one piece of text for another, here "USD " for nothing. VALUE turns the text that is left into a number.

Watch the thousands separator and the decimal mark. One thousand two hundred and four dollars fifty is written 1,204.50 in some countries and 1.204,50 in others. Spreadsheets read numbers using the locale of your computer or file, so the same CSV can give numbers on one laptop and text on another. Before you convert, look at a few raw values and decide which convention the export uses.

Dates. A spreadsheet stores a real date as a number of days, which is why you can subtract two dates or group them by week. In Google Sheets, and in Excel with its default date system, 18 October 2026 is stored as 46313. Text that only looks like a date gets none of this.

The trap is regional date order. Mangrove's sales system writes dates day first: 03/10/2026 means 3 October. When you open a CSV, the spreadsheet reads each date using your locale. On a laptop set to month first, 03/10/2026 becomes 10 March 2026: a real date, in the wrong month, with nothing to warn you. And 18/10/2026 cannot be read at all (there is no month 18), so it stays as text.

Don't repair flipped dates cell by cell: you cannot tell a flipped 10 March from a real one. Go back to the raw file and import it again so that the date column arrives as text. In Google Sheets, File > Import has a "Convert text to numbers, dates, and formulas" box that you can untick. In Excel, Data > From Text/CSV opens a preview; choose Transform Data and change the date column's type to Text before you load it. Then build the date yourself from its parts:

=DATE(VALUE(RIGHT(C2,4)), VALUE(MID(C2,4,2)), VALUE(LEFT(C2,2)))
   year = last 4 characters, month = characters 4-5, day = first 2

The lasting fix is upstream. Ask for exports with ISO 8601 dates, written 2026-10-18. Year, month, day can only be read one way.

Spaces, labels, duplicates and blanks

Invisible characters. "Bakery" and "Bakery " (with a space at the end) look the same on screen, but to a formula or a pivot table they are two different labels. TRIM removes the extra spaces. CLEAN removes non-printing characters such as line breaks that sometimes come along from other systems. Excel's TRIM does not remove the non-breaking space that web pages use; =SUBSTITUTE(A2,CHAR(160)," ") turns it into a normal space first.

Inconsistent labels. The same category can arrive as "Fresh produce", "fresh produce", "Fresh Produce " and "Produce". LOWER, UPPER and PROPER fix the case, but they cannot know that "Produce" and "Fruit & veg" mean the same thing. For that, build a small mapping table: one column with every raw label you found (lower case, trimmed), one with the standard name. Then look each row up in it. A row whose label is missing from the map shows "UNMAPPED", so new spellings cannot slip through.

This matters for the analysis. If fresh produce sales are split across four labels, a pivot table shows four small rows instead of one big one, and the drop you are looking for is spread thin.

Lookups from master tables. The export has store IDs but no city and no store type. Those live in a master table: Mangrove's site table, with one row per store or DC. XLOOKUP finds a value in one column and returns the matching value from another:

=XLOOKUP(A2, stores!$A$2:$A$400, stores!$C$2:$C$400, "NOT FOUND")
   look for the store ID in A2 in the master's ID column,
   return the city from the same row, or NOT FOUND if there is no match

Older versions of Excel: =VLOOKUP(A2, stores!$A$2:$C$400, 3, FALSE)

The dollar signs fix the ranges so they don't move when you copy the formula down 24,665 rows. The fourth argument is what XLOOKUP returns when nothing matches; without it you get #N/A. Count the misses with =COUNTIF(E2:E24666,"NOT FOUND"). A lookup also has a type trap: the number 207 and the text "207" do not match. If one side has IDs stored as text, every row for those stores comes back NOT FOUND.

Duplicates. A duplicate is two rows for the same observation: here, the same store, date and category. Exports get them when a store's data is uploaded twice. Find them before you delete anything. A helper column counts how many rows share the row's key:

=COUNTIFS($A$2:$A$24666, A2, $B$2:$B$24666, B2, $C$2:$C$24666, C2)
   how many rows have this store, this date and this category?
   1 = fine, 2 or more = the key appears more than once

Filter that column to values above 1 and look at the rows. If the copies are identical, they are true duplicates. If the key matches but the sales differ, it's a conflict: one of the two numbers is wrong, and you need to ask which before you keep either. Only then use Remove Duplicates (Data > Remove Duplicates in Excel, Data > Data cleanup > Remove duplicates in Google Sheets), choosing the key columns. Both keep the first row of each group, so the conflicts must be settled first.

Blanks and zeros. A blank and a zero mean different things. Zero says "we know the store sold nothing": it was closed. A blank says "we don't know": the upload failed. AVERAGE skips blank cells but counts zeros, so filling blanks with 0 drags averages down and makes a missing day look like a terrible one. Keep blanks blank, add a note column that says why, and count them with =COUNTBLANK(D2:D24666).

The cleaning log and the checks that prove it

A cleaning log is a list of every change: what was wrong, how many rows it touched, what you did, and the row count after. It takes ten minutes. When the head of stores asks "are you sure?", the log is your answer, and when the same export comes next month, it is your recipe.

Three checks turn "I cleaned it" into "it is right":

1. Row counts before and after every step. Write down the count after each change and explain every difference. If you removed 24 duplicates, the count must fall by exactly 24.
2. The expected shape. You know how many rows a complete table should have: stores × days × categories. Every date should have the same number of rows, and so should every store. =COUNTIFS per date or per store shows any gap.
3. Reconcile to another source. To reconcile is to compare your total with a number from somewhere else, such as finance's weekly sales report, and explain the gap. A small, explained gap is fine. An unexplained one means a step is still wrong.

Fixing types and labels does not change the row count, so check those steps another way: after the fix, the count of text cells, UNMAPPED labels and NOT FOUND lookups should all be zero, and each category should have the same number of rows.