Prepare a messy spreadsheet for analysis
Clean a small sales dataset while preserving the original records and documenting each decision. Use AI to propose transformations, then verify duplicates, dates, missing amounts, row counts, and totals before treating the result as analysis-ready.
Before you start
No coding required
A cleaned sales table, exception list, and validation log.
- Basic spreadsheet editing and sorting
- Spreadsheet application — access: varies
- Text-capable AI chat — access: varies
A total is only useful when you know what its rows represent. Before asking AI to analyze data, resolve its format and record-level problems. This exercise produces a cleaned table and an exception log rather than silently making incomplete records look complete.
Use a spreadsheet and text-capable AI chat. No code or file upload is required. Paste the tables into a blank sheet or work directly with the inline records. Time has not been measured.
1. Preserve the raw data
Original synthetic export, September 2026. Each order ID represents one order. Amounts are USD. The export owner confirms slash dates use day/month/year and B02’s second occurrence is an accidental duplicate, not a separate order.
| Raw row | Order ID | Date text | Region text | Amount text |
|---|---|---|---|---|
| 1 | B01 | 01/09/2026 | north |
1,200.00 |
| 2 | B02 | 2026-09-02 | NORTH | 800 |
| 3 | B02 | 2026-09-02 | NORTH | 800 |
| 4 | B03 | 03/09/2026 | South | (blank) |
| 5 | B04 | 2026-09-04 | south | 500 |
| 6 | B05 | 05/09/2026 | North | 0 |
Keep these raw rows unchanged in one sheet. Use a second sheet for cleaned records. Backticks show spaces/case in this article; they are not part of the source values.
2. Define rules before transforming
Trim surrounding spaces and map region values to North or South. Normalize dates to ISO text YYYY-MM-DD using the confirmed day-first convention. Convert nonblank amount text to numbers. Remove only the confirmed duplicate row 3, retaining a log. Leave B03’s amount missing; preserve B05’s valid zero.
A repeated ID alone would not justify deletion without the export owner’s confirmation. In your own data, clarify whether rows represent orders, order lines, or events first.
3. Ask AI for a proposed result
Prepare this synthetic order export for analysis.
[Paste the raw table and the confirmed rules above.]
Return a cleaned table with raw-row lineage, order ID, ISO date, region,
numeric amount or MISSING, and an exception log explaining every change.
Do not impute the missing amount or convert it to zero.
Preserve B05's zero. Remove only confirmed duplicate raw row 3.
State row counts and the sum of known amounts, clearly excluding missing values.
Do not call that sum a complete revenue total.
4. Compare with the expected table
Checked editorial transformation:
| Raw row | Order ID | ISO date | Region | Amount (USD) |
|---|---|---|---|---|
| 1 | B01 | 2026-09-01 | North | 1,200 |
| 2 | B02 | 2026-09-02 | North | 800 |
| 4 | B03 | 2026-09-03 | South | MISSING |
| 5 | B04 | 2026-09-04 | South | 500 |
| 6 | B05 | 2026-09-05 | North | 0 |
In the spreadsheet, leave the missing amount cell blank and put “Missing amount” in a separate exception column; do not mix the article’s MISSING display marker into a numeric calculation column.
The log records region/date/number-format normalization, row 3 deletion as a confirmed duplicate, and unresolved B03 amount. Six raw rows become five unique orders: four known amounts and one missing amount. Known amounts sum to USD 2,500. North’s known sum is USD 2,000; South’s is USD 500 with one unresolved amount. Neither the overall nor South sum is complete revenue.
5. Validate before analysis
Count raw and retained rows, check unique IDs, and inspect the missing-versus-zero cases. Confirm every clean record retains a source row. Verify dates against the stated convention. Sum known amounts independently; raw known amounts total 3,300 because the duplicated 800 is still included.
If AI produces 3,300 after cleaning, inspect the duplicate. If it reports no missing amounts, check whether B03 was filled with zero. If it swaps March and September, restate the date convention and regenerate the proposal.
For another exercise, supply B03’s confirmed amount as 200. The complete total becomes 2,700 and South becomes 700. Document who supplied the new value in a real workflow. Save the raw export, rules, cleaned result, and log together before using the data in a report.
Source: original fictional export and explicit owner-confirmation fixture. No real export owner was contacted. Output is an editorial example, not evidence of a named model run.