How to find duplicate values in Microsoft Excel

Microsoft Excel can recognize data for what it is; currency, numbers, string values, dates, and more. You can apply different formulas based on what a value is recognized as. Since Excel can recognize values as a certain type, it stands to reason that it can also tell when a value is repeating in a given data set. If you need to, you can find duplicate values in Microsoft Excel, and delete them. The highlight and delete functions are separate so if you only need to find duplicate values, but not delete them, you can do that as well.

Find duplicate values

Open the Excel file that you want to scan for duplicates. You can scan an entire sheet, or a few select cells. Select the rows and columns that you want to find duplicate values in.

On the Home tab, go to Conditional Formatting>Highlight Cells Rules>Duplicate Values.

This will highlight all duplicate values in the selected rows and columns, and it will also open a little window that lets you choose what color the duplicate values are highlighted in. You can choose one of the default color schemes or you can create your own scheme by selecting the Custom Format option from the ‘Value with’ dropdown. You can set the cell font color, fill color, and border color and style when you create the custom format.

Delete Duplicate Values

If you decide the duplicate values need to go, you can manually delete them, or you can have Excel purge them for you.

Select the row or column that you want to remove duplicates for. Unfortunately, you can only delete duplicates from one row or one column. Go to the Data tab and click the Remove Duplicates button.

If you select multiple columns, or multiple rows, Excel won’t find any duplicates. This is because when it comes to deleting them, it looks at values as a whole data set. A value may be repeating itself in a cell but the values in the row above/below it, or in the column on the left/right side might have a different value making it unique.

To understand this, consider that you have a table with the names and birth months of fifty people in it. It goes without saying that you will have duplicates in the birth month field however, the name accompanying the birth month is what makes it a unique data point which is why Excel will not delete it.