Do you have long lists of data with repeated entries in some columns? Excel can help you find any incorrect entries by reporting how many unique entries are in a column and identifying the unique entries.

For example, if you have a list like the following, with details about individual sales, you might want to know how many different products are listed.

Filter for unique entries

1. On the Data menu, point to Filter, and then click Advanced Filter.
2. Click Copy to another location.
3. In the List range box, delete any information that Excel has filled in, and then click the column you're interested in. In the example above, you'd click column B.
4. Click the Criteria range box, and then click the column label. In the example, you'd click Product in cell B1.
5. Click the Copy to box, and then click a blank column. In the example, you could click column F.
6. Select the Unique records only check box.

   When you click OK, Excel copies a list of the unique entries into the blank column, like this:

   

Count the unique entries

If your list of unique entries is long, you can use the COUNTA worksheet function to count them. For example, the following formula counts the number of entries in column F, minus 1 for the column label (Product):

=COUNTA($F:$F)-1

Update the list of unique entries

If you change the original data or add to it, you can repeat the advanced filter command to extract a new list of unique values. Excel remembers your settings for the list range, criteria range, and copy to location, so that the new list of unique values replaces the old list.

More information

For more information about advanced filtering, type advanced filter in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

For more information about the COUNTA worksheet function, type counta in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.