Excel Lists- Remove Duplicates
with
Don Schechter
Learn how to manage your Microsoft Excel Spreadsheets more efficiently by automatically removing duplicate data from long lists.
Related Articles
SUMIF - Excel 2007 SUMIF - How to Use Excel SUMIFExcel Formulas - Using What If Questions in Excel Formulas - Excel What If QuestionsSpreadsheet Terms - Glossary - Excel - Range DefinitionCOUNTIF - Excel 2007 COUNTIF - Excel COUNTIF ExampleExcel Cell Reference - Spreadsheet Terms - Excel Cell Reference Definition
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.