Excel has special options to find duplicate cells. Here are the steps to find and highlight duplicates in Excel program.
When dealing with a lot of data, it is natural to have a few duplicate cells in specific columns or rows in an Excel spreadsheet. This is especially possible when you are dealing with data generated from multiple sources or data that is incredibly similar. For example, recently I had to go through an Excel sheet created and managed by multiple users with data generated from multiple sources. Since the data is created and managed by multiple users, there are a lot of duplicates due to miscommunication and editing choices.
Thankfully, the Excel program has multiple options and ways to find duplicate cells in rows or columns. Once you found them, you can either highlight duplicates in Excel for future reference or delete them entirely.
If you too have a need to find and highlight duplicates or delete duplicates in the Excel spreadsheet, follow the below method and you will be done in no time. Without further delay, let me show you the steps on how to do it.
Note: I’m showing this in Excel 2019 but the same should work in older or newer version on Excel. Importantly, make sure to back the Excel sheet before making changes.
Find and highlight duplicates in Excel
To find duplicates in Excel, follow the steps given below.
- Open the Excel spreadsheet.
- Select the rows or columns you wish to analyze.
- Click on the “Home” tab at the top.
- Select “Conditional formatting.”
- Choose “Highlighting cell rules.”
- Select “Duplicate Values.”
- Select “Duplicate” from the first dropdown menu.
- Click “Ok” in the Duplicate Values window.
- Duplicate cells will be highlighted in Excel.
First things first, open the Excel spreadsheet. You can do that by double-clicking on the spreadsheet file or opening the Excel program and selecting “File -> Open.” In my case, I opened a dummy sheet with some duplicate values.
After opening the Excel sheet, select the rows or columns from which you want to highlight duplicate cells. In my case, I’m selecting two entire columns.
After highlighting, click on the “Home” tab at the top. Now, select the “Conditional formatting -> Highlighting cell rules -> Duplicate Values” option.
Select “Duplicates” from the first dropdown menu. Using the second dropdown menu, you can set the highlighting style. In my case, I’m letting the default styling be. Click on the “Ok” button.
As soon as you click the ok button, the Conditional Formatting feature will automatically highlight all the duplicate cells in the Excel sheet.
What’s good about this method is that if you create new cells with duplicate data, the Conditional Formatting will automatically highlight them. Of course, this is assuming that the new cells are in the same zone that you selected earlier.
Stop highlighting duplicate cells
If you no longer want to highlight duplicate cells in Excel, all you have to do is remove the conditional formatting. To do that, select the columns or rows and then select the “Home -> Conditional formatting -> Clear rules -> Clear rules from selected cells” option.
That is all. It is that simple to find and highlight duplicate cells in an Excel spreadsheet. Also, did you know that you can easily delete all blank rows in Excel at once? Do check it out.
I hope that helps. If you are stuck or need some help, comment below and I will try to help as much as possible.
