Among the standard Microsoft Excel tools, there are many different ways to highlight duplicates with color. The easiest and fastest way is with conditional formatting. To do this, just select a range of cells and select on the tab Home — Conditional Formatting — Cell Selection Rules — Duplicate Values (Home — Conditional Formatting — Highlight Cells Rules — Duplicate Values):
However, in this case, the fill color of all cells will be the same, i.e. it simply signals that the element has duplicates somewhere else in the range, but does nothing to help find them. You can fix the situation with a small macro that will fill each pair (or more) of repeated duplicates with its own color:
So much clearer, right? Of course, with a large number of repeating cells, it will be difficult to distinguish between shades, but with a relatively small number of duplicates, this method will work perfectly.
To use this macro, press the keyboard shortcut Alt + F11 or button Visual Basic tab Developer, insert a new empty module via the menu Insert – Module and copy the code of this macro there:
Sub DuplicatesColoring() Dim Dupes() 'Declare an array to store duplicates ReDim Dupes(1 To Selection.Cells.Count, 1 To 2) Selection.Interior.ColorIndex = -4142 'Remove the fill if i = 3 For Each cell In Selection If WorksheetFunction.CountIf(Selection, cell.Value) > 1 Then For k = LBound(Dupes) To UBound(Dupes) 'if the cell is already in the array of duplicates, fill in If Dupes(k, 1) = cell Then cell.Interior. ColorIndex = Dupes(k, 2) Next k 'if the cell contains a duplicate, but not yet in the array, add it to the array and fill it If cell.Interior.ColorIndex = -4142 Then cell.Interior.ColorIndex = i Dupes(i, 1 ) = cell.Value Dupes(i, 2) = ii = i + 1 End If End If Next cell End Sub
Now you can select any range with data on the sheet and run our macro using the keyboard shortcut Alt + F8 or through the button Macros (Macros) tab developer (Developer).
- Highlight duplicates with color
- What are macros, where to insert macro code in Visual Basic, how to run them
- How to count the number of unique values in a given range of cells