Remove duplicates command, which I presented in the previous article, allows you to quickly remove duplicate lines, but what should be done, if you only want to find duplicates in Excel, but not delete them? There are two Excel tools which are helpful in this matter: conditional formatting and advanced filter.
How to find duplicates with conditional formatting?
Conditional formatting is an option which is used to highlight specific cells or ranges of data based on their value. Cell’s format will be changed in the selected way when the condition specified by the Excel user is met. One of these conditions may be e.g. highlighting duplicate values.
Conditional formatting can be found on the Home tab of the Ribbon:
Select the data, which should be included in conditional formatting – in my example below I would like to find duplicates in column A so I need to select this column:
Then, select należy wybrać opcję Highlight Cells Rules -> Duplicate Values:
New window with Duplicate Values formatting will be opened:
In this window, you can choose if duplicate or unique values should be highlighted and choose how selected cells will be formatted:
After choosing appropriate options, click OK and you can see that duplicate values are highlighted:
After conditional formatting you can filter duplicates values by Filter by Color option. To do that:
- Select top row,
- Go to Data tab on the Ribbon and select Filter, or use keyboard shortcut Ctrl + Shift + L,
- Select Filter by Color, then filter with color and select the formatting used before:
How to find duplicates with advanced filter?
Another way to find duplicates in Excel is using Advanced Filter. You can find this command in Data tab:
After selecting data range and clicking Advanced command you will get new window opened, when you can choose detailed settings of advanced filtering:
- You can choose, if selected data range will be filtered in place or will be copied to another location within spreadsheet,
- Excel automatically selects list range to be filtered, however in this window you can change it manually – in my example I want to filter only data in column A, so my list range has to be changed to $A$1:$A$14,
- Select Only records only.
Click OK and after that, all duplicate values will be hidden.
Data before advanced filtering:
Data after advanced filtering: