Dealing With Duplicate Records in Microsoft Excel




Dealing With Duplicate Records in Microsoft Excel

It's obvious that our sample file has duplicate records. There's many ways to get rid of them or highlight them.

Finding With a Formula

Use this method if you only need to get rid of duplicates once in a while.

  1. We insert a new Column A by clicking on A and hitting InsertàColumn.

  2. We give the row a heading. If we don't, and we sort the data, we might discombobulate it!

  3. We type a formula into A2 that will find duplicates for us. This formula only looks for duplicate values in Column B.

If you need to look for duplicates in more than one column, you can use a formula like this one instead.

  1. We then copy the formula by double-clicking the fill handle. With the data still selected as a result of copying the formula, we then copy the cells (Ctrl+C or EditàCopy), and then we choose EditàPaste Special, Values, OK. We do this to remove the underlying formula.

  1. We now sort descending by Column A to bring all the Dupes to the top.

  1. Select all the rows that have "Dupe" in Column A, and delete them.

  2. Delete Column A.

Finding With Conditional Formatting

Perhaps you only want to find the duplicate values so you can deal with each manually. For this you can use Conditional formatting. Just select the cells you want highlighted if there are duplicates. From the menu, choose FormatàConditional formatting. Choose the options as shown below, and type the formula in as shown, being sure to include the entire range of your cells.

Finding or Highlighting Using VBA

If you need to deal with duplicates often, you'll want to use VBA. debuted on May 26, 2005. provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2014 by MrExcel Consulting | All rights reserved

More To Explore