In a previous article Identify duplicate invoice records in excel I created a list of the original invoices and their duplicates. This article shows you how to quickly highlight duplicate invoice records.

Here is a list of invoices and some duplicates. I use sale date, vendor invoice, amount and client to identify duplicate invoices. In column "Paid amount", duplicates have zero as value.

identify-duplicate-invoice-records

To highlight duplicates I use this conditional formatting formula:

=IF($F2=0, ISNUMBER(MATCH($B2&$C2&$D2&$E2, (Sale_date&Vendor_invoice&Amount&Client), 0)), FALSE)

Named ranges
Invoice A2:A7
Sale_date B2:B7
Vendor_invoice C2:C7
Amount D2:D7
Client E2:E7
Paid_amount F2:F7

What is named ranges?

Here is a picture of highlighted duplicate invoices.

identify-duplicate-invoice-records_3

How to apply the conditional formatting formula in excel 2007:

  1. Select the range (A2:F7)
  2. Click "Home" tab on the ribbon
  3. Click "Conditional formatting"
  4. Click "New rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Click "Format values where this formual is true" window.
  7. Type =IF($F2=0, ISNUMBER(MATCH($B2&$C2&$D2&$E2, (Sale_date&Vendor_invoice&Amount&Client), 0)), FALSE)
  8. Click Format button
  9. Click "Fill" tab
  10. Select a color
  11. Click OK!
  12. Click OK!

Download excel example file
identify-duplicate-invoice-records-using-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value