background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for September 2024 for September 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for September 2024 is Open
Wall Street Prep

Highlight Duplicate Values

Step-by-Step Guide to Understanding How to Highlight Duplicate Values in Excel

Last Updated December 6, 2023

Learn Online Now

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.

Conditional Formatting Box

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.

Duplicate Values

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.

Duplicate Values Formatting Box

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”).

Remove Duplicate Values

Highlight Duplicate Values Keyboard Shortcut

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

Highlight Duplicate Values Shortcut = ALT H L H D

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.

=COUNTIF(range, criteria)>1
  • 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.

Edit Formatting Rule

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.

dl

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

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.

Date Invoice No.
11/30/2022 AR1D456-000X
11/30/2022 KD3S782-000X
11/30/2022 VB2Q920-000X
11/30/2022 AJ2T464-000X
11/30/2022 BP1G218-000X
11/30/2022 MI6B325-000X
11/30/2022 KE9Q460-000X
11/30/2022 ED5P631-000X
11/30/2022 AU9Z127-000X
11/30/2022 TD4N457-000X
11/30/2022 OB1J264-000X
11/30/2022 GL7M134-000X
11/30/2022 FA6W348-000X
11/30/2022 AD2E626-000X
11/30/2022 GW1U951-000X
11/30/2022 TR7M524-000X
11/30/2022 PE7J125-000X
11/30/2022 OB1J264-000X
11/30/2022 ZD1B982-000X
11/30/2022 AQ3Z327-000X

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.

How to Highlight Duplicate Values in Excel

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.

=COUNTIF($C$5:$C$24,C5)>1

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

Highlight Duplicate Values (COUNTIF Function)

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More
Comments
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.