As Excel workbooks get bigger, the harder they are to manage. Making sure data is accurate and up to date is one thing, but how about managing duplicates? Having duplicate information in a workbook or sheet can cause multiple issues. From the obvious doubling up on figures and information giving incorrect reflections in tables, totals, and charts to just not having an accurate spreadsheet of information, it can be a bit of a headache.

How to find duplicates in Excel

Luckily, Microsoft Excel has some handy tools to resolve these issues that allow you to quickly pick out duplicate information and delete it. We’re going to quickly walk you through both tasks so those sheets and workbooks can’t get nice and organized again as quickly as possible.

First, we would always recommend making a backup of your worksheet just in case deleting duplicate information messes up any other formulas and for troubleshooting. It will just make your life easier if things screw up. Let’s get into it.

Finding Duplicates

First, select all cells affected by duplicates by left-clicking and dragging. This can also be done by holding the CTRL key and clicking each row or column depending on the size of your sheet. 

Next, go to the Home tab in the top Excel ribbon and go to Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the next window, make sure Duplicate is selected on the left drop-down menu and select the highlight option that suits you. We went with dark yellow as it didn’t clash with our green headers. This will then highlight all duplicates, but please note, this includes the original cells too.

Remove Duplicates

Whilst finding duplicates highlights the original and duplicated cells, this method of removing duplicates does just that, so don’t worry about losing necessary information. Once again, highlight all applicable cells, go to the Data tab on the Excel ribbon and go to ‘Remove Duplicates’.

We wouldn’t recommend highlighting headers as well, but there is a tick box to select that ignores them if needed. Then, simply select the columns you need to scan for duplicates and hit OK. Well done, you just removed all of your duplicates.

Thanks for choosing PC Guide for learning how to find duplicates in Excel, and how to remove them too. For more help with Excel and Microsoft Office, make sure to bookmark and check out our Office hub.

About the Author

Christian Wait

Christian 'Reggie' Waits' is a contributor to PC Guide, having written a wide array of how to and buying guide content.