Prepare Data for Analysis With AI: A Simple Health Check

Analysts often say most of their time goes into preparing data, not analysing it. That is because messy data gives wrong answers that look right. A quick, systematic health check, with AI suggesting what to look for, saves you from presenting numbers you later have to take back.

Step 1: Describe the data to the AI

Paste the column names and five sample rows with names replaced:

Here is the structure of my dataset (sample rows anonymised):
[paste headers and 5 rows]
It has about [N] rows covering [period]. I want to answer: [question].
List the data quality checks I should do before analysing, in order,
and how to do each one in Excel.

Step 2: The core health check

Check	Why it matters	Quick way in Excel
Row count and period	Missing months change every total	Count rows; check min and max date
Missing values	Blank amounts or dates skew results	Filter each important column for blanks
Duplicates	Double-counted sales or invoices	`COUNTIF` on the ID column, or Remove Duplicates after review
Wrong data types	Text dates or text numbers are ignored by formulas	Check alignment; use `ISNUMBER`
Inconsistent labels	"Mumbai", "Bombay", "mumbai " split one group into three	Filter the column and look at the list of values
Outliers	One wrong entry of ₹10,00,000 instead of ₹10,000 distorts averages	Sort largest to smallest; look at the top and bottom
Returns and cancellations	Negative or cancelled rows may need separate treatment	Filter for negative values or status columns

AI can give you the exact formula for any of these. For example:

Give me an Excel formula to flag rows in column A (Invoice No) that
appear more than once, and explain how to review them before deleting.

Step 3: Decide, do not just delete

For each problem, decide what to do and why:

Missing amount: find the real value, exclude the row, or keep it and note it?
Duplicate invoice: true duplicate (remove) or two lines of the same invoice (keep)?
Outlier: data entry error (correct it) or a genuinely large order (keep and mention it)?

Ask the AI for the options, but make the decision with your knowledge of the business:

I found 3 invoices over ₹5 lakh in data where most are under ₹50,000.
How should I decide whether they are errors or real large orders, and
how should I treat them in the analysis?

Step 4: Clean in a copy, keep a log

Never clean the original export. Work on a copy, or add cleaned columns next to the original ones.
Keep a short cleaning log, a list in a separate sheet: "Removed 12 duplicate rows (same invoice and line). Corrected 2 amounts (₹10,00,000 → ₹10,000, confirmed with accounts). Excluded 45 cancelled orders."

The log lets you explain your numbers later, and lets someone else repeat the analysis.

Step 5: Check totals after cleaning

Compare before and after:

Row count (only changed where you removed rows on purpose)
Total amount (only changed by the amounts you corrected or excluded)

If anything else changed, find out why before moving on.

A word on privacy

For this kind of work, the AI only needs the structure of your data. Do not paste full customer lists, phone numbers or account details into public tools. If your company provides an AI tool approved for confidential data, follow its rules.

Practice task

Take your dataset and run the core health check. Record at least three findings and your decision for each in a cleaning log, then confirm row counts and totals before and after.

Key takeaways

Describe your data's structure to AI and ask for an ordered list of checks.
Check period, blanks, duplicates, types, labels, outliers and cancellations.
Decide what to do with each problem using business knowledge, then log it.
Clean in a copy and confirm counts and totals afterwards.