Upload a workbook, pick a sheet and the columns that make a row a duplicate, and remove the repeats or highlight every duplicate row. See the groups first, then download your own file with only that change made.
Nothing is uploaded. Your file is edited in your browser and never sent anywhere.
This tool lets you remove duplicates in Excel files without opening Excel: drop in an .xlsx or .xlsm, tick the columns that make a row a repeat, and Remove deletes the extra copies in your browser, keeping the first row of each group. Highlight deletes nothing. It fills every row of each group in light red, the first copy included, for you to review. The typical file is a mailing list merged from three sign-up exports, with one email turning up four times.
contacts.xlsx downloads as contacts (xlsx.com).xlsx, with nothing added inside it.Two rows are duplicates when every ticked column holds the same value. Tick Email alone and rows that repeat an address go, whatever their names and phone numbers say. Tick First name, Last name and Postcode and all three have to match.
To start with, the tool ticks the data block: every column from the first one with data up to the first empty column, the same way Excel picks the range around a selected cell. A note typed off to the side, past an empty column, isn't ticked, and Highlight doesn't fill it; Remove still deletes it with its row. "The first row is a heading" is ticked for you when the first row is all text, and a heading row is never compared or removed.
Rows with nothing in any ticked column are skipped, so a run of blank rows isn't treated as a set of duplicates. A blank cell does match a blank in the same column when the other ticked columns agree.
The comparison matches Excel's in most places, and sides with the UNIQUE function where the Remove Duplicates button differs:
| The two values | This tool (defaults) | Data > Remove Duplicates | UNIQUE |
|---|---|---|---|
| abc and ABC | Duplicates | Duplicates | Duplicates |
| The number 1 and the text 1 | Different | Different | Different |
x (trailing space) and x | Different | Different | Different |
| One date shown as 3/15/2023 and as 15 Mar 2023 | Duplicates | Different | Duplicates |
A formula is compared by its result, so =1+1 matches a typed 2, in the tool and in Excel's Remove Duplicates alike. Numbers that only look alike stay apart in both: 1.6 and 2, formatted to show "2", are kept twice.
Two options change the defaults. Untick "Ignore upper and lower case" and abc no longer matches ABC. Tick "Trim spaces first" and spaces at either end are ignored, runs of spaces between words count as one, and non-breaking spaces count as ordinary spaces.
When Remove Duplicates reports no duplicates on rows you can see are the same, one of three things differs underneath.
The display. Microsoft's help page says Excel judges duplicates by the value displayed in the cell. Tested in Excel for Mac, it needs both: the same stored value and the same displayed text. Two rows can hold the same date with different number formats, one showing 3/15/2023 and the other 15 Mar 2023, and Excel keeps both. The tool compares the stored date, so those two rows are one date. Reformatting the whole column to one date format fixes it in Excel.
Spaces. "Ann Lee " with a trailing space is not "Ann Lee" to Excel, and a name copied from a web page often carries a non-breaking space that looks like an ordinary one. The TRIM function removes the first problem and leaves non-breaking spaces where they are, which is why a TRIM helper column sometimes changes nothing. "Trim spaces first" in the tool handles both.
Numbers stored as text. An order number 1045 typed in one row and imported as the text 1045 in another are different values, to Excel and to this tool. Convert the text column to numbers with Data > Text to Columns > Finish, then remove duplicates.
Highlight keeps every row and fills every row of every duplicate group across the data block, the first row included, in the light red Excel's own Duplicate Values highlight uses. Each cell keeps its font, borders and number format. No rule and no helper column is added.
The fill is plain formatting, not a conditional formatting rule, so it stays where it is when you later edit the data. One colour covers every group, and the kept row is coloured too, so the highlight doesn't show which row Remove would keep. The preview does, for the first 8 groups.
The duplicate rows are deleted as if you had deleted them in Excel. Formulas on every sheet, defined names, Excel tables, chart series, merged cells, conditional formatting, data validation, hyperlinks and notes that pointed below a removed row move up to match. The row-deletion engine is the one Remove Blank Rows uses, checked against Excel's own row deletion.
A formula that pointed only at cells in a removed row shows #REF! afterwards, as it would in Excel, and the result says how many formulas that happened to. Notes on removed rows go with them, and the result counts those as well.
Everything else is kept byte for byte, including charts, pictures, pivot tables on other sheets and the macros in an .xlsm. The file is edited, not rebuilt.
Click a cell in the data, then Data > Remove Duplicates. Tick "My data has headers" if row 1 holds headings, untick every column that shouldn't decide a match, and click OK. Excel says how many duplicates it removed and how many unique values remain. Ctrl+Z undoes it. Microsoft notes it won't work on outlined or subtotalled data, so remove those first.
For a copy with the duplicates gone and the original untouched, the UNIQUE function (Microsoft 365 and Excel 2021 or later) spills the distinct rows of a range:
=UNIQUE(A2:F500)It compares stored values, so the date shown two ways counts once, as it does in the tool.
To flag duplicates in a helper column, count earlier rows with the same key:
=COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)>1Filled down, it shows TRUE on the second and later copies. COUNTIF-based checks have their own traps: the text 0123, 00123 and 123 count as the same value, two different 16-digit IDs stored as text count as duplicates because COUNTIF compares numbers to 15 digits, and * or ? in a value act as wildcards. The COUNTIF generator builds COUNTIF and COUNTIFS formulas when you need a count instead.
For a sheet you already have open, the Data tab is fine. The tool earns its place when you want to see the groups and their row numbers before anything is deleted, or fill each duplicate row across the data without writing a rule.
When the repeats you're chasing sit in two different lists, such as this month's customers against last month's, Compare Two Columns shows which values are in both and which are only in one.
Use Data > Remove Duplicates: Excel keeps the first of each set of duplicates and removes the rest from the selected range. This tool keeps the first row too, and its preview lists the first 8 groups before anything is deleted, for example Row 2 kept; rows 4 and 8 removed.
Tick only that column in the Remove Duplicates dialog. Rows that repeat a value in it are removed even when their other columns differ, and the first row with each value stays. Excel removes them from the selected range only, the range's other columns included, and shifts the rest of the range up, so a note beside the data and outside the range stays put and can end up next to the wrong row. This tool deletes whole worksheet rows instead, as if you had deleted the rows yourself.
The rows aren't identical to Excel, even though they look it. The usual causes are a trailing space or a non-breaking space copied from a web page, a number stored as text in one row and as a number in the other, and the same date shown in two different formats. Data > Remove Duplicates needs the displayed text to match as well as the value, so 3/15/2023 and 15 Mar 2023 are kept as two rows.
Select the column and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values: it colours every repeated cell, first copy included, judging each cell on its own value rather than the whole row. This tool's Highlight duplicates option fills every row of each duplicate group across the data, judged on the columns you tick, and deletes nothing.
Sort by the date column, newest to oldest, then remove duplicates. Excel's Remove Duplicates always keeps the first row it meets, so after the sort the first row of each group is the newest one. This tool also keeps the first row of each group, so sort in Excel before you drop the file in.
No: Data > Remove Duplicates and the UNIQUE function both treat abc and ABC as the same value. In this tool, untick Ignore upper and lower case to keep them apart.