KC Lemson

By KC Lemson [MS]

Blogs

How to filter out rows with duplicate content in Excel

  • Comments 7
  • Likes

If you have data in Excel and you want to hide rows with duplicate entries, it's super easy:

1. Select the data you want to filter
2. Data | Filter | Advanced Filter
3. Click “Unique Records Only“

Excel will then hide rows that are duplicates of each other. I use this when putting together stats for the Exchange blog. At one stage in the process, I have a CSV full of rows with the article title and search terms used to get to that article. I want to let the authors of the blog know what terms were used to get to their articles, so I filter out the duplicate rows to make it more digestable.

Comments
  • That is the most amazing Excel tip ever! Thank you, thank you, thank you. Who knew it was there all along?

    --jason

  • Jason: Comments like yours make this so worthwhile :-) Thanks.

  • Another cool trick:

    If you want to do a quick count/sum/avg of some numbers, simply select them and look in the lower right at the status bar. If you don't want whatever its showing you (eg, it shows you sum and you want avg, right click it)

  • John: Way ahead of you, man: http://blogs.msdn.com/kclemson/archive/2003/10/24/53879.aspx

    ;-)

  • Oh monkey pants.

  • Did not know you could do that,.....thanks much

  • This blog entry on eliminating duplicate rows in Excel has changed my life... thank you, earnest looking Microsoft employee! Now why the fuck isn't this in the help menu anywhere? I'm looking at you, Clippy....