This blog post shows you how to easily identify duplicate rows or records in a list.

### Conditional formatting formula:

=COUNTIFS(\$A\$1:\$A\$30, \$A1, \$B\$1:\$B\$30, \$B1, \$C\$1:\$C\$30, \$C1)>1 + ENTER

### How to use conditional formatting formula

1. Select cells A1:C30
2. Click "Home" tab
3. Click "Conditional Formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Type =COUNTIFS(\$A\$1:\$A\$30, \$A1, \$B\$1:\$B\$30, \$B1, \$C\$1:\$C\$30, \$C1)>1 in "Format values where this formula is TRUE" window.
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color for highlighting cells.
10. Click "Ok"
11. Click "Ok"
12. Click "Ok"

### How the conditional formatting formula works

The formula contains absolute and relative cell references. In each cell the formulas cell references changes.

In cell A1: COUNTIFS(\$A\$1:\$A\$30, \$A1, \$B\$1:\$B\$30, \$B1, \$C\$1:\$C\$30, \$C1)>1

becomes

COUNTIFS({1, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0}, {1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0})

becomes

COUNTIFS({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}) and returns 1.

COUNTIFS({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0})>1 returns False and cell A1 is NOT formatted with a cell color.

In cell A2 the formatting formula changes to: COUNTIFS(\$A\$1:\$A\$30, \$A2, \$B\$1:\$B\$30, \$B2, \$C\$1:\$C\$30, \$C2)>1 and returns FALSE and is also NOT formatted with a cell color.