Duplicates in Excel don’t worth being there because when you want to maintain data in a proper structure and record for future reference and use, you don’t want rows and columns to have duplicate content or figures that unnecessarily increase the file size, make data complex to read, and have nothing to do with your powerful records. Do you face these issues? In this article, we will discuss the practical ways to find duplicates in Excel to answer your question about how to find duplicates in Excel. So, if you have the same data problems, you can solve them by following this article.
Table of Contents
Overview:
MS Excel sometimes has duplicate values, which you may find easily in short files with minimum data. But when your data set is large and you want to filter it to remove duplicates, it’s time-consuming and complex to manually find duplicate rows and columns. That’s why there must be some proven and helpful ways to let you keep your data original. Do you know about the COUNTIF formula or conditional formatting feature? It quickly helps you to highlight the duplicates for a specific number of the data. If your file has columns or rows with duplicate data mistakenly entered or copied, you can filter it and find the duplicate values.
Five Ways To Find Duplicates In Excel:
Do you doubt your files in Excel? If yes, you must open the file and go ahead with any of the following ways we have discussed to let you understand how to find duplicates in Excel.
Conditional Formatting:
You can use this feature in MS Excel 2007 and onwards to find your duplicate content. For example, any file may include the data related to expenses incurred for some services. It means the data has dates of purchase, a list of the service availed, and other essential data. It may have duplicate values in rows or columns. So, this method helps to find it out in a way. The following are easy steps you need to follow to find duplicate content.
- Choose the range of rows and columns where you doubt duplicate files—for example, A1:C20.
- Go to the home tab, and select conditional formatting. You can find this in the STYLES section.
- There is a drop-down menu. Select HIGHLIGHT CELL RULES and click on DUPLICATE VALUES.
- Search for duplicates, and you will see a pop-up window DUPLICATE VALUES. From its left side, choose DUPLICATE, and go to the VALUES in the drop-down menu to choose the color and highlight the cells having duplicate data.
- Click OK, and you will see the highlighted data in the data table.
- You can filter the columns to identify the duplicate values.
- Right-click on the columns and filter by selected color to show duplicate data.
- Thus, you have the results of duplicate values that you can delete.
Conditional Formatting For Special Occurrence:
- Let’s understand this method with one data having three Occurrences because this method is used for a specific number of duplicate values.
- Select the range of your rows and columns.
- Go to CONDITIONAL FORMATTING.
- Go to STYLES and click on NEW RULE, but remember that this option will only highlight the specific number of duplicate data using the COUNTIF formula.
- You will have a pop-up window with the new title of NEW FORMATTING RULE.
- Choose the type of rules and use a specific formula to identify the cells and format.
- You will see something like EDUT THE RULE DESCRIPTION. Here, you can enter your COUNTIF formula.
- The formula is “=COUNTIF.” It is the range of cells in your data table and cell criteria.
- Find your highlighted cells with a specific number of occurrences. Remember that you may see duplicate cells even if they’re triplicate or have more than three times copies if you want to know two.
Case Two:
- After entering the COUNTIF formula, click on FORMAT.
- It will show you the new pop-up window FORMAT CELLS.
- Go to the STYLES and choose REGULAR.
- Add the color in the FILL tab to highlight duplicate values.
- It will finish the selection in the FORMAT CELLS window.
- Click on the button OK.
- Again click on the button OK and go to NEW FORMATTING RULE.
- You will see the results in the window with the duplicate values, which are highlighted.
Changing Rules (Formulas)
You use this third method, and your question about How to find duplicates in Excel will be solved. It works based on changing the formula, which is an existing rule of your table. Here are the steps to follow.
- Go to Home Tab.
- Choose CONDITIONAL FORMATTING. It is in the STYLES.
- Go to CLEAR RULES to choose the following.
- It would be best if you cleared the rules from the cells. It will reset the rules for the specific table range selected. Hence, when you want to clear the rules, remember to select the data. You can also do this if you want to clear the rules from the whole Excel file.
- It will display the default settings of the table with no highlights and anything.
- You will easily be able to find the duplicates in Excel.
Remove Duplicates:
Now, we consider that you have found the duplicate data in Excel that you want to delete and keep the record fresh. It is best to keep a copy of the table selected before deleting the duplicate values because when you delete the Duplicates, they will be permanently removed or deleted from the file. Here are the steps to do it.
- Choose the table range that you want to make duplicate free.
- Go to Data Tab.
- Select REMOVE DUPLICATES. It is available in the Data tools. It will help you to keep the individual cells saved and duplicated data removed.
- You will see a pop-up window REMOVE DUPLICATES. It will have selected some default selections like Order ID and Items.
- Click on the SELECT ALL box.
- Click on OK and execute the task.
Do You Want To Change The Cell Selection?
If you have to change the number of rows and columns you selected, you can do UNSELECT ALL and select the range of rows and columns again. Once you perform this task, you will see some points in the display, like the number of duplicate values removed and several unique values remaining. Click on OK to finish the task.
- COUNTIF Formula:
You can use the COUNTIF formula to duplicate in the table. This formula functions based on the selection range and cell criteria and returns the number of Duplicates for every cell. Here are the steps to find duplicates.
- Enter the COUNTIF formula.
- Press ENTER key, and remember to keep the range fixed.
- Drag this formula throughout the range. You can do it with the fill handle. Alternatively, place your cursor on the next cell. Also, double-click the fill handle.
- The output will show you the number of duplicates in the entire range selection.
- You can also apply the filter to the column header and view the number of duplicates more than one, and that’s how to find duplicates in Excel.
Frequently Asked Questions:
What Is Finding Duplicates In Excel?
You may be working on several worksheets, and those files may have several duplicates or sets of data with more than one existence. It would be best if you highlighted them to delete. So, when the finding duplicates methods are applied, you can find them and highlight the same before deleting the data; it can be in the rows and columns. We have discussed the ways to find duplicates in Excel in this article.
How Can You Find Duplicates In Excel Columns?
Here is the formula to do it. “=COUNTIF(A:A,A2)>1”
This formula works to find duplicates in column A, and remember that it returns to true for every duplicate value, and unique values make the formula return to false. That’s how to find duplicates in Excel for columns, and if you still have any queries, let us know.
What Is The Excel Formula To Find Duplicates In Excel?
There is one general formula that you can use to find case-sensitive duplicate values. It is like this. “IF(SUM((-EXACT(range,uppermost_cell)))<=1,””,”Duplicate”)”
Did this formula work for you? Let us know how many duplicates were there in your file.
What Does This Formula Mean?
The function of this formula is to compare the CELL RANGE with the TARGET RANGE. Do you know that some functions have more than one instance? It means that when the duplicates are more than one, the formula function returns to DUPLICATE. Did you find this answer helpful?
Wrapping Up:
By now, you must have understood how to find duplicates in Excel. If your data file is small, you can manually target the Duplicates and delete them, but when the excel file has several sheets, and all of them have large data, it’s
tedious to find the duplicate data. We suggest you use duplicate value finder methods to ease your task. Did you know these five ways before this article? Let us know, and share this excellent information with your people working with excel.