cleaning data

Quality Check and quick fixes

A panel that reads the whole file and lists what is wrong with it (rows cut, repeated or too short, empty columns, zeros lost in a spreadsheet, text with a broken encoding, columns that write numbers, yes/no values or countries in several ways), with a key to go through them and a fix for most.

Table › Quality Check opens the Quality Check: a panel at the right of the file that reads all of it and lists what is wrong, the way a code editor lists the errors of a program. Each problem is also underlined in the text with a wavy line; rest the pointer on it to see what it is. The status bar shows how many there are (7 problems).

A small file of customers with the Quality Check open: wavy underlines under a name that ends in a space, “NA” and “n/a” in the amount column, “Córdoba”, “madrid” among “Madrid”, a row with a value missing, “abc” among amounts, a repeated row and an empty row

The panel reads the file again a moment after every change, so the list is always the file as it is. It takes the place of the Column profile: opening one closes the other.

What it looks for

Only what is surely wrong, or surely written in two ways: it doesn’t guess what your data means. Under each kind the panel lists its first 50 places, with the line, the column and the value. A click on one goes to it: it selects it and brings all of it into view.

The kinds are always in the same order, the order to fix them in: first what makes the file read wrong, then what a click cleans and changes what comes after (a space at the end of Madrid makes it another value than Madrid, so trimming comes before grouping), then what needs you to choose, and last what can only be looked at. Start at the top.

What makes the file read wrong

Problem What it means Its fix
Quotes that never close see Unclosed quotes
Lines above the table a title, notes or blank lines before the header row, as exports from banks and public bodies have (see Lines above the table). While they are there, the check lists nothing else: every row would be compared with a line that isn’t the header row Remove them, and the delimiter and the header row are detected again
Rows cut by a line break a value had a line break and no quotes around it, so its row is on two lines or more Join them: the row is one again, the value in quotes with its line break inside
Rows with fewer values than the header a row ends early: its last delimiters are missing Add the missing delimiters
Rows with more values than the header a row has values with no column: often a delimiter inside a value that should be in quotes
The header row, repeated the header row again further down, as when two exports are pasted one after the other Remove them
Empty rows a row with nothing but delimiters and spaces Remove them
Rows that repeat an earlier one the same text as a row before it Remove them: the first one stays
Empty columns no value in any row, like the one a delimiter at the end of every line leaves Delete them
Columns without a name, Column names that repeat another in the header row; capitals and spaces around don’t make two names different Name them / Make them unique (see Column names)

Empty lines aren’t rows. Without a header row, the first row says how many values the others should have.

Text to clean

A click each, and the rest of the list is more exact afterwards.

Problem What it means Its fix
Text with a broken encoding España for España, don’t for don’t: text that was read with the wrong encoding before it got here Repair them
HTML entities Tom & Jerry Decode them (see Characters)
Invisible or unusual characters zero-width spaces, non-breaking spaces, control characters, � Clean them: the invisible ones go, and the spaces become ordinary ones
Letters written with a separate accent an é written as an e followed by an accent, as text copied from some PDFs or file names from a Mac can be. It looks the same as é, but doesn’t match it in a search, a sort, a duplicate check or Similar values Normalize them: each accent joins its letter, as Normalize Unicode does. In the column names too
Spaces at the start or end of a value the ones the editor marks with dots Trim them
Repeated spaces inside a value two spaces or more between words Make them single

Columns written in two ways

The first six are a click; the last two open the window of their operation, because there is something to choose.

Problem What it means Its fix
Missing values written in several ways NA, n/a and - in the same column Empty them (see Empty Missing Values)
Numbers with another decimal separator than their column 1,5 among 1.5 and 2.25 Write them like the rest: with the separator most of the column has; currency and units stay
Numbers formatted unlike the rest of their column $1,200.00 among 900 and 1200, or 2,000 among 1500 and 3400: a currency sign or thousands separators that other numbers of the column don’t have. A program that reads the column as numbers stops at those Write them like the rest: the currency sign goes if the other numbers have none, and the thousands separators go; the digits stay as written ($1,200.00 → 1200.00)
Codes that lost their leading zeros 8001 in a column of five-digit codes like 08001: what a spreadsheet does to them Add the zeros
Yes/no values written in several ways yes, true, 1 and Y in the same column, where nearly every value is a yes or a no Write them one way: like most of the column (true → yes, FALSE → no), with its capitals
The same country written in different ways USA among United States and Spain, DE among Germany and France, Holland among Netherlands, in a column of countries Write them one way: by name or by code, whichever most of the column uses; each country as the column already writes it, or else by its name in English or its ISO code
Dates written another way than their column 3/1/2026 or 3 Jan 2026 among 2026-01-03 and 2026-02-11 Reformat Dates… opens Reformat Dates on those columns
The same value with other capitals madrid in a column that says Madrid many times Group Similar Values… opens Similar values on that column. The panel lists each column with the problem on its own, with its own button

Values to look at

Nothing can fix these for you: the panel shows where they are, and Select them selects them.

Problem What it means
Values of another type than their column text where nearly every other value is a number, a date, a time or an email address. Empty cells and missing values (NA, null…) are never of the wrong type
Numbers in another currency than their column €750.5 in a column of amounts in dollars: they can’t be added up or compared until they are in one currency, and only you know the rate. Until then, the column’s numbers aren’t checked for their format
Dates that aren’t in the calendar 31/02/2026, which is no day whichever comes first, the day or the month; or a year like 0026
Email addresses with a part missing ana@, ana@example, ana.@example.org, in a column of addresses
Numbers rounded by a spreadsheet 1.23457E+11 where there was a number of twelve digits, or sixteen digits and more that now end in zeros: the digits are lost, and only the file they came from has them
Spreadsheet errors #REF!, #DIV/0!, #VALUE!, or ######## from a column too narrow
Repeated values in a column of unique ones the same value twice in a column that looks like a key: it is called id, code, sku, email… or holds email addresses, and nearly all its values are different

How sure it is

Each kind is found only where nothing else explains what is there:

  • A row cut by a line break needs its second piece to be something that can’t be a row: second line where every other row has a number. Two short rows that could each be a row are short rows, since some programs leave out the delimiters at the end of a row.
  • Rows that repeat aren’t a problem in a file of one column, or where rows repeat all the time, as in a log.
  • Lost zeros are looked for in columns of codes of one length: nothing but digits, nearly all as long as the longest, and some starting with a zero. When a spreadsheet has taken every zero, only the column’s name can tell (zip, postal code, postcode).
  • Other capitals don’t count in values with digits, where aB1 and Ab1 may be two codes, nor in columns with more than 20,000 different values.
  • A broken encoding is repaired only when the characters can’t be anything else. MANHÔ may be MANHÔ badly read, or the word MANHà and a closing quote: it is left as it is, unless the same value has another sequence that leaves no doubt. Inside a broken value, a space at its edge or an invisible character is what is left of a letter, so it isn’t reported or removed until the encoding is repaired.
  • HTML entities aren’t reported in values that are HTML, with its tags: there they belong.
  • Currencies are different only when they can’t be the same one: $ may be the dollar of any country or the peso, so $ and USD, or $ and CA$, aren’t reported; $ and € are. Units and % aren’t currencies: 12 kg and 12 are left alone.
  • Thousands separators are reported only next to a number of four digits or more written without them: 1,200 among 900 and 45 is how anyone writes it.
  • Yes/no values count in a column where at most one value in ten is something else (active among yes and true is left as it is), and written in two ways or more: yes, no and Yes is one way, with other capitals.
  • Countries come from a list of every country with its ISO codes and the other names it goes by (USA, Great Britain, Ivory Coast, Czech Republic…), and count in a column where at most one value in ten is something else. Codes count only in capitals: de and in are words. A column of US states, CA or DE among them, has no countries written as names, so nothing in it is reported.

If the whole file looks like España, it was opened with the wrong encoding rather than saved with it: see Encodings and line breaks.

Going through them

F8 goes to the next problem after the cursor, and Shift+F8⇧F8 to the one before, around the file. With the panel closed, F8 opens it first. A click on the count in the status bar does the same.

Fixing them

The Quality Check panel: “12 problems of 10 kinds”, then each kind with its count and its fix as a blue button: Rows with fewer values than the header (Add the missing delimiters), Empty rows and Rows that repeat an earlier one (Remove them), Text with a broken encoding (Repair them), Spaces at the start or end of a value (Trim them), Repeated spaces inside a value (Make them single), each with the line and value where it is

The button under each kind’s name fixes every problem of that kind in the file, in one step of Ctrl+Z⌘Z, and says how many cells or rows it changed. Two of them, Reformat Dates… and Group Similar Values…, open the window of that operation instead, because there is something for you to choose. The kinds that have no safe fix can be selected (Select them), to look at them, type over them all, or run an operation of the Cells menu on them.

The list is in the order to fix things in, so going down it works: each fix may clear problems further down, and the panel reads the file again after every one.

Ctrl+.⌘. (Edit › Quick Fix…) opens the same fixes as a list, wherever you are. When the cursor is on a problem, the list starts with the fix for that value or that row alone: Trim this value, Add the missing delimiters to this row, Join this row again, Repair this value.

Some problems are for you to judge. A row with too many values needs its quotes put back by hand, and a repeated id may be a mistake or two rows about the same thing: the panel only shows where they are.

While the panel is reading the file again after a change, its list is dimmed and its buttons wait: it is a moment in most files.

Large files

The panel reads the file in the background, a part at a time, and says how far it has got; what depends on a whole column (decimal separators, dates, lost zeros, capitals) takes a second read of those columns. It keeps and underlines the first 300 places of each kind, and counts all of them: fix those, and the next ones come up.