WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Highlight Duplicates in Excel

Highlight Duplicates in Excel

How to Highlight Duplicate Values in Excel?

Below are the steps to find or highlight duplicate values in Excel.

  • Step 1: Select the range of list that contains duplicate values. (Select only the data range, not the entire worksheet)

Highlight Duplicate values Example 1-1

  • Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Highlight Duplicate values Example 1-2

The excel shortcut keys to highlight duplicate excel values is:

Shortcut key to highlight duplicate values

  • Step 3: Once you click on Duplicate Values, it will open up the below dialogue box. Here we can select the type of formatting in excel we want. We can highlight both the cell and the text with three different colors, we can only fill the cell, we can highlight only the text values, or else we can highlight the duplicate cells borders.

 Highlight Duplicate values Example 1-3

  • Step 4: Once the formatting type is selected, it will highlight all the duplicate excel values in the range. Here I have selected Light Red Fill with Dark Red Text.

Duplicate values

Highlight Duplicate values Example 1-5

Highlight Duplicate Values in the Entire Worksheet

We can not only highlight the duplicate excel values in a single column; rather, we can do this task across the entire worksheet as well.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

I have data in the first two columns. I want to give conditional formatting so that in the future if I enter any kind of duplicate values, it should highlight the duplicate value for me.

Hightight Duplicate values Example 2

  • Step 1: Select the entire invoice number column. The reason we need to select the entire because here I am creating a validation so that if any of the duplicate entries are made in the future, it should automatically notify me the duplicate value is made.

 Hightight Duplicate values Example 2-1

  • Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Hightight Duplicate values Example 2-2

  • Step 3: Select the color you want to highlight. Here I have selected Green to fill with Dark Text.

Duplicate values

  • Step 4: Here, I have already found four duplicate values.

Hightight Duplicate values Example 2-4

  • Step 5: Now, try to enter any of the duplicate invoice numbers it will find and highlight the duplicate excel entry.

Remove Duplicate Values in Excel

Finding and Highlighting the duplicate values is definitely a needful skill at this point in time, and you learned that in the previous example. In this section of the article, I will show you how to remove those duplicates from the selection.

  • Step 1: Select the duplicate data range.
  • Step 2: Go to Data > Remove Duplicates.

Remove Duplicates 1

The shortcut key to Remove Duplicates in Excel is:

Shortcut key to remove duplicate

  • Step 3: Once you have selected the Remove Duplicate Values option, it will open up the below dialogue box. Since I have only one column, it is showing that single column header here.

Note: Make sure My data has headers checkbox is ticked.

Remove Duplicate

  • Step 4: Click on the Ok button. It will instantly remove all the duplicate values from the selection. It will also show you the prompt on how many duplicates are removed and how many unique values are remaining.

Microsoft Excel

Look at the above image; it has given a dialogue box stating 3 duplicate values found and removed; 16 unique values remain.

Things to Remember

  • We can select different formatting styles under the custom formatting option.
  • While removing the duplicates, it is very important to select the correct column header; otherwise, there are chances our data goes for a toss.
  • Rely on conditional formatting for highlighting future duplicates as well.
  • Finding and Highlighting duplicate excel values makes the data more accurate.
  • There are many other cool options residing under conditional formatting.

Recommended Articles

This has been a guide to Find and Highlight Duplicates in Excel. Here we discuss how to find and highlight duplicate values in Excel using the Conditional Formatting Method along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Excel Borders
  • Highlight Every Other Excel Row
  • Removing Duplicates in Excel
  • Advanced Filter in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download Highlight Duplicate Values Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More