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.
![highlight duplicate values in excel](https://itoolab.com/wp-content/uploads/highlight-excel-duplicates-select-values.jpg)
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/hihglight-duplicates-conditional-formatting.jpg)
![highlight duplicate rows in excel](https://itoolab.com/wp-content/uploads/highlight-duplicate-value.jpg)
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-duplicates-click-ok.jpg)
![how to highlight duplicate values in excel](https://itoolab.com/wp-content/uploads/highlight-duplicates-result.jpg)
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.
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-duplicate-rows.jpg)
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.
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/hihglight-duplicates-conditional-formatting.jpg)
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-duplicates-new-rule.jpg)
![highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-dupliactes-select-a-rule-type.jpg)
![highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-duplicates-new-formatting-rule.jpg)
![highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-duplicates-click-ok-again.jpg)
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.
![how to highlight duplicates in columns](https://itoolab.com/wp-content/uploads/highlight-duplicates-in-columns.jpg)
Step 2: Click ‘Home’ from the ribbon, and then click ‘Conditional formatting’ in styles section.
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/hihglight-duplicates-conditional-formatting.jpg)
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-duplicates-new-rule.jpg)
![highlight duplicates in excel](https://itoolab.com/wp-content/uploads/highlight-duplicates-new-formatting-rule.jpg)
Step 6: Click ‘Ok’.
![how to highlight duplicates in columns](https://itoolab.com/wp-content/uploads/highlight-duplicates-in-columns-result.jpg)
How to Remove Duplicates in Excel
Remove Duplicates in Single Column
Step 1. click Data > Data Tools > Remove Duplicates.
![how to remove duplicates in excel](https://itoolab.com/wp-content/uploads/remove-duplicates-in-excel.jpg)
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).
![remove duplicates in excel](https://itoolab.com/wp-content/uploads/remove-duplicates-my-data-has-headers.jpg)
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.
![how to remove duplicates in excel](https://itoolab.com/wp-content/uploads/remove-duplicates-in-excel.jpg)
If your data has headers, make sure the ‘My data has headers’ option is checked.
Select all the columns except the Date column.
![remove excel duplicates](https://itoolab.com/wp-content/uploads/remove-duplicates-unselect-all.jpg)
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.
![how to remove duplicates in excel](https://itoolab.com/wp-content/uploads/remove-duplicates-in-excel.jpg)
If your data has headers, make sure the ‘My data has headers’ option is checked.
Select all the columns.
![remove microsoft excel duplicates](https://itoolab.com/wp-content/uploads/remove-excel-duplicates-select-all.jpg)
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’
![highlight duplicates in columns and rows](https://itoolab.com/wp-content/uploads/remove-excel-password.jpg)
![how to highlight duplicates in excel](https://itoolab.com/wp-content/uploads/import-excel-file-with-restriction-password.jpg)
![highlight duplicates in excel](https://itoolab.com/wp-content/uploads/remove-restriction-password-successfully.jpg)
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.