Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Table of Contents
Highlight Duplicate values in Excel
When we are working with huge data especially financial data we may require to find the duplicate values. Duplicate values could be duplicate invoice entry, duplicate indent, duplicate of anything, which causes errors to our reporting purpose.
Duplicate entry not necessarily in a single column it could be in multiple columns, it could be in multiple rows or in the entire data range itself. Finding those duplicate values is not that easy without the help MS Excel in the modern data-driven world. Working with big data set is no surprise these days.
For all our good sake we have a tool in excel which can identify and highlight duplicate values in excel. In excel we have a tool called Conditional Formatting to highlight all the duplicate excel values in the selection range.
Excel goes one step further from identifying the duplicate values to remove them from the selection range if required by the user. In this article, I will explain you the process of identifying, highlighting, and removing duplicate values in excel.
How to Highlight Duplicate Values in Excel?
Below are some of the examples that can help you to identify and remove duplicate values in Excel.
Example #1 – Highlight Duplicate Excel Values using Conditional Formatting
Conditional Formatting is one of the simplest but very powerful built-in tools in excel. It is useful to format the cells based on the conditions we give, that is why it is named as Conditional Formatting.
Mr Badri has a list of few names. In this list, Mr Badri got few duplicate names. Mr Badri needs to identify those duplicate names. I will help him out to identify and remove those duplicate values in the list.
Below is the image of that name list that contains duplicate values.
Step 1: Select the list range that contains duplicate values. (Select only the data range, not the entire worksheet)
Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
The excel shortcut keys to highlight duplicate excel values is:
Step 3: Once you click on Duplicate Values it will open up the below dialogue box. Here we can select the type of formatting 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.
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.
Example #2 – 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.
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 me the duplicate value for me.
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 entry made in the future it should automatically notify me the duplicate value is made.
Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Step 3: Select the color you want to highlight. Here I have selected Green to fill with Dark Text.
Step 4: Here I have already found four duplicate values.
Step 5: Now try to enter the any of the duplicate invoice numbers it will find and highlight the duplicate excel entry.
Example #3 – Remove Duplicate Values in Excel
Finding and Highlighting the duplicate values is definitely the 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
The shortcut key to Remove Duplicates in Excel is:
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.
Step 4: Click on Ok button. It will instantly remove all the duplicate values from the selection. It will also show you the prompt how many duplicates are removed and how many unique values are remaining.
Look at the above image it has given dialogue box stating 3 duplicate values found and removed; 16 unique values remain.
Things to Remember About Highlighting Duplicates in Excel
- We can select different formatting styles under 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 are residing under conditional formatting.
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 Conditional Formatting Method along with practical examples and downloadable excel template. You may learn more about excel from the following articles –