QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Highlight Duplicates in Excel?

By Neha T Leave a Comment

We use Excel to save or accumulate information. Working with large Excel sheet, we may duplicate records or information and further, it becomes difficult for us to identify duplicates. Well, Excel provides a facility to highlight duplicates.

Analyzing the highlighted duplicates, you can determine whether you have to keep that record or you have to delete it. In this section, we will discuss the steps to highlight duplicates in excel.

Steps to Highlight Duplicates in Excel 2016:

Method 1

Step 1: First select the range of cells where you want to highlight the duplicates. Like in the image below, I have selected the range of cells A2:A17.

Highlight Duplicates in Excel-1

Step 2: In the Home tab you have a Styles group, here you have to click on Conditional Formatting drop-down button.

Highlight Duplicates in Excel-2

Step 3: From the displayed menu of Conditional formatting select Highlight Cell Rules which will further provide you options, and from these options you have to select Duplicate Values….

Highlight Duplicates in Excel-3

Step 4: A Duplicate Values window will appear where you can format the cell in your way. In this dialog box, we have two dropdown boxes. In the first drop-down box you have two options, ‘Unique’ & ‘Duplicate’ as we have to highlight the duplicates, select Duplicate.

In the second drop-down box, you have to make choice about how your duplicate would appear. Like, I have selected an option which will make the background of the cells with duplicate values, as yellow and the text would be in dark yellow colour as you can see in the image below. Click OK.

Highlight Duplicates in Excel-4

Method 2

Step 1: Again you have to select the range of cells where you want to highlight the duplicates.

Highlight Duplicates in Excel-1

Step 2: In the Home tab under the Styles group click on Conditional Formatting drop-down button. It will display options from where you have to select ‘New Rule’.

Highlight Duplicates in Excel-5

Step 3: A New Formatting Rule window appears here, under ‘Select a Rule Type:’, select the option where you can format cell that has unique or duplicate value.

Under ‘Edit Rule Description’ select ‘duplicate‘ in the dropdown box. Then click on Format button to format the cell with a duplicate in your way.

Highlight Duplicates in Excel-6

Step 4: Format Cells window will appear. Under the ‘Font’ tab you can change the font type and font colour. Under the ‘Border’ tab you can select a border for a cell with duplicate value. Under the ‘Fill’ tab you can select the background colour or pattern for the cell with duplicate value. Click OK.

Highlight Duplicates in Excel-7

In the New Formatting Rule window, again click OK

Highlight Duplicates in Excel-8

Step 5: You can see in the image below, that the duplicates are highlighted in the selected range.

Highlight Duplicates in Excel-9

In the New Formatting Rule window, under ‘Select a Rule Type:’ you can select the option which allows you design a formula, to identify which cells have to be formatted and use Format button to highlight duplicates.

So, this is all about the steps of highlighting the duplicates in excel. You can explore more methods to do the same. Hope the content is useful. Keep reading.

You Might Also Like:

Delete Duplicates feature imageHow to Delete Duplicates in Excel 2016? compare two columns in Excel Feature imageHow to Compare Two Columns to Find Matches & Differences in Excel 2016? How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016? Lock Formulas or a Cell in Excel - feature ImageHow to Lock Formulas or Cells in Excel? Merge Cell in Excel feature imageHow to Merge Cells, Columns & Rows in Excel? Create a named range in Excel feature imageHow to Create Named Range in Excel? Edit a Drop-Down List featured ImageHow to Edit Dropdown List In Excel 2016?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to enable picture in gmail chat window
  • How to Disable Cookies in Firefox?
  • How to create and execute Macros in MS Word
  • How to embed a webpage in gmail
  • How to Change Default Browser in Windows 10?

Copyright © 2023 · QueHow · Contact Us