Data Cleaning Checklist: 12 Fixes Before You Analyse Anything

Data cleaning is the work of fixing errors, inconsistencies and gaps in a dataset before you analyse it. Skip it and every total, average and chart built on top inherits the problems. This checklist covers the 12 fixes that catch most issues in business data such as sales exports, CRM lists and stock sheets. Work through them in order; each takes minutes, not hours.

Structure fixes

1. One header row, one record per row

Remove report titles above the header, merged cells, subtotal rows and grand-total lines at the bottom. A ‘Total’ row left in a sales export will double your revenue when you sum the column.

2. Clear, unique column names

Rename ‘Column1’, ‘Amt’ and two columns both called ‘Date’ to something unambiguous: invoice_date, due_date, amount_excl_vat. Good names make every later step, including AI analysis, more accurate.

3. Remove exact duplicates

Duplicates creep in when exports are appended twice or a system retries a sync. In Excel use Data > Remove Duplicates; in SQL use SELECT DISTINCT or group by the key. Check first whether rows that look identical really are: two identical R350 orders from the same customer on the same day might be genuine.

Type fixes

4. Make dates real dates

Dates stored as text will not sort or group correctly, and mixed formats are worse. Is 03/04/2026 the 3rd of April (South African convention) or the 4th of March (US)? Convert everything to one unambiguous format, ideally ISO (2026-04-03), and check the earliest and latest dates make sense.

5. Make numbers real numbers

Strip currency symbols (R), thousands separators and trailing spaces so amounts become numeric. Watch out for decimal commas: ‘1 250,50’ and ‘1,250.50’ are the same amount written two ways. Negative values in brackets, like (450), also need converting.

6. Keep codes as text

Account numbers, product codes, postal codes and ID numbers are labels, not quantities. Store them as text so leading zeros survive (a 0157 postal code should not become 157) and long numbers do not turn into scientific notation.

Consistency fixes

7. Standardise categories

‘Gauteng’, ‘GP’, ‘gauteng ’ and ‘Gauteng Province’ are four regions to a computer. Trim spaces, fix capitalisation and map variants to one value. A quick way to find them is to list the unique values in each category column and read them.

8. Standardise units and currency

Mixing kilograms and grams, or rand and US dollar amounts, in one column produces nonsense totals. Add a unit or currency column if needed, and convert to one basis before summing. State whether amounts include VAT.

9. Handle missing values deliberately

Decide per column what a blank means. A blank discount probably means zero. A blank region means unknown, and should stay distinct from any real region. A blank amount might mean the row should be excluded. Never let blanks silently become zeros in averages.

Sense checks

10. Check ranges and impossible values

Look at the minimum and maximum of every numeric column. Negative quantities (returns?), a unit price of R0.01, an order dated 2062, a customer aged 140. Each one is either an error to fix or a special case (such as credit notes) to separate out.

11. Investigate outliers, don't just delete them

An order for R480,000 when the typical order is R3,000 may be a typo or your best customer of the year. Check it at source. Our guide to spotting anomalies in business data explains simple methods to find them.

12. Reconcile to a trusted total

Finally, compare a headline figure with a source you trust: total March sales against the accounting system, or active customers against the CRM dashboard. If they differ by more than you can explain (timing, VAT, credit notes), something upstream is still wrong.

The checklist at a glance

Clean at the source when you can

If you fix the same problems every month, fix the process instead: add a dropdown for region in the capture form, make the date field mandatory, or change the export settings. Cleaning is necessary, but prevention is cheaper. For the issues that most often slip through to finished reports, see 8 data quality issues that quietly break your reports.

When you do use an automated tool, it should tell you what it found. Summarix includes data-quality notes in each report so you can see blanks, duplicates and suspicious values alongside the results; see features.