Excel is one of the most popular spreadsheet management programs in the world and part of the famed Microsoft Office suite of productivity apps. However, as easy to use as Excel is, when dealing with bigger spreadsheets, things can get a lot more complicated.
In this article, we’re talking about how you can highlight and remove duplicates in Excel.
Highlighting duplicates in Excel
Highlighting duplicates in Excel is as easy as you’d expect. All you have to do is select the corresponding values in the sheet and apply conditional formatting to highlight the duplicate elements. Here’s how.
Step 1: Open your Excel spreadsheet and select the table with duplicate values. Make sure to cover the entire section where you want to remove duplicates.
Step 2: Once the table has been selected, click Conditional Formatting, followed by Highlight Cells Rules and then Duplicate Values.
Step 3: A dialogue window will show up asking whether you want to highlight duplicates or unique entries in the selection as well as what formatting you want to apply to the selected items. Chose your preferences and click OK.
After this, all the duplicate values in your selection will be highlighted in the colour of your choice. Alternatively, if you want to highlight duplicates without first references, create a new rule in the Conditional Formatting window as follows
=COUNTIF($A$2:$A2,$A2)>1In the aforementioned rule, make sure to change A2 with the topmost cell of your selected range and 1 with the number of occurrences you want to ignore.
Also read: What does #num mean in Excel?
Deleting duplicate values in Excel
If you want to move further and delete duplicate values from your selection, Excel offers an in-built tool to get the job done. All you have to do is select your data and head over to the Data tab. Then click the Remove Duplicates option.
Excel will ask you what columns you want to consider for deletion and whether or not your data has headers. Once you’ve selected the appropriate options, click OK to remove any duplicates.
Also read: How to fix Excel error: Object variable or with block not set?
