How to find, highlight and remove duplicates in Excel
Show the repeated values, mark the rows you would drop, and delete only after you have chosen which row stays.
To remove duplicates in Excel, show them first and delete them second. Conditional formatting colours repeated values. A COUNTIF column marks the rows. Data → Remove Duplicates deletes rows. UNIQUE and Power Query write a new list and leave the original sheet alone. Copy the sheet before any of the delete steps, and trim spaces before you compare, or rows that only look different will both stay.
Copy the sheet, then check for duplicates
Right-click the sheet tab, choose Move or Copy, and tick Create a copy. Remove Duplicates can be undone with Ctrl+Z while the file stays open. A copy still helps after you have saved, closed the file, or made a second edit. Format the range as a table (Home → Format as Table, or Ctrl+T) so the header is recognised and the range stops at the data.
One risk is easy to miss: Remove Duplicates treats empty cells as equal. Run it on the email column alone and every row without an email is one group, so all of them except the first are deleted. The key column further down keeps those rows apart.
Show duplicate values with conditional formatting
Select one column, for example the email. Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Pick Duplicate and a fill. Excel colours every cell whose value appears more than once, including the first one. The comparison ignores capitalisation and keeps spaces, so "Müller" and "Müller " stay different.
A selection of several columns compares every cell with every other cell. "Berlin" in the city column and "Berlin" in a note both light up. For a whole row, build a helper key or use the formula rule below.
Mark only the repeat, not the original
The formulas in this article use the English function names and a comma between arguments. Which character separates arguments depends on the list separator in your regional settings: a US setup uses the comma, a German one the semicolon.
Select A2:A500 with A2 as the active cell. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format:
=COUNTIF($A$2:A2,A2)>1The range grows as you go down. The first "Müller" counts as 1 and stays plain. The second counts as 2 and is coloured. For name and company together, with those columns in A and C:
=COUNTIFS($A$2:A2,A2,$C$2:C2,C2)>1Mark duplicates with COUNTIF
A helper column gives you a number you can filter, instead of a colour. In E2, for an email in A:
=COUNTIF($A$2:$A$500,A2)Fill down. Filter the column to values greater than 1 and read the group before you delete anything. For several columns at once, COUNTIFS counts a row only when every pair matches:
=COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2,$C$2:$C$500,C2)Find duplicates between two lists
A new export often overlaps with a list you already have. Put both in one workbook. With the new emails in column A and the existing list on a sheet named Customers, also in column A:
=COUNTIF(Customers!$A$2:$A$5000,A2)>0TRUE means the address is already on the other sheet, FALSE means it is new. Filter on the value you need. When the spelling of the two lists differs, build the key below on both sheets and compare the keys.
Line up spaces and capitalisation before you compare
Remove Duplicates already ignores capitalisation. It does not ignore a trailing space, a double space, or a non-breaking space from a website. Power Query is case-sensitive, so capitalisation matters there. Build a key column and compare that:
=TRIM(LOWER(SUBSTITUTE(A2,CHAR(160)," ")))SUBSTITUTE turns a non-breaking space into a normal space, TRIM removes leading and trailing spaces and collapses doubles, LOWER makes the key lower case. Run COUNTIF and Remove Duplicates on this key. The row you keep still shows the original spelling. LOWER does not turn "Müller" into "Mueller". ü and ue remain two spellings.
If some rows have no email, give them a key of their own, so Remove Duplicates does not treat them as one group:
=IF(TRIM(A2)="","empty "&ROW(),TRIM(LOWER(SUBSTITUTE(A2,CHAR(160)," "))))Remove Duplicates on the Data tab
This is the command that deletes. Select a cell in the table, then Data → Remove Duplicates. Tick "My data has headers" when the first row is a header.
- Untick every column that should not define a duplicate.
- Tick the key column if you built one, or the real columns that belong together.
- Confirm. Excel reports how many duplicate values it removed and how many unique values remain.
- Read that number against the filter you looked at before. A surprise count means the wrong columns were ticked.
Excel compares what a cell shows, not the value stored behind it. The same date in two formats counts as two values, so format a date column the same way before you compare it.
The column choice decides what a duplicate is
- Email only: two rows with the same address become one, even when the names differ. A shared mailbox such as info@ then drops a person, and rows with an empty email collapse to one.
- First name, last name and company: the same person exported twice collapses. The same person at a new company stays.
- Every column: only rows that match in every cell are removed. One extra space in one cell keeps both rows.
Keep the row you want, first or newest
The command keeps the first row of each group and deletes the later ones. It does not compare how complete the rows are. Sort so the row you want is above its copies: for example a helper =IF(D2="",1,0) on the phone column, sorted smallest to largest, puts rows that have a phone first. To keep the newest entry, sort a date column from newest to oldest. Then run Remove Duplicates.
Find duplicates with UNIQUE, and keep the original
UNIQUE writes the result beside the source. The source stays. In Microsoft 365, Excel for the web, Excel 2021 and Excel 2024:
=UNIQUE(A2:D500)That spills one row for each distinct combination of A to D. =UNIQUE(A2:A500) does the same for one column. If the range reaches past the last filled row, the empty rows come back as one extra row of zeros. FILTER keeps only rows whose column A is filled, so pick a column that every real row has:
=UNIQUE(FILTER(A2:D500,A2:A500<>""))=UNIQUE(A2:A500,,TRUE) returns only values that occur exactly once, so every repeated value disappears, including the copy you might have wanted to keep. Use the form without TRUE when the goal is one remaining row per duplicate. Older desktop Excel has no UNIQUE. Use COUNTIF and Remove Duplicates there.
Remove duplicates with Power Query
Power Query fits a list you will refresh. The desktop app has it. In Excel for the web, as of November 2026, Microsoft offers the full Power Query experience, including the editor, to Microsoft 365 business and enterprise plans (generally available since January 2026). Other Microsoft 365 subscriptions can view and refresh existing queries, but not create or edit them.
- Select the table. Data → From Table/Range. Confirm that the first row is headers.
- Optional key: Add Column → Format → lowercase, on a copy of the column. Remove duplicates on that key, then delete the key column so the kept email stays in its original spelling. Transform → Format → lowercase would change the values you keep. The same Format menu has Trim, which removes leading and trailing spaces but leaves a double space inside a value.
- Select the columns that define a duplicate. Home → Remove Rows → Remove Duplicates. One column leaves one row per value in that column. Several columns: the combination has to match. Microsoft does not guarantee that the kept row is the first of the group. When the surviving row matters, sort on the sheet and use Remove Duplicates there, then check the result.
- Home → Close & Load. The result lands on a new sheet. The original table stays. Refresh the query when the source changes.
Power Query's remove-duplicates step is case-sensitive. "Mueller" and "mueller" are two values until you compare a lower-case key. Home → Keep Rows → Keep Duplicates does the opposite and shows only the repeated rows, which is a good way to read the groups first. Merge Queries has Use fuzzy matching, with a similarity threshold of 0.8 by default. It proposes matches for you to review. A fuzzy match is a reason to look, not a row to delete.
What exact matching does not see
After trim and lower case, Excel still compares characters.
- "Müller GmbH", "Mueller GmbH" and "Müller Gesellschaft mbH" are three companies. TRIM and LOWER do not fold ü into ue, and they do not know a legal form.
- max.mueller@example.com, m.mueller@example.com and max@example.com are three addresses. A person can have all three.
- The same name at two companies is two rows whenever the company column counts. One row can be the previous employer. The sheet does not say which job is current.
- info@ on two people is one mailbox and two people. Deduplicating on the email column keeps one person and drops the other.
For a sheet you are cleaning once, the steps above are the whole job: copy, key, filter, then delete or spill. A B2B contact list that keeps arriving has the cases in that list, and exact matching leaves them side by side.
A short order of work
- Copy the sheet and format the range as a table.
- Add a key with TRIM, LOWER and SUBSTITUTE for the non-breaking space, and a key of its own for each row without an email.
- COUNTIF on the key, filter to counts above 1, and read the groups.
- Sort so the row you want to keep is first: the most complete, or the newest.
- Remove Duplicates on the key, or UNIQUE or Power Query onto a new sheet.
When the sheet is a B2B contact list
ListPlus is the list you bring B2B contacts into, or where you look for new leads. It checks the rows, fills the gaps that matter, and hands on what you have decided to keep. Excel remains the right tool for a one-off sheet.
100+ checks, including similar names
On a contact list, 100+ checks read the columns you have. For duplicates they include Duplicate Email, Shared Email, Duplicate Name + Company, Duplicate LinkedIn and Similar Name + Company. Duplicate Name + Company treats a person's name as the same without capitalisation, accents or the German umlaut spelling: Müller, Mueller and Muller are one name. A legal form such as GmbH or AG is left out of the company comparison. Similar Name + Company flags a near miss, a typo or a small spelling difference in the name or the company, for you to review. A repeated info@ on two people is a shared mailbox, which is different from one person stored twice.
Standard checks and their corrections cost 0 credits. Optional AI checks show the price before they start. A fix is a suggestion you apply, one row or the group. For an exact duplicate group the more complete row stays, takes values only the other row had, and the other row is marked ignored.
Find B2B leads, then fill a work email and a mobile
You can bring the Excel file, or look for new B2B leads and work them in the same list. Where a work email or a mobile is missing, ListPlus looks it up. Email and phone lookups cost credits only when something is found. Before a paid run you see the steps and a credit ceiling. By default the run fills empty cells and leaves values you already have.
Two rows for the same name at two companies are often both true. Checking stored company data for a sign of a job change is a separate step from deduplicating, and it tells you which row to question before you drop one.
Your CRM, and your own agent
Checked contacts can go to the CRM you already use, for example HubSpot. Your agent runs where it runs. ListPlus provides the list and the tools it calls, on the lists you share with that connection, for Claude, ChatGPT and the other clients on the agents page. The connection is included in every plan, including Basic.
When the sheet is enough
For a table you are cleaning once, copy it, build the key, filter the COUNTIF column, and then use Remove Duplicates, UNIQUE or Power Query. Keep the column choice narrow enough to catch real repeats and wide enough to keep two people apart.
For a B2B list that keeps growing, the same steps still clean the exact repeats. The remaining work is the near spellings, the shared mailboxes and the row whose company has changed, and then the work email or mobile that is still empty. That is the list ListPlus checks and fills.
Questions about removing duplicates in Excel
What does Remove Duplicates do with the first row?
Data → Remove Duplicates keeps the first row of a group and deletes the later rows in the selection. Sort first so the row you want to keep is on top. Conditional formatting and COUNTIF do not delete anything.
Which columns should I tick?
Every ticked column counts. Email only: identical addresses collapse, even when the names differ, and every row with an empty email except the first is deleted, because blank cells count as equal. Name and company: the same person at a new company stays. All columns: only rows that match in every cell.
How do I keep the newest row instead of the first?
Sort a date column from newest to oldest before you run Remove Duplicates. The command keeps whichever row of a group is on top, so after that sort it keeps the newest one.
How do I find contacts that are already in another list?
Count each email against the other sheet, for example =COUNTIF(Customers!$A$2:$A$5000,A2)>0. TRUE means the address is already there. Filter on TRUE or FALSE, depending on whether you want the repeats or the new contacts.
Why do Müller GmbH and Mueller GmbH both stay?
Excel compares characters. ü and ue, a space, a different legal form or a different email are unequal values. TRIM and LOWER line up spaces and capitalisation. They do not turn one spelling of a name into another.
Does UNIQUE or Power Query work in every Excel version?
UNIQUE is in Microsoft 365, Excel for the web, Excel 2021 and Excel 2024. In older desktop versions use COUNTIF and Remove Duplicates. Power Query is in the desktop app. In Excel for the web, as of November 2026, Microsoft offers the full Power Query experience, including the editor, to Microsoft 365 business and enterprise plans. Other Microsoft 365 subscriptions can view and refresh existing queries.