Delete Duplicates in Microsoft Excel

Sort your sheet by the column in which you're trying to find duplicates. For our example formula, our data is in column A.

In a spare column (we're using column B), use the following formula:


Copy the formula down, using the fill handle.

Click anywhere in your data, and hit DataàFilteràAutofilter.

From the Autofilter dropdown in your spare column (we're using column B), choose 1. You'll see only those rows that returned a 1 as a result of the formula above, which means they're all duplicates.

Select all the rows you see, and delete them. Turn off the Autofilter by going to DataàFilter and uncheck Autofilter. Delete the column in which you typed your formula. 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