Excel 2020: Preview What Remove Duplicates Will Remove
August 26, 2020 - by Bill Jelen
The new Remove Duplicates tool added in Excel 2010 was a nice addition.
However, the tool does remove the duplicates. Sometimes, you might want to see the duplicates before you remove them. And the Home, Conditional Formatting, Highlight Cells, Duplicate Values marks both instances of Andy instead of just the one that will be removed. The solution is to create a conditional formatting rule using a formula. Select A2:B14. Home, Conditional Formatting, New Rule, Use a Formula. The formula should be:
It is hard to visualize why this will work. Notice that there is a dollar sign missing from $A$1:$A1. This creates an expanding range. In English the formula says "Look at all of the values from A1 to A just above the current cell and see if they are equal to the current cell". Only cells that return >0 will be formatted.
I've added the formula to column C below so you can see how the range expands. In Row 5, the COUNTIF checks how many times Andy appears in A1:A4. Since there is one match, the cell is formatted.
Title Photo: Victor Garcia at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.