Intermediate Excel Training

Hello Louisa,

Hope you enjoyed your Microsoft Excel courses with Best STL.

Thank you for your question regarding how to find duplicate entries in Excel 2010.

This can be a fairly simple exercise or more complicated depending on how many columns are involved in your search.

If you are only looking at duplicate entries in one column, simply select all the data in that column and from the Home ribbon look at the Styles group and click on the Conditional Formatting button. From the Highlight Cell Rules option select Duplicate Values and click ok (accepting the default colour as red).

Add the Autofilter to your data and filter that column by colour and you should be left with all the duplicate entries. Sorting it after filtering will also help with your analysis of the duplicates.

If you need to analyse the duplicates of several columns you must create a concatenation formula joining each cell of each column together. Do this in a blank column and make sure that this new column is NOT adjacent to your data columns (leave one column gap). The formula would look something like this:

=CONCATENATE(B2&F2&G2&H2&M2)

Assuming that columns B, F, G, H & M were being tested for duplicates. After creating the formula I would copy all these cells and then paste over values to remove the formulas. Now add a heading above the new data and add autofilter.

Now all you have to do is select the new data and find the duplicate values using Conditional Formatting as described above. Finally filter by colour and you should have a list of your duplicates which you can sort A-Z to make the analysis easier.

Louisa, give this a try!


I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer