Clean Your Data Before the Report: A Practical Checklist

A report is only as good as the table behind it. Most wrong numbers in monthly reports are not calculation errors. They are dirty data: duplicates, empty fields, mixed date formats, and totals that quietly include test entries.

Cleaning is not glamorous, but thirty minutes of checks before you build charts saves hours of arguing about numbers later.

Before You Touch Anything

• Save a copy of the raw file and never clean the original.
• Write down where the data came from, who exported it, and the date range.
• Agree on the meaning of each column with the person who will read the report.

Ten Checks That Catch Most Problems

• Row count. Compare the rows in the export with the count in the source system. If they differ, find out why before doing anything else.
• Duplicates. Check order number, phone, and email, not just the name. Watch for trailing spaces and for the same number saved as 965 and 00965.
• Blank fields. Count empties in the columns you will report on. Blanks in a date column often mean a failed sync, not a careless customer.
• Date formats. Force one format. A column that jumps between 01/02 and 2/1 hides a day and month swap that changes every monthly total.
• Numbers stored as text. Amounts with commas or currency symbols will not sum. Convert them, then recheck the total against the source.
• Arabic text and whitespace. Trim extra spaces, unify letter forms such as alef, and merge the same city or company written in different ways.
• Currency and tax. Confirm every amount is in one currency and that you know whether tax is included.
• Negative and zero values. Refunds, cancellations, and 0.00 rows need a decision: include them, separate them, or exclude them.
• Test and internal records. Remove staff accounts, demo orders, and test phone numbers. Filter on known test emails or a flag column.
• Outliers. A ten-minute call logged as 900 minutes is an entry error, not a record. Check the extremes before they drag an average.

Document What You Changed

Keep a short log with every report: which rows you removed, which columns you fixed, and which rule you applied. When someone asks next month why the number moved, the log answers in one line instead of an hour of guessing.

Make It Repeatable

If you clean the same export every month, move the steps into a script or a query. A saved routine runs the same checks in the same order every time, and a new team member gets the same result as the person who built it.

At Q8DM, this is where most reporting work actually sits, in the cleaning and mapping layer before any chart is drawn.

If your monthly report still starts with an hour of manual fixing, it is worth automating. Send your next export through the checklist once, write down each step you took, and you already have the specification for the script. Want a report that arrives clean and on time? Start at q8dm.com.