Author: Oscar Cronquist Article last updated on October 02, 2018

You can highlight duplicates in an excel defined table using conditional formatting.

However, that won´t work if you only want to find duplicates among filtered visible values. Example, the table below has filtered values from January 2012.

Highlight duplicate values in a filtered list3

Create a new conditional formatting rule

  1. Select table column "Description"
  2. Go to "Home" tab (Ribbon)
  3. Click "Conditional formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
    Highlight duplicate values in a filtered list4
  6. Copy and paste this conditional formatting formula:
    =SUM(COUNTIF(INDIRECT("Table2[@Description]"), IF(SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)), INDIRECT("Table2[Description]"), "")))>1
  7. Click "Format..." button
  8. Go to Fill tab
  9. Pick a color
  10. Click OK
  11. Click OK

Highlight duplicate values in a filtered list5

Download excel *.xslx file

Highlight duplicates in a filtered table.xlsx