Cleaning Excel data with ChatGPT starts with a copy of the file and a set of rules. Before removing duplicates or standardizing dates, you need to know which fields may change, what counts as the same record, and how to check that no information has been lost. The goal of this guide is a file that is easier to work with, alongside the original, a change log, and a list of exceptions you can review.
For current plans, prices and every Maitz article on ChatGPT, see our ChatGPT hub.
We will practice with a fictional office-supply inventory: 12 rows with leading zeros in identifiers, extra spaces, unclear dates, and duplicates. Some problems can be corrected automatically. Others will remain flagged for investigation. A clean file can still contain important unanswered questions.
Checked on October 3, 2026; the English practice files were rebuilt from the same data and rechecked locally on October 8, 2026. The sample files, formulas, and reconciliations were tested locally. No test was performed in the ChatGPT interface, and this guide does not promise that the tool will produce the same result in every account or attempt.
Practice files: source, result, and validation log
- Excel file before cleaning: 12 fictional records with deliberate problems.
- The same source in CSV format: for practicing careful import of identifiers and text.
- Clean Excel reference file: four sheets named Clean, Source, Change Log, and Validation.
- The reference result in CSV and the change log.
- Local validation results: what was checked and what remains unresolved.
Start with the source file and try the requests in this guide before opening the solution. The clean file does not present every record as valid: it also preserves the problematic records and explains why they need review. The prompts below were written for these files; they were not run in ChatGPT.
Step 1: Back up the file and define each column
Save the original under a separate name, such as inventory-original.xlsx, and work on a copy. If your real file contains formulas, hidden sheets, connections, pivot tables, or macros, document them before uploading. This exercise uses a simple table; it does not establish that rebuilding a complex workbook will preserve all its features.
Define clearly what one row represents. Here, each row is a fictional inventory-movement record. The Movement ID and SKU fields are text, even when they look like numbers. Quantity is a number of units. Prices are in US dollars, using a decimal point and a thousands comma under a rule defined in advance. A missing date does not mean today, and a missing quantity does not mean zero.
The Source Row column, with values such as R001, lets you compare versions even after sorting. It is a reference for validation, not an alternative business identifier. When two records share a Movement ID, you still need to check whether they are duplicates or different entries.
Step 2: Check permissions and privacy before uploading
Use only the supplied sample for this exercise. Before uploading a real file, make sure you are allowed to share it with the service, and remove unnecessary information: customer, employee, or account details, and internal notes. Replacing a person’s name with a number does not necessarily make the information anonymous.
In ChatGPT, you can check content-use settings under Settings, then Data controls. Changing the training setting does not delete files. Also, if a file is saved in Library, deleting the chat alone does not delete the copy there. Read the data controls explanation and the chat and file retention policy in the context of your account and your organization’s policies.
Where the training toggle sits in ChatGPT, and what it doesn’t do, is covered in our guide to opting out of AI training on your chats.
Step 3: Upload the file and ask for a diagnosis only
Start a new chat, attach the XLSX file, and first request a review without changes. OpenAI documents support for analyzing Excel and CSV files, but tool availability and quotas depend on the account and environment. A table with clear headers and one record per row is better suited to analysis than a file containing several mixed tables or an image of the data. See the official data analysis documentation.
Diagnostic prompt (written for this guide, not run in ChatGPT):
Inspect the attached file without changing it. Report the sheet names, data-row count, and column names.
Treat the Movement ID and SKU columns as text and preserve leading zeros.
Find extra spaces, ambiguous or invalid dates, missing values, exact duplicates, and repeated identifiers with different data.
Do not fill in values or remove records.
Link every finding to its original row, using the Source Row column.
Propose cleaning rules for approval.
The exercise should show 12 data rows. If the response refers only to the first five rows or does not identify which sheets were checked, request a full review. A detailed response about a sample is not proof that the entire file was checked.
Step 4: Separate safe corrections from business decisions
A space before an item name can be removed under a simple rule. By contrast, 01/02/2026 could mean February 1 or January 2. A price written as 1.234,50 also does not match this exercise’s number-format rule. Although both examples may look familiar, do not interpret them by guessing.
| Example | Action in this exercise | What is preserved |
|---|---|---|
| Identifier 000101 | Keep as text | All leading zeros |
| Double spaces in a name | Reduce to one space | The original name in the source sheet |
| 01/02/2026 | Flag as Ambiguous | The original date, with no normalized date |
| Quantity 0 versus an empty cell | Preserve the distinction | A valid zero and, separately, missing information |
| Two identical records | Flag a duplicate group | Both rows, pending a decision |
This also illustrates the difference between an exact duplicate and an identifier conflict. R003 and R010 are identical across all business fields. R002 and R011 share an identifier, but their quantities differ. Automatically deleting either one could erase information. Likewise, the same item name with a different SKU is not a reason to merge records.
Step 5: Request a clean copy and a change log
After reading the diagnosis, provide explicit rules:
Cleaning prompt (written for this guide, not run in ChatGPT):
Create a new XLSX file without changing the source file.
Include four sheets: Source, with values identical to the source; Clean; Change Log; and Validation.
Keep all 12 rows in their original order.
Keep Movement ID and SKU as text, including leading zeros.
Clean whitespace only in the item-name and supplier columns.
Keep the original date and add a normalized-date column. Convert only valid YYYY-MM-DD dates or valid DD/MM/YYYY dates where the day is greater than 12.
Flag ambiguous, missing, and invalid dates without inventing a date.
Convert whole-number quantities to numbers; leave a missing cell missing.
For prices, allow a decimal point with two decimal places and valid thousands commas; flag every other format for review.
Do not remove or merge duplicates.
Calculate a row value only when both quantity and price are valid, rounded to two decimal places.
Log each value or data-type change with its Source Row and the rule applied.
Summarize exceptions and validation checks, and create a downloadable file.
If ChatGPT returns only an explanation, ask for the result file itself. If file generation is not available in your environment, you can ask for code or instructions to run locally, but that is a separate workflow. Do not present text in a chat as though it were a file that has already been created and tested.
Step 6: Compare values, counts, and totals
Open the result and run a short check against the source and reference solution. These are the expected results for our exercise:
- 12 rows in the source and 12 in the result. Zero rows removed.
- All identifiers and SKUs preserved, including leading zeros.
- Two duplicate groups for review, containing four rows in total.
- Two ambiguous dates, one invalid date, and one missing date.
- One missing quantity and one price left unconverted. Both remain exceptions.
- Known quantities total 64. A row value is calculated for 10 of the 12 records, totaling $4,334.50.
The $4,334.50 total is a partial total for validation only. It includes rows flagged as duplicates and excludes two rows that lack valid data needed for the calculation. It therefore cannot be called the final inventory value. A matching before-and-after total is also insufficient on its own: two opposing errors can cancel each other out. Check counts, identifiers, and values row by row as well.
In the reference file, the row-value formulas were also tested after temporarily changing a quantity and restoring it. Checks confirmed that a quantity of 0 remains zero and that the missing quantity does not become 0. The source-sheet values were compared with the original, and file checks confirmed that the original remained unchanged. These tests used local tools, without opening the files in desktop Excel; for the English files, the formulas were recalculated in LibreOffice Calc.
Frequently asked questions
Why did the zeros disappear when I opened the CSV?
CSV does not preserve cell types in the way XLSX does. Excel may interpret an identifier as a number when opening it. Import through Data, then From Text/CSV, and set identifier columns to text before loading; button names may vary by version. If conversion has already occurred, return to the original file. Changing cell formatting after the zeros have been lost does not restore them. See Microsoft’s explanation of preserving leading zeros.
What about cells containing unexpected formulas?
A CSV from an external source may contain text that opens as a formula, such as a cell starting with an equals sign. The practice files contain no such cells. For a real file, ask for these cells to be identified and flagged before opening or exporting, and do not allow automatic execution. Do not remove characters indiscriminately: a minus sign, for example, can be part of valid data. See OWASP’s explanation of CSV Injection.
Can I simply ask to “remove all duplicates”?
Only after defining a unique key and a decision rule. Two rows with the same name can represent two different movements. First request a list of candidates, then decide separately how to handle identical records and conflicting records.
Can I correct a date based on the rest of the file?
You can use the rest of the file to identify a suspected issue and ask a question. Converting an ambiguous date requires confirmation of its format from an authoritative source, such as the export specification. Once the rule is known, you can apply it to a new copy and document it.
When is the result ready to use?
When the original is preserved, the changes are explained, validation checks reconcile, and the exceptions relevant to your intended use have been resolved. Until then, it is an organized file for further review. Before replacing data in another system, check a copy and perform a small trial import.
Sources
- OpenAI Help Center: Data controls in ChatGPT
- OpenAI Help Center: Chat and file retention in ChatGPT
- OpenAI Help Center: Data analysis with ChatGPT
- Microsoft Support: Keeping leading zeros and large numbers
- OWASP: CSV Injection
