Excel's Remove Duplicates button deletes rows immediately and always keeps the first one. The hard part is deciding which columns define a duplicate, which record to keep, and whether a row is genuinely a duplicate or just looks like one. Paste your headers and a sample into ChatGPT and get the exact column selection, sort order, and COUNTIFS formula to verify before you delete anything.
I need to remove duplicates from an Excel table, but I'm not sure which columns should define a duplicate record. My column headers are: [paste your column headers here — e.g. Order ID, Customer, Date, Product, Amount] A sample of 5–10 rows: [paste rows here] Business rule for a duplicate (if known): [describe what makes two rows represent the same real-world record; if unsure, write "unknown"] Please tell me: 1. Which column combination is the strongest CANDIDATE duplicate key from the sample 2. What business-rule assumption that recommendation depends on 3. Whether any column alone is sufficient, or whether multiple columns are needed 4. Which columns should probably NOT be part of the key and why 5. Which rows are candidate duplicates versus rows that may be valid separate records 6. What I must confirm before deleting anything Do not declare a row "safe to delete" from the sample alone if the business rule is not explicit. Do not automatically exclude timestamps, row IDs, or generated keys: explain whether they are identifiers, audit fields, or part of the actual business key. Do not infer what an ID or timestamp represents beyond what the column name and sample prove, and avoid absolute statements such as "never part of the key" unless that follows from an explicit business rule.
Paste real headers and rows — ChatGPT uses your actual column names in the answer. Works in ChatGPT.
I have duplicate rows in Excel and need to decide which one to keep. My dataset has these columns: [paste your column headers] The columns that define a duplicate are: [e.g. "Customer Name + Product + Month" — from the previous prompt or your own analysis] For each set of duplicates, I want to keep: [choose one:] - The most recent row (based on column: [date column name]) - The row with the highest value (based on column: [amount/quantity column name]) - The first row (default Excel behaviour) - Other rule: [describe it] Please give me: 1. The sort order to apply BEFORE running Data → Remove Duplicates so the row I want to keep ends up first 2. The exact columns to check in the Remove Duplicates dialog 3. A COUNTIFS helper formula to apply AFTER the sort, so occurrence 1 is the row that would be kept and occurrences 2+ are the rows that would be removed 4. A warning if my chosen sort field has ties, blanks, or text-formatted dates/numbers that could make the sort unreliable Use the minimum sort needed to guarantee that the desired row appears first for each duplicate key. Do not add sort levels unless they are required for the keep rule or to resolve ties. Do not infer that a lower/higher ID is the original record unless that rule is explicitly stated. If a deterministic tie-break is needed, propose it as an option that requires confirmation. For text-formatted dates or numbers, distinguish visual clues from reliable tests such as ISNUMBER().
Excel always keeps the first occurrence — sorting first lets you control which row that is.
Before I delete anything, I want to flag duplicate rows in Excel so I can review them first. My column headers: [paste headers] The columns that define a duplicate: [e.g. Customer + Date + Amount] Please write me: 1. A COUNTIFS helper column formula that returns 1 for the first occurrence and 2, 3... for later occurrences — so I can filter and review duplicates before deleting 2. A formula that marks only the rows that WOULD BE DELETED (occurrences 2+) with the word "DELETE" 3. A formula to calculate how many data rows will remain after deduplication using the helper column 4. A formula to calculate how many rows are flagged for deletion State exactly which worksheet columns each COUNTIFS range refers to and make all expanding ranges the same size. Also tell me what to check in rows marked DELETE before removing them — especially whether excluded columns contain data that differs from the kept row. If the current row order matters, say so explicitly. Do not call a flagged row "safe to delete" solely because the visible sample columns match. State that deletion is only justified after confirming all relevant excluded columns and the business rule.
Never run Remove Duplicates on your only copy. Work on a duplicate sheet or paste the result into a new workbook first.
| Import Row ID | Customer | Product | Date | Amount | Dup check on Customer only |
|---|---|---|---|---|---|
| R-1041 | Acme Corp | Widget Pro | 15/01/2026 | £4,200 | 1st — KEPT |
| R-1087 | Acme Corp | Widget A | 22/01/2026 | £1,800 | DELETED ✗ |
| R-1112 | Acme Corp | Widget Pro | 05/03/2026 | £4,200 | DELETED ✗ |
| R-1203 | Nord Ltd | Widget B | 10/02/2026 | £9,100 | 1st — KEPT |
| R-1301 | Nord Ltd | Widget B | 10/02/2026 | £9,100 | DELETED ✗ |
| Import Row ID | Customer | Product | Date | Amount | Dup check: Customer+Product+Date+Amount |
|---|---|---|---|---|---|
| R-1041 | Acme Corp | Widget Pro | 15/01/2026 | £4,200 | 1st — KEPT ✓ |
| R-1087 | Acme Corp | Widget A | 22/01/2026 | £1,800 | Unique — KEPT ✓ |
| R-1112 | Acme Corp | Widget Pro | 05/03/2026 | £4,200 | Different date — KEPT ✓ |
| R-1203 | Nord Ltd | Widget B | 10/02/2026 | £9,100 | 1st — KEPT ✓ |
| R-1301 | Nord Ltd | Widget B | 10/02/2026 | £9,100 | Candidate duplicate — REVIEW |
Candidate key: Customer + Product + Date + Amount. Customer alone is clearly too broad in this sample. Import Row ID differs between otherwise identical rows, so treat it as an import/audit identifier unless your business rules say otherwise. Candidate duplicate: R-1301 matches R-1203 on all four business fields. Confirm that those four fields really define one business record before deleting. ✓
| Customer | Product | Last Updated | Status | Amount | Default: kept? |
|---|---|---|---|---|---|
| Acme Corp | Widget Pro | 03/01/2026 | Draft | £4,200 | KEPT ✗ (oldest) |
| Acme Corp | Widget Pro | 18/02/2026 | Approved | £4,200 | DELETED ✗ |
| Acme Corp | Widget Pro | 05/06/2026 | Invoiced | £4,200 | DELETED ✗ |
| Nord Ltd | Widget B | 10/01/2026 | Draft | £9,100 | KEPT ✗ (oldest) |
| Nord Ltd | Widget B | 14/07/2026 | Invoiced | £9,100 | DELETED ✗ |
| Customer | Product | Last Updated | Status | Amount | After sort + dedup |
|---|---|---|---|---|---|
| Acme Corp | Widget Pro | 05/06/2026 | Invoiced | £4,200 | KEPT ✓ (most recent) |
| Acme Corp | Widget Pro | 18/02/2026 | Approved | £4,200 | deleted |
| Acme Corp | Widget Pro | 03/01/2026 | Draft | £4,200 | deleted |
| Nord Ltd | Widget B | 14/07/2026 | Invoiced | £9,100 | KEPT ✓ (most recent) |
| Nord Ltd | Widget B | 10/01/2026 | Draft | £9,100 | deleted |
Step 1: Sort by Last Updated → Z to A (newest first) Step 2: Data → Remove Duplicates → check Customer + Product Result in this sample: most recent row per Customer+Product is kept. Acme Corp: Invoiced ✓ | Nord Ltd: Invoiced ✓ If Last Updated contains ties or text dates, add a deterministic secondary sort before deduplication.
| Customer | Product | Date | Amount | Notes | Helper col (missing) |
|---|---|---|---|---|---|
| Acme Corp | Widget Pro | 15/01/2026 | £4,200 | — | ? |
| Acme Corp | Widget Pro | 15/01/2026 | £4,200 | Amended T&Cs | ? |
| Nord Ltd | Widget B | 10/02/2026 | £9,100 | — | ? |
| Nord Ltd | Widget B | 10/02/2026 | £9,100 | — | ? |
| Blue Fin | Widget A | 22/03/2026 | £2,800 | — | ? |
| Customer | Product | Date | Amount | Notes | =COUNTIFS helper | Action |
|---|---|---|---|---|---|---|
| Acme Corp | Widget Pro | 15/01/2026 | £4,200 | — | 1 — keep | KEEP |
| Acme Corp | Widget Pro | 15/01/2026 | £4,200 | Amended T&Cs | 2 — duplicate | ⚠ Review first |
| Nord Ltd | Widget B | 10/02/2026 | £9,100 | — | 1 — keep | KEEP |
| Nord Ltd | Widget B | 10/02/2026 | £9,100 | — | 2 — duplicate | DELETE |
| Blue Fin | Widget A | 22/03/2026 | £2,800 | — | 1 — keep | KEEP |
=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2,$D$2:$D2,D2) Returns 1 for first occurrence, 2+ for later occurrences. Action formula (G2): =IF(F2>1,"DELETE","KEEP") Rows remaining: =COUNTIF(F2:F6,1) → 3 Rows flagged for deletion: =COUNTIF(F2:F6,">1") → 2 ⚠ Acme Corp occurrence 2 has data in Notes — reconcile that data first. Nord Ltd occurrence 2 has no differing Notes in this sample, but still review excluded columns before deleting.
Copy the entire sheet to a new tab before touching anything. Excel provides Undo after Remove Duplicates in the current editing session, but do not rely on the undo history as your backup. Copy the original range/sheet first. Working on a copy means you can always go back. If your dataset is large, paste values only to keep the backup fast.
Paste your headers, sample rows, and—if you know it—the business definition of a duplicate into ChatGPT. Ask for a candidate key and the assumptions behind it. ChatGPT can spot structural patterns, but a sample alone cannot prove the real-world business key. Using too few columns can delete valid rows; using too many can miss duplicates.
Before deleting anything, add the COUNTIFS helper using exactly the confirmed duplicate-key columns. If you plan to keep the newest/highest row, sort first and then calculate/recalculate the helper so occurrence 1 matches the row Excel would keep. Filter values greater than 1 and compare excluded columns before deletion.
Excel's Data → Remove Duplicates always keeps the first occurrence and deletes the rest. If you need the most recent record, sort by date descending first. If you need the highest value, sort by amount descending. The sort determines which row appears first and therefore which occurrence is kept. Verify the sort field is a real Excel date/number, not text, and define a secondary sort when ties are possible.
Go to Data → Remove Duplicates, check only the columns ChatGPT identified, and click OK. Excel will tell you how many rows were removed and how many remain. Verify the message against the helper only if the helper uses the same duplicate-key columns and the same row order as the final Remove Duplicates operation. With the progressive COUNTIFS pattern on the final sorted data, COUNTIF(helper_range,">1") should equal the number of removed rows, while COUNTIF(helper_range,1) gives the expected remaining data rows.
Data → Remove Duplicates. In the dialog, select the columns that together define a unique record — for example, Customer + Product + Date, not Customer alone. Excel keeps the first occurrence of each unique combination and deletes the rest. Always work on a copy of your data first — removing duplicates deletes data from the selected range, so keep a separate copy of the original first.=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2) in a helper column, adjusting the column references to match your duplicate-defining columns. The expanding range $A$2:$A2 counts how many times the combination has appeared up to and including the current row. A value of 1 means first occurrence; 2 or more means a later occurrence in the current row order. Every COUNTIFS criteria range must expand to the same row. If you sort to control which record is kept, calculate/recalculate this helper after sorting, then filter values greater than 1 for review.