HalfofTheHalfofThe
  • Home
  • Technology
    • Reviews
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Google
    • Mobile
    • Samsung
    • Tesla
    • Twitter
  • News
    • Sports
    • Amazon
    • Apple
    • Business
    • Cars
    • Entertainment
    • Facebook
    • Mobile
    • Samsung
    • Science
    • Tesla
    • Twitter
  • How To
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Games
    • Google
    • Mobile
    • Samsung
    • Science
    • Tesla
    • Twitter
  • Business
  • Entertainment
  • Health
    • Pets
    • What
    • Why
  • Lifehacks
    • Explain
    • SmartHome
    • SocialMedia
  • More
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Google
    • Mobile
    • Netflix
    • Twitter
    • Samsung
    • Tesla
Search
Technology
  • How To
  • Explain
  • Mobile
  • Lifehacks
  • News
  • Reviews
  • SmartHome
  • Technology
Information
  • About Us
  • Contact Us
  • Cookie Policy
  • Privacy Policy
  • Terms and Conditions
All content
  • Home
  • Technology
    • Reviews
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Google
    • Mobile
    • Samsung
    • Tesla
    • Twitter
  • News
    • Sports
    • Amazon
    • Apple
    • Business
    • Cars
    • Entertainment
    • Facebook
    • Mobile
    • Samsung
    • Science
    • Tesla
    • Twitter
  • How To
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Games
    • Google
    • Mobile
    • Samsung
    • Science
    • Tesla
    • Twitter
  • Business
  • Entertainment
  • Health
    • Pets
    • What
    • Why
  • Lifehacks
    • Explain
    • SmartHome
    • SocialMedia
  • More
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Google
    • Mobile
    • Netflix
    • Twitter
    • Samsung
    • Tesla
  • About Us
  • Contact Us
  • Cookie Policy
  • Privacy Policy
  • Terms and Conditions
2022. All Rights Reserved by Halfofthe. “As an Amazon Associate we earn from qualifying purchases.”
Notification Show More
Latest News
How to Root Android?
How to Root Android?
Android How To
How to Set Up Voicemail on Android?
How to Set Up Voicemail on Android?
How To Android
Can I upload my own subtitles to Plex?
Can I upload my own subtitles to Plex?
How To Android
How to Retrieve Deleted Text Messages on Android?
How to Retrieve Deleted Text Messages on Android?
Android How To
How to Record a Phone Call on Android?
How to Record a Phone Call on Android?
Android How To
Aa
HalfofTheHalfofThe
Aa
  • How To
  • Explain
  • Mobile
  • Lifehacks
  • News
  • Reviews
  • SmartHome
  • Technology
Search
  • Home
  • Technology
    • Reviews
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Google
    • Mobile
    • Samsung
    • Tesla
    • Twitter
  • News
    • Sports
    • Amazon
    • Apple
    • Business
    • Cars
    • Entertainment
    • Facebook
    • Mobile
    • Samsung
    • Science
    • Tesla
    • Twitter
  • How To
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Games
    • Google
    • Mobile
    • Samsung
    • Science
    • Tesla
    • Twitter
  • Business
  • Entertainment
  • Health
    • Pets
    • What
    • Why
  • Lifehacks
    • Explain
    • SmartHome
    • SocialMedia
  • More
    • Amazon
    • Apple
    • Cars
    • Facebook
    • Google
    • Mobile
    • Netflix
    • Twitter
    • Samsung
    • Tesla
Follow US
  • About Us
  • Contact Us
  • Cookie Policy
  • Privacy Policy
  • Terms and Conditions
2022. All Rights Reserved by Halfofthe. “As an Amazon Associate we earn from qualifying purchases.”
HalfofThe > Blog > How To > How to highlight duplicate values in Excel?
How To

How to highlight duplicate values in Excel?

Bippin
Last updated: 2022/09/14 at 4:12 PM
By Bippin Published August 3, 2022
Share
4 Min Read
How to highlight duplicate values in Excel?

If you wish the duplicate values in your Excel spreadsheet to stand out, you can do so, and you have the option to choose between two conditional formatting rules.  

How to highlight duplicate values in Excel?
How to highlight duplicate values in Excel?

This article, How to highlight duplicate values in Excel, will show you two easy ways you can apply conditional formatting to duplicate values or the row that contains a duplicate value.  First, I will show you the easy built-in rule that formats duplicate values. Then, we need to apply a conditional format rule that highlights the entire record.  

How to highlight duplicate values in Excel?  

  • Select the values you want to format.  
  • Click the Home tab, then, click the Conditional Formatting dropdown, this is situated in the Styles group.  
  • From the dropdown, choose Highlight Cells Rules, then select Duplicate Values from the resulting submenu.  
  • Now, select a preset format from the dropdown to the right.  
  • After you click on OK, Excel will highlight the duplicate values.  

This built-in rule is easy to implement as well as adequate, however, it sometimes is not; then you might have to turn to a formulaic rule.  

How do you highlight rows in Excel?  

Sadly, you cannot use a built-in rule to highlight the entire row when the column contains a duplicate value. For that, you will have to have a formula that relies on a COUNTIFS() in the form  

COUNTIF(range,criteria)  

In the formula, the range identifies the entire data set (a record) and criteria specify the condition. That can be a cell reference, a value, or it could even an expression. To do so follow:  

  • Click on the data range, if you wish to highlight the entire row. If you use a Table, then Excel will update the range as you add as well as delete records.  
  • Click on Conditional Formatting situated in the Styles group, then click on New Rule.  
  • In the top pane of the resulting dialog, you have to select the last option, use a Formula to Determine Which Cells to Format.  
  • You will have to enter the formula in the bottom pane.  
  • For example,   
  • =COUNTIF($D$3:$D$16, $D3)>1  
  • Click on Format, choose a format, then click on OK.   
  • Click on OK.  

The COUNTIFS() function itself counts the number of times a value occurs in the column you selected. If that value is greater than one, it means that the value occurs more than once, then the function will return True, and the format is applied. If the count is 1 or less, the function will return False, and nothing happens.  

Now, you have got to know about two conditional formatting rules which you can use to work with. One is built-in that helps to highlight individual values. The other one is a formula that will help you highlight the entire record.  

If you liked this article (or if it helped at all), leave a comment below or share it with friends, so they can also know How to highlight duplicate values in Excel? 

You Might Also Like

How to Root Android?

How to Set Up Voicemail on Android?

Can I upload my own subtitles to Plex?

How to Retrieve Deleted Text Messages on Android?

How to Record a Phone Call on Android?

TAGGED: duplicate values in Excel, Excel, highlight duplicate values, highlight rows
Share this Article
Facebook Twitter Pinterest Whatsapp Whatsapp Reddit Telegram Email Copy Link Print
Avatar photo
By Bippin
Aah, I see you’re here now, looks like I finally managed to capture your attention. If you’ve come all the way here then why don’t you follow my social accounts, so that you can be on this journey with me? Say what?
Previous Article What are the cleverest iPhone/iPad signatures people use instead of What are the cleverest iPhone/iPad signatures people use instead of “Sent from my iPhone”?
Next Article Sent as a Text message on iPhone, How to fix it? Sent as a Text message on iPhone, How to fix it?

Trending

Learn how to use the Microsoft word document
Learn how to use the Microsoft word document
Explain Microsoft
A few details regarding the upcoming Google Pixel 7 as well as the Pixel 7 Pro
A few details regarding the upcoming Google Pixel 7 as well as the Pixel 7 Pro 
Google
How to Electronically Sign PDF Documents Without Printing and Scanning Them? Easy guide
Life How To
Apple M1 VS M2 Comparison - Features and Specs
Apple M1 VS M2 Comparison – Features and Specs
Apple Mac
What Is r/Place? How to use it on Reddit? Everything Explained about rPlace
What Is r/Place? How to use it on Reddit? Everything Explained about rPlace
Lifehacks How To SocialMedia
How to View Blurred Text on Websites?
How to View Blurred Text on Websites?
How To

People Also Ask For

How to Root Android?
Android

How to Root Android?

Bippin Bippin February 15, 2023
How to Set Up Voicemail on Android?
How To

How to Set Up Voicemail on Android?

Bippin Bippin February 13, 2023
Can I upload my own subtitles to Plex?
How To

Can I upload my own subtitles to Plex?

Bippin Bippin February 13, 2023
How to Retrieve Deleted Text Messages on Android?
Android

How to Retrieve Deleted Text Messages on Android?

Bippin Bippin February 12, 2023
How to Record a Phone Call on Android?
Android

How to Record a Phone Call on Android?

Bippin Bippin February 12, 2023
How to Reset Android Phone when Locked?
Android

How to Reset Android Phone when Locked?

Bippin Bippin January 22, 2023
How to Share Location on Android?
Android

How to Share Location on Android?

Bippin Bippin January 22, 2023
How to Transfer Data from Android to Android?
How To

How to Transfer Data from Android to Android?

Bippin Bippin January 19, 2023
How to Retrieve Deleted Text Messages on Android?
How To

How to Retrieve Deleted Text Messages on Android?

Bippin Bippin January 19, 2023
How to Facetime Android?
How To

How to Facetime Android?

Bippin Bippin January 18, 2023
How to Empty Trash on Android?
Android

How to Empty Trash on Android?

Bippin Bippin January 16, 2023
How to SSH on Windows?
How To

How to SSH on Windows?

Bippin Bippin January 14, 2023
Show More
HalfofTheHalfofThe
Follow US

2023. All Rights Reserved by Halfofthe. “As an Amazon Associate we earn from qualifying purchases.”

  • About Us
  • Contact Us
  • Cookie Policy
  • Privacy Policy
  • Terms and Conditions

Removed from reading list

Undo
Welcome Back!

Sign in to your account

Lost your password?