Weekly Computer Tip # 236
29 June 2008

Filter for unique value
(Excel 2000-2007)

I know, I know, there was no tip last week. (Left for Holland on Friday night but even though coach Marco van Basten seemed confident his team had enough to progress to the half-final it was his fellow Dutchman Guus Hiddink with Russia who won the match. But enough about football ... here's the overdue tip: fancy filtering!)

When you import data you easily end up with duplicate rows. (Been there, done that.) Excel offers a quick way to clean your data, allowing you to filter for unique values.

Here's how:

  1. Excel 2007: on the Data tab, in the Sort & Filter group, select Advanced. OR Excel 2000-2003: On the Data menu, point to Filter, and then click Advanced Filter.
  2. Select the Unique records only check box.
  3. Click OK.

When you filter for unique values, you temporarily hide duplicate values. In Excel 2007 you can permanently delete duplicate values by using the Remove Duplicates command in the Data Tools group on the Data tab.