To remove duplicates, click a cell in your list, choose Data → Remove Duplicates, tick the columns that make two rows the same, and click OK. To find them first without deleting anything, use COUNTIF.
Point at a step to see the sheet right after it.
A2:A6 = Ana Souza, Tom Berg, Thomas Berg, Mei Chen, – Only the row that repeats word for word went. Thomas Berg stays, because his name differs from Tom’s.
Lists pick up duplicates whenever two of them are combined: sign-ups from two forms, contacts from two people’s address books, a monthly export pasted under last month’s. Counts come out too high, the same customer gets two emails, and a lookup finds the first copy and never the second.
Excel has two jobs here: finding the duplicates, which is a formula and changes nothing, and removing them, which is a command on the Data tab and deletes rows. Below you remove the repeats from a mailing list step by step, then see each part taken apart on a small sheet, including the two ways a duplicate slips through.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
COUNTIF counts how many times a value is in a range. Put it beside each row with the
row’s own value, and every row on the list more than once shows a number above 1: both copies, not
only the second. Nothing is deleted, so it’s the safe first look at how many repeats there are.
D4 = 2 Every row whose email is on the list more than once shows 2 or more, both copies included. Filter the column for values above 1 to see them all before you delete anything.
To see each value once without touching the list, use UNIQUE: =UNIQUE(B2:B200) lists every different email down the column, and wrapped in COUNTA it says how many
people the list really holds. It’s in Microsoft 365, Excel 2021 and later.
E2 = 3 Three people. On its own, =UNIQUE(B2:B6) lists those three emails down the column, and the original stays as it is.
To see duplicates at a glance, select the column and use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel highlights every value that appears more than once, and nothing is deleted.
Click a cell in the list, then Data → Data Tools → Remove Duplicates. On Windows the keyboard shortcut is Alt, A, M, pressed one after another. Excel selects the whole list around the cell and guesses whether the first row is a header; the “My data has headers” box shows its guess, and you can change it. In an Excel table, the same button is also on the Table Design tab.
Click OK and Excel deletes the rows that repeat an earlier one, moves the rest up and reports how many went and how many unique rows remain. The first row of each repeat always stays. Cells outside the list don’t move, so a column you left out of the selection stops lining up with the rows beside it. Excel warns you when it sees data next to your selection; take “Expand the selection” unless you mean it.
The dialog lists every column with a box beside it, all ticked to start with. A row goes only when it repeats an earlier row on every ticked column. Ticking everything removes rows that are exact copies; ticking fewer removes more. The question is what identifies one record on your list: an email, a customer ID, an order number.
A2:A6 = Ana Souza, Tom Berg, Thomas Berg, Mei Chen, – Name and email together: Tom and Thomas differ on the name, so both stay. More columns ticked means fewer rows count as repeats.
Point at a step to see the sheet right after it.
Unselect All and then ticking the one column you mean is quicker than unticking the others. Never tick only a column whose values repeat by design, such as a city, a category or a status.
Excel compares the values the cells show, without regard to capitals: “Oslo”, “OSLO” and “oslo” are one value. A space is part of the value, though, and an export often leaves one at the end of a cell, where nobody sees it. “Oslo ” then counts as different, and the duplicate stays.
A2:A5 = Oslo, Oslo , –, – OSLO and oslo went: Excel ignores capitals. The Oslo with a space after it stayed, because to Excel that space makes it a different value.
Point at a step to see the sheet right after it.
So clean a column before you remove duplicates from it: =TRIM(B2) in a spare column,
filled down and pasted back as values. Data cleaning practice covers TRIM and the other fixes. Excel
also goes by how a value is displayed and stored: the same date shown as 2026-12-08 in one row and
8 Dec 2026 in another counts as two values, and so does 10 stored as a number in one row and as text
in another.
Of every set of repeats, the row nearest the top stays and the rest go. Excel doesn’t look at which is newer or more complete. When it matters which copy survives, sort the list first so the one you want is on top: newest date first, or the paid plan before the free one.
A2:B3 = ana@example.com, Free, tom@example.com, Free Ana’s first row stayed and her later Paid row went. Excel keeps whichever comes first, with no regard for which is newer or better.
Point at a step to see the sheet right after it.
Remove Duplicates deletes rows, and the message box is the only record of what went. Undo puts
them back straight away (Ctrl+Z, or Cmd+Z on a Mac). For anything you can’t easily rebuild, copy
the sheet first (right-click its tab → Move or Copy → Create a copy), or count first with
COUNTIF and UNIQUE and compare the numbers afterwards. When you need the list with and without
its repeats, leave the original alone and build the unique list beside it with UNIQUE.
To carry on in your own Excel, download the mailing list. It’s the same job at full size: 160 people merged from four sign-up forms, 30 of them on it twice in four different ways, and a tasks sheet that finds them, removes them and checks the count, with the answers on a sheet of their own.
A mailing list merged from four sign-up forms: 160 people, 30 of them on it twice. Exact repeats, emails in capitals, people who signed up again under another name, and trailing spaces. Practice COUNTIF, UNIQUE, Remove Duplicates and TRIM.
190 rows · Name · Email · City · Source · Signed up
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches Remove Duplicates: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →