What is Highlight Duplicate Values in Excel?
A duplicate value occurs more than once in a dataset. It is often found while working with large databases in excel. It is essential to find and highlight the duplicate values in excel because the user may or may not want to retain them.
For example, Jennifer creates a list of expenditures incurred on fruits and vegetables for a particular month. Since the leftover amount varies from the actual in-hand balance, she examines the list again.
Jennifer notices that $18 spent on apples has been mistakenly entered twice. Hence, it is essential to identify duplicates in a series of data values. This helps deal with the duplicates accordingly.
The purpose of highlighting duplicates in excel is to make the data understandable and accurate. Moreover, it helps in differentiating the unique values from the duplicated ones.
Prior to deleting the duplicates permanently, it is recommended to keep a copy of the original data. This allows one to return to the source data, if required.
How to Highlight Duplicate Values in Excel?
We can highlight the duplicate values in a single excel column as well as in an entire worksheet. The difference between the former and the latter is in the selection of cells. Hence, one must be careful while selecting cells in the first step.
Let us consider a few examples.
Example #1–Highlight Current Duplicates in the Selected Excel Range
The following table shows the list of names of a few people. We want to find and highlight the current duplicates with the help of conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more.
The steps to find and highlight the duplicates in excel by using conditional formatting are listed as follows:
- Select the data range that consists of the duplicate values.
Note: In this case, ensure that only the particular range containing duplicates is selected and not the entire worksheet.
- Click the conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more drop-down under the “styles” group of the Home tab. In “highlight cells rules,” select duplicate values, as shown in the following image.
Alternatively, press the shortcutShortcutAn Excel shortcut is a technique of performing a manual task in a quicker way.read more keys “Alt+H+L+H+D” one by one.
- The “duplicate values” dialog box opens, as shown in the following image. One can choose the required type of formattingType Of FormattingFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more in the “values with” box.
It is possible to highlight either the cell, the text or both with different colors. One can also highlight the borders of the cells containing duplicates.
- Select “light red fill with dark red text” and click “Ok.” The values occurring more than once in the selected range (column A) are highlighted, as shown in the following image.
Note: The conditional formatting feature highlights the duplicate values including their first occurrence.
Example #2–Highlight Future Duplicates in the Selected Range
The succeeding table consists of the invoice numbers and the corresponding amounts. On a selected range, we want to perform both the following tasks:
- Highlight the current duplicate values
- Highlight the duplicate values to be entered in the future
Use the conditional formatting feature of Excel.
The steps to highlight the current and the future duplicate values by using conditional formatting are listed as follows:
Step 1: Select the column “invoice number” (column A). With this selection, it will be possible to highlight the new duplicate value entered in the existing list (column A) in future.
Step 2: In the Home tab, click the conditional formatting drop-down under the “styles” group. Select “duplicate values” from “highlight cells rules,” as shown in the following image.
Step 3: Select the color in which the duplicate cell values are to be highlighted. We select “green fill with dark green text.” Click “Ok.”
Step 4: Four duplicate values are highlighted in green. These are the ones that are occurring more than once.
Step 5: Enter any of the duplicate invoice numbers in row 22 of column A. The new duplicate entry of column A is highlighted automatically, as shown in the succeeding image.
Note 1: Alternatively, select the entire worksheet (in step 1) and perform the steps 2 and 3. The results will be the same as those obtained in step 4.
Note 2: There is a difference between selecting a specific column and the worksheet. In the former, the duplicate value entered in a new cell of the same column will be highlighted. In contrast, if a worksheet is selected, the duplicate value entered in any cell of this worksheet will be highlighted.
Example #3–Remove Duplicates From the Selected Range
Working on the data of example #1, we want to remove the duplicates from the selected range.
The steps to remove duplicates from a selected range are listed as follows:
Step 1: Select the data range containing duplicates.
Step 2: Click “remove duplicates” from the “data tools” group of the Data tab.
Alternatively, press the shortcut keys “Alt+A+M” one by one.
Step 3: The “remove duplicatesRemove DuplicatesTo remove duplicates from the excel column, the user can adopt any of the three well-known methods: Using data tools group, Using the advanced filter in excel, Conditional formatting in excel.read more” dialog box appears, as shown in the following image. The box under “columns” shows the header of the selected column.
Select the checkbox “my data has headers.” Click “Ok.”
Step 4: The duplicate values are removed from the selected column (column A). A message appears stating the number of duplicates deleted and the number of unique values retained.
Click “Ok” to see the results.
Step 5: The column A now shows only the unique data values. Hence, the duplicate values have been removed.
The Cautions Governing Duplicate Values
The cautions to be observed while dealing with duplicates are listed as follows:
- Make sure to select the correct range in which duplicates are to be highlighted.
- Ensure that you select the “custom format” option in the “duplicate values” dialog box to choose the formatting style of the duplicate cells.
- Remember to select the correct column header while removing the duplicates.
Frequently Asked Questions
A duplicate value occurs more than once in a dataset. Duplicates are highlighted in order to make the data understandable. Moreover, highlighting allows the user to review every duplicate value and decide whether to retain or remove it.
The steps to find and highlight duplicates in excel are listed as follows:
a. Select the range in which duplicates are to be found and highlighted.
b. Click the “conditional formatting” drop-down from the Home tab of Excel. Select “duplicate values” from “highlight cells rules.”
c. The duplicate values” box opens. Select the required formatting from the various options available in the “values with” box. Click “Ok.”
The duplicates within the selected range are found and highlighted with the color selected in step c.
Let us highlight duplicates in the following columns:
• Column A consists of rose, lily, sunflower, lily, and lotus in the range A1:A5.
• Column B consists of tulip, rose, orchid, lotus, and rose in the range B1:B5.
• Column C consists of sunflower, daffodil, marigold, tulip, and hibiscus in the range C1:C5.
The steps to highlight duplicates in the excel columns A, B, and C are listed as follows:
a. Select the range A1:C5.
b. Click the “conditional formatting” drop-down from the Home tab. Select “manage rules.”
c. The “conditional formatting rules manager” dialog box opens. Click “new rule.”
d. Under “select a rule type,” click “format only unique or duplicate values.” Under “format all,” select “duplicate.”
e. Click “format” and select the required color in the “fill” tab. Click “Ok.”
f. Click “Ok” again in the “new formatting rule” window.
g. The rule applied to the current selection appears in the “conditional formatting rules manager” dialog box. Click “Ok.”
All the flower names occurring more than once in the selected range (A1:C5) are highlighted. The unique flower names are orchid, daffodil, marigold, and hibiscus. The remaining flower names are duplicate values.
Note: The mentioned procedure highlights the duplicate values including their first occurrence.
The steps for highlighting duplicates in multiple columns with the COUNTIF formula are listed as follows:
a. Select the entire range in which duplicates are to be found.
b. Click the “conditional formatting” drop-down from the Home tab. Select “new rule.”
c. The “new formatting rule” window opens. Choose the option “use a formula to determine which cells to format” under “select a rule type.”
d. Under “edit the rule description,” enter the following formula.
“=COUNTIF(range,top_cell)>1”
The “range” is the selected range of step a. The “top_cell” is the first, leftmost cell of the current selection. For instance, if the “range” is A1:C5, the formula becomes “=COUNTIF($A$1:$C$5,A1)>1.”
e. Click “format” and select the desired color (in the “fill” tab) for highlighting duplicates. Click “Ok.”
f. Click “Ok” again in the “new formatting rule” window.
The duplicates in the selected range are highlighted.
Note: In the formula entered in step d, the “range” ($A$1:$C$5) is written using absolute references. The “top_cell” (A1) is written using relative reference.
Recommended Articles
This has been a guide to guide to Highlight Duplicates in Excel. Here we discuss how to find and highlight duplicate values in excel with step by step examples You may learn more about Excel from the following articles-
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
59.9K subscribers