347 647 9001+1 714 797 8196Request a Call
Call Me

Finding and Removing Duplicates in Excel

March 25, 2015
, , , , , , ,

Did you ever come across a situation wherein, you see a lot of copied data in your spreadsheet and you don’t even remember copying it. Well it happens with all of us at some point of time and finding and removing such copied or duplicate data will take up a lot of time. So instead use the following tricks for finding and removing duplicates.

Finding the Duplicates in Excel

1.  For finding the duplicates we shall be using the conditional formatting function. In our example below, we want to find if any name has been repeated. So first we shall select the Name column (A1:A15).

How to find duplicates in Excel

2.  Click on the conditional formatting option available at the right hand side of the home tab. Alternatively, you can also use shortcut key: Alt + H + L.

conditional formatting option in excel

3.  When you click on the dropdown of conditional formatting, you shall receive various options. Select the first option ‘Highlight Cells Rules’ and the sub option as ‘Duplicate Values.’

finding the duplicate values in excel

4.  You shall see a duplicate values dialog box, wherein you can select your formatting option. In our case, we select ‘Light Red Fill with Dark Red Text’.

formatting duplicate values in excel

5.  Click on OK and you shall see that the duplicate names have been highlighted in red color.

duplicate values in excel

Removing the duplicates in Excel

1.  We shall use the same example as above for learning removal of duplicates. To remove the duplicates, select a random cell and select the option of Remove Duplicates from the data tab in the ribbon. You can also use the shortcut ALT + A + M

Remove duplicate option in Excel

2.  After clicking on remove duplicates a dialog box shall appear of remove duplicates. It will automatically have your column headers, select the columns of which you want to remove the duplicate values. In our case we shall select all which means if the data in all three columns is duplicated then the rows shall be deleted. ( The blue ones won’t be deleted since the age is different)

remove duplicate dialog box

3.  After pressing OK, all the duplicate values are removed, a pop up shall appear informing you about the duplicates removed and the unique values that remain. As mentioned above JKL remains since the age mentioned is different

removing the duplicates in excel

4.  What if you want delete all values which have same name and gender irrespective of their age?? Well simple, in the 2nd step just unmark the age checkbox.

selecting criteria for removing duplicates

5.  After clicking on OK you will see that even the row with JKL as name has been deleted.

removing duplicates in excel

If you have any queries or suggestions then do mention it in the comments box below and we shall get back to you at the earliest.


About the Author

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 73133