The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has been filtered to show only records for January 2012.
There is a built-in function in Excel that lets you highlight duplicates, however, that won't work properly if you have filtered the data.
The image above shows the built-in tool highlighting duplicate items. But one item exists only once in the filtered data, however, it is highlighted as a duplicate.
The reason is that there is another item not showing in the filtered table. You need to rely on a custom conditional formatting formula if you want it to compare only filtered visible values.
Note, Conditional formatting is volatile and may slow down your worksheet considerably.
Build an Excel Table
The reason I am using an Excel Table is that the conditional formatting adjusts automatically if you add or delete records. It also uses structured references so there is no need to change cell references in the formula.
The disadvantage is that you have to use the INDIRECT function each time you reference the Excel Table in the CF formula, it is not a big deal but it is worth noting.
Select any cell in your data set.
Press short cut keys CTRL + T to open the "Create Table" dialog box.
Enable the checkbox if the columns in the data set have header names.
Click OK button to apply.
Create a new conditional formatting rule
Select table column "Description".
Go to the "Home" tab on the ribbon.
Click the "Conditional formatting" button.
Click "New Rule..".
Click "Use a formula to determine which cells to format".
Paste to "Format values where this formula is true:". I will explain the formula later in this article.
Click the "Format..." button.
Go to tab "Fill".
Pick a color.
Click OK button.
Click OK button again.
Explaining CF formula in cell C211
This Conditional Formatting formula highlights cells that have a duplicate in a filtered Excel Table, note that it will not be highlighted if a duplicate exists outside the filtered values (not visible).
Step 5 - Check the number of times the current value exists across visible values
The COUNTIF function will return an array with this setup which we then can use to calculate a total. The reason we don't change the arguments with each other is that the range argument will not accept an array based on calculations.
The > larger than character is a logical operator that lets you compare values, it will return a boolean value TRUE or FALSE based on the outcome. The Conditional Formatting formula uses the boolean function to determine if the cell values should be highlighted or not.