How to Find Duplicates in Excel Quickly

With hundreds of rows in today’s spreadsheets, it is virtually impossible for anyone to spot a duplicate value, or duplicate row in Excel.

With Microsoft Excel 2007, and further enhancements in Excel 2010 and Excel 2013, it is now extremely easy to spot duplicates, and there are multiple ways to find them, or eliminate them.

1. Spot a duplicate value, and get it to change its color automatically

In this simple method, we make use of Conditional Formatting, available on the Home Tab.

  • Simply select the data range where you want to spot any duplicate values, and then click on Conditional Formatting.A drop down menu of choices appears. Select the first choice – Highlight Cell Rules. Then select the Last option -Duplicate Values.The short cut key combination is Alt +H L H D.highlight_cells_duplicate_valueSelect your favorite color to highlight the duplicate values.

    duplicate_values_popup

    Both the duplicate values are now highlighted.

    finding duplicate values in Microsoft Excel

There you are. A quick and nifty way to find the duplicate values in any column, quickly.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn theĀ  Pivot Table techniques in our
Pivot Table Masterclass Training
!


We will talk about another nifty way to eliminate duplicates, once for all, in the next article.

You may find these resources useful:

Cheers,
Vinai Prakash

Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts Excel workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!