Video:Excel Lists- Remove Duplicateswith Don Schechter
Learn how to manage your Microsoft Excel Spreadsheets more efficiently by automatically removing duplicate data from long lists.See Transcript
Transcript:Excel Lists- Remove DuplicatesHi, 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 ListFirst 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 DuplicatesI 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 DuplicatesNow 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 ExcelYou 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.
About videos are made available on an "as is" basis, subject to the User Agreement.