What a spreadsheet data check can and can't tell you

A data quality check looks at a spreadsheet or an export and flags rows that break rules you set. That is useful, and it is easy to expect too much from it. This guide explains the four common kinds of check, how to do simple versions yourself, and what it means when a report comes back with nothing to flag. It is about checking structure, not about proving that numbers are right.

The four kinds of check

We group checks on a business spreadsheet into four kinds.

Blank

A column that should always be filled in is empty, such as a customer number on an order.

Repeated

A value that should be unique appears more than once, such as an order number.

Too old

A date is older than a limit you set, such as a stock count that has not been updated in a day.

Out of range

A number is outside limits you choose, such as a negative amount or one far larger than normal.

Notice what these have in common: each is a rule you can state in one sentence, and each can be decided by looking at one column, or one value, at a time.

You can do simple versions yourself

Excel has built-in tools for two of these. Microsoft's help pages explain how to find duplicate values, how to highlight them with conditional formatting, and how to remove them. They also show how to apply data validation, which stops a wrong entry being typed in the first place by limiting a cell to a list, a number range or a date range. For example, you can allow only dates within a range, or only numbers between two limits.

Two habits make these tools safer. Highlight duplicates and look at them before you remove anything, and work on a copy of the file, not the original. Also decide in advance whether "ORD-1" and "ord-1", or a value with an extra space, should count as the same value, and test how your tool treats them on a few made-up rows.

What a check cannot tell you

A check can only test the rules you gave it. That has consequences.

• It cannot tell you a number is right. An invoice for 1,200 that should have been 1,020 passes any range check that allows both.
• It cannot tell you the file is complete. A check sees the rows that are there, not the rows that should be there.
• It cannot compare two systems. Whether your accounting export matches your bank, or your orders match your inventory, is a different job.
• It cannot spot values that look normal but are wrong. A plausible wrong address or a believable wrong price is invisible to a rule about blanks and ranges.
• It does not tell you why. It points at rows. A person still has to decide what happened.

What "no findings" means

A report with no findings means none of the rules you set fired on that file at that time. It does not mean the file is correct. If you write "no problems found" in an email to your accountant, write it as "none of these four rules flagged anything," and say which rules.

This is not an audit

A data quality check is not an accounting audit or an assurance engagement. Do not present its report to a bank, a lender, an auditor or a tax authority as if it were one. If you need that, you need a qualified professional.

How to read a report

1. Start with which rules were run, and which columns they covered. A rule that was not run cannot have found anything.
2. Look at the rows listed and the reason for each. Pick two or three and open them in the original.
3. For each finding, decide whether it is a real error, a data-entry habit, or a rule that was too strict.
4. Fix problems in your source system if you can, not only in the export, or the same rows will come back next month.

Choose rules you can defend

The best rules are ones you can explain to a colleague. "Customer number must not be blank." "Order number must be unique." "Stock count date must be within 24 hours of the export." Start with a few that would hurt if they were wrong, and add more once you trust the first ones.

Where Bash Tail fits

We offer a data quality check built around exactly these four kinds of rule. It is in early access, our contract and data-processing terms are not final, and the product page lists what it does and does not do. This guide does not depend on it: you can run simple versions of all four yourself.