Highlight duplicates in Excel

How to highlight duplicates in Excel

1. Open the file. Drop the .xlsx or .csv onto the box. It is read in your browser and is never uploaded.
2. Choose what repeats. Repeated values in one column, rows that are identical across every column, or values that appear in two columns at once.
3. Pick the colouring. One colour for every duplicate — the same pale red Excel uses — or a different colour per group, so you can see at a glance which rows belong to which repeated value.
4. Fill the cell or the whole row. Colouring the whole row is what you want when the duplicate is a record rather than a value, because the row stays readable as a unit when you sort.
5. Download the highlighted workbook. The .xlsx comes out with the fills already applied, so the colours survive being opened in Excel, LibreOffice or Numbers.

How Excel does it, and the one thing it will not do

In Excel the route is Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Select the range first, pick a fill, and every value that appears more than once in that selection turns colour. It is four clicks and it works.

What it will not do is give each repeated value its own colour. Excel paints every duplicate the same shade, so a column with forty repeated invoice numbers becomes forty pink cells with no way to see which ones are the same as which. Doing it properly in Excel means VBA or forty separate conditional-formatting rules. Here it is a dropdown.

The second thing it will not do is colour the whole row. Conditional formatting can be coaxed into it with a formula rule like =COUNTIF($A:$A,$A1)>1 applied to the whole range — worth knowing, and fiddly enough that most people give up. Switching Fill to "the whole row" does the same thing here.

Why highlighting beats deleting, at least first

Data → Remove Duplicates is the other button people reach for, and it is destructive: it takes the rows out and tells you a count. Highlighting leaves the file intact and makes the problem visible, which is the right order when you are not yet sure the duplicates are really duplicates — two customers can legitimately share a surname, and two rows can share an email because one is a returns record.

If what you actually want is the list of duplicated values and the clean file, that is the Excel duplicate finder next door. This page is for when you want to look at your own sheet with the repeats marked.

Case, spaces, and blanks

Bob@example.com and bob@example.com are the same mailbox and different strings; ACME Ltd and ACME Ltd  differ by a trailing space that came in with a copy-paste. Leading and trailing spaces are always trimmed here and case is ignored unless you turn that off — the same two rules Excel's own duplicate rule applies, which is why its results and these agree.

Empty cells are not treated as duplicates of each other. A column with three hundred blanks would otherwise come back as one enormous highlighted group, which tells you nothing.

The colours survive the download

The .xlsx written here carries a real styles.xml, so the fills are part of the workbook rather than a picture of one — open it in Excel, LibreOffice or Numbers and the highlighting is there, editable, and it sorts and filters with the rows. That is the part a screenshot of a web tool cannot give you.

Numbers stay numbers and text stays text: leading zeros in a postcode or a staff number are preserved rather than being silently turned into 1234, which is the failure mode of most online converters.

When the highlighting should be permanent

A browser tab is the right place for a one-off look. When the same export lands every month and the same duplicates need flagging every time, the fix belongs in the file rather than in a tab: in TableDI a Dedupe column marks repeats as a rule that recalculates for rows pasted in next month, and the rule travels with the workspace. How the cleaning rules work.