How To Highlight Duplicates In Excel: Everything You Need To Know
Mar 08, 2023
Have you ever looked at an Excel spreadsheet and wondered why there are so many duplicate entries?
With large data sets, identifying each Duplicate manually can be almost impossible.
Thankfully, with just a few simple steps, you can quickly learn how to highlight duplicates in Excel. We will discuss how to quickly identify and highlight duplicate values within an Excel worksheet to improve the organization and analysis of your data.
We will discuss how to:
-
Highlight duplicate values in an Excel spreadsheet.
-
Highlight duplicates using conditional formatting.
-
Remove duplicates.
Highlight Duplicate Values in an Excel Spreadsheet.
Highlighting duplicate values in an Excel spreadsheet will save you hours of frustration by allowing you to spot mistakes or inputs that need correcting quickly. It's simpler than it might seem.
To find duplicate cells in Microsoft Excel:
-
Select the data that you want to check for duplicates. This could be a column, row, or range of cells.
-
Explore the Home tab and head to the Styles group. Hit Conditional Formatting, and you're good to go!
-
Select the Highlights Cells Rules, and click Duplicate Values.
-
The Duplicate Values dialog box window will open, with the default format being a light red fill and dark red text. To use the default format, click OK.
Read Also: Microsoft Excel Pricing: Cost and Pricing Plans
Highlight Duplicates Using Conditional Formatting.
Highlight duplicates in multiple columns, including the first occurrence.
If there are duplicates in the data set, Excel can help you find them. Excel has a built-in way to detect duplicate entries. You can also use a custom-built conditional formatting rule that uses this formula:
COUNTIF(range, top_cell) > 1
The formula to find duplicates in the range A2:C9 is as follows:
=COUNTIF($A$2:$C$9, A2)>1
-
Select your date range.
-
Navigate to Conditional Formatting, and click New Rule.
-
When the New Formatting Rule dialog box shows up, Click "Use a formula to determine which cells to format," Don't forget to input the formula.
-
Click the Format Button in the dialog box, Format your duplicate cells, and click OK.
-
After choosing a formatting style, click okay in the New Formatting Rule.
How to Remove Duplicates.
-
Select your range of data.
-
Navigate to Data Tools Tab, and Select Remove Duplicates.
-
In the Remove Duplicates dialog box, check all the columns to remove duplicates and click the OK button.
-
A notice from Microsoft Excel will appear, and click the OK button.
Final Thoughts on How To Highlight Duplicates in Excel.
The instructions above show the different ways to highlight your duplicate data in Excel. Use it to make your task more manageable. If you enjoyed this article, you might also like to read removing duplicates in Google Spreadsheets.
You can visit our home page for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel/Google Sheets Templates!
Frequently Asked Questions on How To Highlight Duplicates in Excel:
How do I identify duplicate cells in Excel without removing them?
You can use conditional formatting to highlight them. First, select the data that you want to check, then apply the conditional formatting rule for duplicates.
What does duplicate data do in my Excel sheet?
Repeating data makes the report less reliable and less helpful for making decisions.
How do I define duplicate data?
When the same data is stored in two or more cells, it is called data duplication and should always be checked because it takes up free space on the computer.
Related Articles:
Google Sheets Highlight Duplicates: Everything You Need to Know
How to Remove Duplicates in Google Sheets Without Using Apps Script
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
