How to Highlight and Remove Duplicates in Excel (Step by Step Guide）

The first step to remove excel duplicates is to highlight them. Highlight duplicate is an in-built Excel software option in which you can use different fonts styles and colors to highlight duplicate values in excel. To know how to highlight adn remove duplicates in excel is considered to be a savior skill for managers, data scientists and analysts. When you know how to highlight and remove duplicate values in excel, it not only saves you from faltering in analysis but also saves you from the extremely time-consuming drill of finding duplicates in an excel sheet. This article in the forthcoming parts will help you learn different ways about how to highlight duplicates and remove them in excel in the easiest manner possible.

How to Highlight Duplicates in Microsoft Excel

How to Highlight and Remove Duplicate Values and Rows in Excel

It won’t be wrong to say that highlight and remove duplicates feature has made lives of all the excel users easy. Highlighting and removing duplicates has different ways with each helping in a certain scenario. In this particular part, we will learn it one by one.

Section 1. Highlight and Remove duplicates in excel Values

Duplicate values are individual values that appear repetitively in a column data set. The process to highlight duplicate values is pretty simple to learn and emulate. Before we start with procedure, first, let’s assume a data set as shown in Figure below, which have repeat values in column D. Now, let’s follow a rule-based process to highlight duplicate values in excel. The involved steps are as follows:

Step 1: Select values you want to format, such as D3:D16.

Step 2: Now, click ‘home’ from the ribbon above and then click ‘conditional formatting’ in the Styles section.

Step 3: Click ‘Highlight Cell Rules and a drop-down menu will show. Select ‘Duplicate Value’ from it.

Step 4: Now, a pop-up window will appear. Select ‘Duplicate’ from the options and in the ‘values with’ select ‘Light Red Fill with Dark Red Text’.

Step 5: Lastly, click ‘Ok’. As a result, the duplicate values in the particular column will show as highlighted.

Section 2. Highlight and Remove Duplicate in Excel in Same Rows

The above-mentioned method or rule can only be used for a data set of a particular column. If we wish to highlight duplicate by rows having more than one columns, we would need a new method. Luckily, we do a method that help us to highlight duplicate rows in excel. To help you learn, let’s suppose we have the following data set.

let’s highlight duplicate rows in excel. The involved steps are as follows:

Step 1: The first step relates to selection. So, select A1 to C10.

Step 2: Click ‘Home’ from the ribbon, and then click ‘Conditional formatting’ in styles section.

Step 3: A drop down menu will appear, click ‘Highlight Cell Rules’ and then click ‘new rule’.

Step 4: Now, a new window will appear, in the top pane, select ‘Use a formula to determine which cells to format’.

Step 5: Enter =COUNTIF ($C$1: $C$10, $C1)>1 in the bottom panel. Click ‘format’ button to choose color and font style for highlight duplicates.

Step 6: Click ‘Ok’.

As we can see, in the final result in above figure, duplicate excel rows have been highlighted. And you can use the same way to remove excel duplicates above.

How to Highlight Duplicates in Same Column in Different Colour

Many a time, we create repeat column entries by mistake. But, we can excel highlight duplicates in columns using a special excel formula. Conditional formatting, as well as SUMPRODUCT function, are used in a combined way to excel highlight duplicates in columns. Below is the step-by-step process to do this. Let’s have a look.

Firstly, let’s assume the data set below as sample.

Step 1: As the first step, we will make a wider sample collection. From the above mentioned sample figure, making a selection from A2 to D6, which includes all the entries in the sample.

Step 2: Click ‘Home’ from the ribbon, and then click ‘Conditional formatting’ in styles section.

Step 3: A drop down menu will appear, click ‘Highlight Cell Rules’ and then click ‘new rule’.

Step 4: Now, a new window will appear, in the top pane, select ‘Use a formula to determine which cells to format’.

Step 5: Now, apply this formula:=SUMPRODUCT(($A$2:$D$2=A$2)*($A$3:$D$3=A$3)*($A$4:$D$4=A$4)*($A$5:$D$5=A$5)>1. Put it in the Format values where this formula is true box. Click ‘format’ button to choose color and font style for highlight duplicates.

Step 6: Click ‘Ok’.

How to Remove Duplicates in Excel

Remove Duplicates in Single Column

Step 1. click Data > Data Tools > Remove Duplicates.

Excel will then select the entire set of data and open up the Remove Duplicates window.

Step 2. In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Make sure the column is selected (in this case there is only one column).

Step 3. Click OK.

This would remove all the duplicate values from the column, and you would have only the unique values.

Remove Duplicates in Multiple Columns

Step 1. click Data > Data Tools > Remove Duplicates.

Step 2. In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Select all the columns except the Date column.

Step 3. Click OK.

This would remove the 2 duplicate entries. This keeps the first occurrence and removes all the remaining duplicate occurrences.

Remove Duplicate Rows

Step 1. click Data > Data Tools > Remove Duplicates.

Step 2.In the Remove Duplicates dialog box:

If your data has headers, make sure the ‘My data has headers’ option is checked.

Select all the columns.

Step 3. Click OK.

Use the above-mentioned techniques to clean your data and get rid of duplicates.

Also See: How to Unlock Locked Duplicate Cells in Excel

The highlight duplicate function allows a user to easily locate repetition and take action by removing the anomalies. However, to avoid such occurrences, many use Excel password protection. Encryption is good for your safety. But the issue is that we often forget passwords to key excel files. Such a situation demands the removal of the Excel password, but Excel has no formal mechanism for it. So, to solve this, Passwiper for Excel has emerged as the savior for many people who have lost their passwords.

Here are the steps:

Step 1: Download and install PassWiper for Excel software on your Computer. Once downloaded, click ‘Remove Excel Password’

Step 2: Upload file for which ‘Remove Excel Password’ is required. On uploading, click ‘remove’ to start the process.

Step 3: On completion of scanning, PassWiper for Excel will remove VBA project forgotten password. Now, your excel file is unlocked. You can use it as you like to.

Summary

Highlighting duplicate values in excel is a brilliant feature, which saves a lot of valuable time of excel software users. This article helps you to learn as how to highlight duplicates in excel. Whether it be rows or column, practical methods to highlight duplicate values in excel has been shared in a step-by-step manner for easy understanding. Many people in fear of duplication password protect their important excel files but they forget the password themselves and fail to connect to their important files. Passwiper for Excel in such a situation provides an easy and fast method to remove excel password.