EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Excel Highlight Duplicates
 

Excel Highlight Duplicates

Madhuri Thakur
Article byMadhuri Thakur

Updated August 19, 2023

Highlight Duplicates in Excel

 

 

Highlight Duplicates in Excel

We can highlight the duplicate values in the selected dataset, whether a column or row of a table, from the Highlight Cells Rule, available in Conditional Formatting under the Home menu tab. To highlight the duplicates, select the data from where we need to highlight the duplicates, then select the Duplicate Values option, which is there under Conditional Formatting. From the box of Duplicate Values, choose Duplicate with the type of color formatting we want. Mainly Red text is selected by default to highlight duplicates. In this article, we will learn about Highlight Duplicates in Excel

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

There are various ways to find duplicate values in Excel. They are:

  • Conditional Formatting – Using Duplicate Values Rule
  • Conditional Formatting – Using Excel Function or Custom Formula (COUNTIF)

How to Highlight Duplicate Values in Excel?

Highlighting Duplicate Values in Excel is very simple and easy. Let’s understand how to find and highlight duplicate values in Excel using two methods.

You can download this Highlight Duplicates Excel Template here – Highlight Duplicates Excel Template

Conditional Formatting – Duplicate Values Rule

Here we will find the duplicate values in Excel using the conditional formatting feature and highlight those values. Let’s take an example to understand this process.

Example #1

We have given the below dataset.

Example 1

To highlight the duplicate values in the above dataset, follow the below steps:

  • Select the entire dataset.
  • Go to the HOME tab.
  • Click on the Conditional Formatting option under the Styles section, as shown in the below screenshot.

Click on Conditional Formatting

  • It will open a drop-down list of formatting options, as shown below.

Drop down list

  • Click on Highlight Cells Rules here, and it will again display a list of rules here. Choose the Duplicate Values option here.

Highlight Cells Rules

  • It will open a dialog box of Duplicate Values, as shown in the below screenshot.

Excel Highlight Duplicates Example 1-5

  • Select the color from the color palette to highlight the cells.

Excel Highlight Duplicates Example 1-6

  • It will highlight all the duplicate values in the given data set. The result is shown below.

Result of Example 1

With the highlighted duplicate values, we can take action accordingly.

In the upper section, we highlighted the cells with conditional formatting inbuilt features. We can also do this method by using an Excel function.

Conditional Formatting – Using Excel Function or Custom Formula (COUNTIF)

We will use here COUNTIF function. Let’s take an example to understand this method.

Example #2

Let’s again take the same dataset values to find the duplicate values in Excel.

Example 2

For highlighting the duplicate values here, we will use the COUNTIF function that returns TRUE if a value appears more than once in the list.

The COUNTIF function we will use like shown below:

=COUNTIF(Cell range, Starting cell address)>1

Follow the below steps to do this.

  • Select the whole dataset.
  • Go to the HOME tab and click on the Conditional Formatting option.

Excel Highlight Duplicates Example 2-2

  • It will open a drop-down list of formatting options, as shown below.

Excel Highlight Duplicates Example 2-3

  • Click on the New Rule option here. Refer to the below screenshot.

Excel Highlight Duplicates Example 2-4

  • It will open a dialog box for creating a new custom rule, as shown below.

Excel Highlight Duplicates Example 2-5

  • Select the last option, “Use a formula determining which cells to format,” under the Select a Rule Type section.

Excel Highlight Duplicates Example 2-6

  • It will display a formula window, as shown below.

Excel Highlight Duplicates Example 2-7

  • Enter the formula as =countif($A$3:$F$12,A3)>1, then click on the Format tab.

Excel Highlight Duplicates Example 2-8

  • Choose the Fill color from the color palette to highlight the cells, then click OK.

Excel Highlight Duplicates Example 2-9

  • This will highlight all the cells having duplicate values in the dataset. The result is shown below:

Result of Example 2

Things to Remember About Excel Highlight Duplicate Values

  • Finding and highlighting duplicate values in Excel often comes into use while managing attendance sheets, address directories, or other related documents.
  • After highlighting duplicate values, if you delete those records, be extra cautious about impacting your entire dataset.

Recommended Articles

This has been a guide to Highlight Duplicates in Excel. Here we discuss how to highlight duplicate values in Excel using practical examples and a downloadable Excel template. You can also go through our other suggested articles –

  1. Goal Seek in Excel
  2. Tables in Excel
  3. Excel Combo Box
  4. Scrollbar in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Highlight Duplicates Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Highlight Duplicates Excel Template

EDUCBA

डाउनलोड Highlight Duplicates Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW