If you have a huge list of data in excel from different sources and want to check and remove duplicate values in Excel then you are at right place. Today we will learn how to find duplicate values in Microsoft Excel.
Microsoft Excel has a built-in intelligence which enables it to recognize the duplicate entries for any type of content. You can find duplicate values no matter it is a number, string, dates, text or even math equations. A number of formulas are available to sort the values on the basis of multiple criteria.
You can remove duplicate values in Excel after finding them but only if you need. However, the highlight feature alone is enough to find the duplicate values.
Find Duplicate Values in Cells, Rows, Columns
First open the Excel file for which you want to find the duplicate entries. Now there are two options either to run the check for an entire sheet or to selected number of cells.
- Select the rows and columns for which you want the system to find duplicate values.
- At next, move to HOME TAB and find the option CONDITIONAL FORMATTING.
- Click on Conditional formatting then HIGHLIGHT CELLS RULE>> DUPLICATE VALUES.
- Let the system run, the time it takes depends on the size of data. The huge the data the more time it takes.
- Finally, you will get all the duplicate values highlighted. A popup will also appear where you can select the color for all duplicate values.
You can also customize the highlight color and other things by using the customize box in the same popup.
How to Remove or Delete Duplicate Values in Excel?
This is next step where you want to remove or delete the duplicate entries in Excel. You can manually delete the cells, but if the data is too big then it must be done automatically.
Now select the row or column to delete the duplicate entries. Excel currently only support to remove duplicates from one column or row at a give time. If you have selected multiple columns and rows at single time it will not work properly or may even not find any duplicates at all.
Because, in coding with which Excel is programmed to find duplicates the values become unique when they are in different columns and rows. Because when the code compare values from multiple columns and rows it will find the data unique on the basis of a single different value in a given cell or a row or column and thus mark the whole row or column as unique.
However, its a little deep thing to understand, you can try the feature at your data and can then compare the results. This will help to understand the concept in detail.