Spreadsheet Data Cleaner

What it does

Profiles a messy file, cleans it using clear rules, flags anything it cannot safely fix, and gives you a short report on data quality and what the data shows. It works on a copy and never deletes rows that contain real data.

When to use it

• Before importing contacts into a CRM or email platform.
• When an export from Xero, MYOB, QuickBooks, Shopify or a booking system is hard to read.
• When several people have typed into the same sheet in different ways.

What you need

• Required: the file (CSV or Excel), uploaded or pasted.
• Helpful: what the file is for, which columns matter most, and the system it will be imported into.
• Optional connectors, if you already use them: Google Drive or Google Sheets, Microsoft 365 (Excel on OneDrive or SharePoint), and exports from Xero, MYOB, QuickBooks or HubSpot. If Claude's file analysis or code tool is switched on, Claude can return a cleaned file. If not, it can clean small tables directly in the chat. No paid add-on is required.

Steps for Claude

1. Protect the original. Work on a copy and say so. Keep the original file unchanged.
2. Profile: rows, columns, column names and types, missing values per column (count and percentage), exact duplicate rows, and formatting problems.
3. Clean, using Australian formats:
   • Column names: lower case, underscores, no special characters.
   • Dates: Australian data is usually day first. Convert to YYYY-MM-DD, and flag any date that could be read two ways (such as 03/04/2026) rather than guessing.
   • Money: strip $ and commas, store as numbers in AUD, and keep GST-inclusive and GST-exclusive amounts in separate columns if both exist.
   • Phone numbers: one consistent format. Keep postcodes and ABNs as text so leading zeros are not lost. An ABN has 11 digits; flag any that do not.
   • Text: consistent capitalisation for names and categories; trim spaces.
4. Remove exact duplicate rows only. For near duplicates (same email, different spelling of a name), flag them in a new column and let the user decide.
5. Flag, do not delete. Add columns such as flag_missing_email for rows with gaps in important fields.
6. Report using the format below. If a chart would help and a code tool is available, add one simple chart of the main measure over time.

Output format

• Data overview: rows before and after removing duplicates, columns and what each holds, date range, any gaps.
• What the data shows: three to five plain sentences.
• Issues found: issue, rows affected (number and percentage), recommended fix.
• Top findings: three to five, each marked as expected, neutral or concerning.
• Cleaned file: as a download if available, or the cleaned table.

Australian rules and sources

• Business records. The ATO requires most records to be kept for 5 years, unchanged and protected from alteration. Clean a copy, not your source records: https://www.ato.gov.au/businesses-and-organisations/preparing-lodging-and-paying/record-keeping-for-business/overview-of-record-keeping-rules-for-business
• Personal information. Customer lists hold personal information. Check whether the Privacy Act covers your business: https://www.oaic.gov.au/privacy/privacy-guidance-for-organisations-and-government-agencies/organisations/small-business . Remove columns you do not need before uploading, in line with OAIC guidance on AI tools: https://www.oaic.gov.au/privacy/privacy-guidance-for-organisations-and-government-agencies/guidance-on-privacy-and-the-use-of-commercially-available-ai-products
• Old data. Businesses covered by the Privacy Act must take reasonable steps to destroy or de-identify personal information once it is no longer needed: https://www.oaic.gov.au/privacy/privacy-guidance-for-organisations-and-government-agencies/handling-personal-information/guide-to-securing-personal-information
• ABN format and lookup: https://abr.business.gov.au/Help/AbnFormat

Limits

Cleaning rules can be wrong for unusual data, so check the flagged rows and a sample of cleaned rows before importing. Duplicates that differ slightly are flagged, not merged.

Example requests

1. "Clean this customer list before I import it into Mailchimp. Flag anyone missing an email."
2. "Here's a stock sheet three staff have been editing. Tidy it up and tell me what's wrong with it."
3. "Clean this MYOB sales export and tell me which months look incomplete."