Excel Study · Module 2: Entering & formatting data
Class 20Remove duplicates
Basics1:38 videoFree
A list with repeats? First count them with one formula, and see why you should always test an AI's formula in Excel. Then Data → Remove Duplicates keeps the first of each and deletes later repeats for good, so copy your data first.
Try this AI prompt
Works in ChatGPT, Gemini, Copilot or Claude.
In Excel, A2:A7 has names and some names repeat. Write one formula for B9 that counts the repeats: the number of names minus the number of different names. It must work in every Excel version.
The answer that works
✓ checked in Excel=COUNTA(A2:A7)-SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))
AI can get Excel wrong. Paste its answer into Excel and test it before you trust it.