Highlight Duplicate Values

How to Highlight Duplicate Values in Excel?

The quickest and most convenient method of identifying and highlighting duplicates in a data set is to utilize the conditional formatting feature in Excel.

Conditional formatting provides the ability to highlight duplicate entries, after which the user can either remove the duplicates or keep them as-is.

Duplicate values can often be the result of a system or error arising from manual entry, which in most cases would be subsequently removed.

On the other hand, the duplicate entries could be a normal occurrence (and thus might not need to be removed). For instance, a company might be wanting to identify recurring customers that have placed multiple orders in the past.

One side benefit of utilizing conditional formatting in Excel is that not only are duplicate values in the current range highlighted, but the conditional formatting can continuously check for duplicates as new entries roll-in, assuming the rule is set properly.

The process of highlighting the duplicate values in a selected range of data can be broken into four steps:

• Step 1 → Open the “Home” Tab
• Step 2 → Click on “Conditional Formatting” (Styles Group)
• Step 3 → Select “Highlight Cells Rules”
• Step 4 → Click on Duplicate Values…

Highlighting Duplicate Values with Conditional Formatting

The “Conditional Formatting” selection can be found on the Home tab in Excel, within the Styles group.

Within “Conditional Formatting”, the next step is to open the “Highlight Cell Rules” group, which displays a variety of rules that could be applied.

Near the bottom of the list, the “Duplicate Values…” selection is the one that we want to select, which should open the formatting style box in which duplicate entries will appear.

Upon clicking on “Duplicate Values…”, the following box should appear. You can either proceed with the default formatting setting (i.e. “Light Red Fill with Dark Red Text”) or customize the style.

If applicable to the task at hand, once the duplicate entries are highlighted, the “Remove Duplicates” option can then be selected in the “Data” tab (and “Data Tools” group”).

Highlight Duplicate Values Keyboard Shortcut

The following keyboard shortcut can be used to make the process above even more efficient.

Note that each key should be pressed separately, not simultaneously.

Highlighting Duplicate Values with Excel COUNTIF Function

An alternative method to highlighting duplicate values is to create a formula-based rule.

By clicking on “New Rule”, rather than “Duplicate Values…”, the option to create a new formatting rule appears.

Selecting “Use a formula to determine which cells to format” shows the option to enter a custom formula in the “Format values where this formula is true”.

The formula used to highlight the duplicate values is as follows.

• “range” → The selected range of cells in which the rule will find and highlight duplicates. In order for the conditional formatting to function correctly, the range must be anchored, i.e. an absolute reference (F4).
• “criteria” → The active cell can be any cell reference in the range. Unlike the range, the active cell should NOT be an absolute reference, as the formula is automatically copied across all cells in the chosen range.

If you want to highlight only the duplicates that appear more than two times, the “>1” can be replaced with “>2” – or if you want to highlight only values with an exact given number of duplicates, simply switch the “>” with “=”.

In comparison to the prior method, setting a custom rule tends to be more practical for more complicated tasks beyond highlighting simple duplicates.

For instance, the formula can be adjusted to count cells that meet a partial criterion, such as all cells that end in a specified string of text or digits.

Highlight Duplicate Values Calculator – Excel Model Template

We’ll now get started with our Excel tutorial. To access the spreadsheet used in our modeling exercise, fill out the form below.

First Name *

Email *

Submit

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Email provided

Your Download is Ready

Submitting...

Part 1. How to Highlight Duplicate Values Using Conditional Formatting

Suppose you’re reviewing the end-of-month invoices due to be paid out in the month of November 2022.

The task here is to confirm that there are no duplicates in the following data set before payments are issued.

After selecting our range of invoice data and clicking the shortcut to highlight duplicates, we can see there is one duplicate right away, i.e. the cells formatted in red fill and red font.

Invoice number “OB1J264-000X” was mistakenly entered twice, so one of the entries must be removed to avoid mistakenly paying the recipient twice.

Part 2. How to Find Duplicate Values Using COUNTIF Function

In the next section of our Excel tutorial, we’ll perform the same task, but with the COUNTIF function.

The following formula would be entered into the rule box to highlight the duplicate values.

Either approach works, as confirmed by how invoice number “OB1J264-000X” is highlighted here like before.