Video:Excel Lists- Remove Duplicates

SHARE ON                

Transcript:Excel Lists- Remove Duplicates

Hi, I'm Don Schechter for About.com Computing.

Today, I'll teach you how to find duplicates in a long list of information in MS Excel. This is a good piece of knowledge to have when organizing a large amount of information.

Choose Excel Spreadsheet and List

First open Excel, then Go to File, Open and choose your document. Then select the entire column of information you want to test to see if there are duplicates.

Conditional Formatting to Remove Duplicates

I want to see if anyone has the same last name. Go to Format, and click on Conditional Formatting. A box will appear. Where it says Cell Value is, change to Formula is using the drop-down menu. We will be using this formula to test for duplicates. Be sure to replace the range you want to test. I am testing just the B column from B3 to B13. The cell after the comma is the cell that you are comparing all the others to. (type 9=COUNTIF($B$3:$B$13,B3)>1)

Formatting your Excel Duplicates

Now click the Format button to bring up the Format Cells box. Select the Patterns tab and choose a color you want to highlight all the duplicates. Click OK twice to return to your worksheet. If the range contains any duplicates they should be flagged by the background color.

Generalizing Conditional Formatting in Excel

You can adapt this technique by changing the ranges used in the countif function. The first part, (before the semicolon) should be where you are starting the range, after the semicolon should be where the range ends. Make sure you use dollar signs to signify an absolute reference. The part after the comma should be the cell you are using to compare to all the others, usually where your range begins.

Thanks for watching. To learn more, visit us on the Web at Computing.About.com.