An array formula is a formula that works with arrays or series of data rather than single data values. When you enter an array formula, type the formula in the cell and then press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and when you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }.

You can download an example workbook here that illustrates all the formulas on this page.

Testing A List For Duplicate Items

The formula below will display the words "Duplicates" or "No Duplicates" indicating whether there are duplicates elements in the list A2:A11.

=IF(MAX(COUNTIF(A2:A11,A2:A11))>1,"Duplicates","No Duplicates")

An alternative formula, one that will work with blank cells in the range, is shown below. Note that the entire formula should be entered in Excel on one line.

=IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A11<>"")*ROW(A2:A11)))),
INDIRECT("A2:A"&(MAX((A2:A11<>"")*ROW(A2:A11))))))>1,"Duplicates","No Duplicates")

Highlighting Duplicate Entries

You can use Excel's Conditional Formatting tool to highlight duplicate entries in a list.  All of the examples in this section assume that the data to be tested and highlighted is in the range B2:B11. You should change the cell references to the appropriate values on your worksheet.

This first example will highlight duplicate rows in the range B2:B11. Select the cells that you wish to test and format, B2:B11 in this example. Then, open the Conditional Formatting dialog from the Format menu, change Cell Value Is to Formula Is, enter the formula below, and choose a font or background format to apply to cells that are duplicates.

=COUNTIF($B$2:$B$11,B2)>1

 The formula above, when used in Conditional Formatting, will highlight all duplicates. That is, if the value 'abc' occurs twice in the list, both instances of 'abc' will be highlighted. This is shown in the image to the left, in which all occurrences of 'a' and 'g' are higlighted.