Managing a pile of data over excel sheets isn’t less of an art. Chances are, you might end up with duplicate entries, which can hamper the results’ accuracy. That’s why it’s imperative to learn how to find duplicates in Excel.
Because if you know how to find duplicates in Excel, you can remove them and save a lot of time. Otherwise, locating and changing every single duplicate value would be as hard as biting a bullet kind of task.
Moreover, for someone who considers himself/herself to be proficient in MS Excel, it is a must to know how to find duplicates in Excel. You’ll also discover methods to do so in Google Sheets and typical mistakes that you must not make. Thank me later!
How To Find Duplicates In Excel 2007 (MS Excel- 2007 and Updated Versions)
Excel sheet consolidation can be tricky at times, and while you work with the utmost care, there can be errors or duplications. But as far as duplication is concerned, the ways listed below can help you.
How To Find Duplicates In Excel Using Conditional Formatting
- Select the entire cell range in which you have entered data (take reference from the GIF).
- Now the Home tab would be open by default. Otherwise, you can click on it and open it up.
- Locate the ‘Conditional Formatting’ option and click on it.
- Then go to the ‘Highlight Cell Rules’ option.
- Click on ‘Duplicate Values.’
- A pop-up box will appear on your screen showing “Duplicate Values” in the box towards the left and specifying the color to highlight duplicates in the right box. Though there won’t be much problem with the color, the box on the left must be set to ‘Duplicate Values.’
- Then click on ‘OK.’
All the duplicate values in the selected cell range will get highlighted in different colors.
How To Find Duplicates In Excel Using COUNTIF
The COUNTIF function can help you if you want to know the number of times a certain value has been repeated in data.
- Select the entire cell range in which you have entered data.
- Now, in the cell next to the very first value, enter the under-stated formula:
=COUNTIF(
- Now select the table/cell range from which you wish to expunge out the duplicate values. And then fix it with $ signs.
- Press the comma and select the very first value from the table. Alternatively, you can enter the cell address of the very first value using the keyboard.
- Then put a close bracket. It’ll look like this:-
=COUNTIF($A$2:$A$12,A2)
- Press ‘Enter’.
EXPLANATION: Here, A2:A12 (we can also read it as A2 to A12) represents the cell range or the data in which we want to find the duplicate values. The $ sign is used to fix the values so that they don’t change when we drag and apply the same formula to other cells.
TIP: You can either insert the $ sign yourself by placing the cursor before every value or simply select the data/cell range and press F4).
- Then place the cursor at the bottom of the cell in which you’ve got the result. When the cursor changes to a “+” sign, drag it down to apply the same formula in the remaining cells.
You’ll see the number of times a value is repeated in the corresponding cell. Here you’ll notice that the cell address in the formula that appears after the comma “,” will change for every cell. This is so because we didn’t need it and so didn’t fix it.
How To Find Duplicates In Excel Using IF Function
Generally, the IF Function is a nightmare for most people, but if you learn the logic behind that, you will surely find it as easy as ABC. If you don’t know how to find duplicates in Excel using the IF Function, let’s understand it with an example:
- In the cell next to the very first value, enter the under-stated formula:
=IF(COUNTIF(
- Select the cell range/data wherein you want to expunge the duplicate values and fix it using the $ sign or press F4.
- Then proceed with inserting a comma and select the very first cell of the data (no need to fix it).
- The next step is to put a ‘>’ sign and then enter ‘1’. For your ease, here’s what you need to enter:
IF(COUNTIF($A$2:$A$12,A2)>1, “DUPLICATE”, “”)
EXPLANATION: Since the IF Function returns the value you want if a condition does suffice and doesn’t suffice, here we’ve used COUNTIF in it. In other words, we’ve commanded that we want “DUPLICATE” if the number of times a certain value has been repeated more than once, and if it has not been repeated more than one time, we want the cell to be blank, which is indicated by (“”).
The cell address of the data table has to be fixed with the $ sign because otherwise, the reference data will change when we drag the cursor to apply the function in other cells.
How To Remove Duplicates In MS Excel 2007
- Select the entire cell range in which you have entered data.
- Now the Home tab would be open by default. You need to hover over to the ‘Data’ tab.
- Locate the ‘Remove Duplicates’ option in the pane.
- A dialogue box will open in front of you. Now, if you want to remove duplicate values from only a certain or specific column, you can check/tick the boxes next to that column and uncheck/untick the boxes next to those columns.
- Click ‘OK.’ Again, click on ‘OK.’
How To Find Duplicates In Excel In Google Sheets
Since most people today work on Google Sheets, it can’t be left out while discussing this topic.
How To Find Duplicates In Excel Using Format Rules
- Select the data cell range in the sheet.
- Click on the ‘Format’ tab on the screen.
- A separate pane will open up on the right side. Under ‘Format Rules,’ select the option that best fits your requirement. For instance, if you think a certain name or expense, let’s say ‘Telephone Bills’ has been entered more than once, i.e., it is a duplicate value, select the ‘Text Contains’ option and enter the text in the box just below it.
- Finally, click on ‘Done.’
You’ll find the duplicate values highlighted.
How To Find Duplicates In Excel Using COUNTIF
The COUNTIF function can help you if you want to know the number of times a certain value has been repeated in data.
- Select the entire cell range in which you have entered data.
- Now, in the cell next to the very first value, enter the under-stated formula:
=COUNTIF(
- Now select the table/cell range from which you wish to remove the duplicate values. And fix them with $ signs.
- Press the comma and select the very first value from the table. Alternatively, you can enter the cell address of the very first value using the keyboard.
- Then put a close bracket. It’ll look like this:-
=COUNTIF($A$2:$A$12,A2)
- Press Enter.
EXPLAINATION: Here, A2:A12 (we can also read it as A2 to A12 represents the cell range or the data in which we want to find the duplicate values). The $ sign is used to fix the values so that they don’t change when we drag and apply the same formula to other cells.
TIP: You can either insert the $ sign yourself by placing the cursor before every value or simply select the data/cell range and press F4).
- Then place the cursor at the bottom of the cell in which you’ve got the result. When the cursor changes to a “+” sign, drag it down to apply the same formula in the remaining cells.
You’ll see the number of times a value is repeated in the corresponding cell. Here you’ll notice that the cell address in the formula that appears after the comma “,” will change for every cell. This is so because we didn’t need it and so didn’t fix it.
How To Find Duplicates In Excel Using IF Function
Generally, the IF Formula is a nightmare for most people, but if you learn the logic behind that, you will surely find it as easy as ABC. Let’s understand it with a practical example:
- In the cell next to the very first value, enter the under-stated formula:
=IF(COUNTIF(
- Select the cell range/data wherein you want to expunge the duplicate values and fix it using the $ sign or press F4.
- Then proceed with inserting a comma and select the very first cell of the data (no need to fix it).
- The next step is to put a > sign and then enter 1. For your ease, here’s what you need to enter:
IF(COUNTIF($A$2:$A$12,A2)>1, “DUPLICATE”, “”)
EXPLANATION: Since the IF Function returns the value you want if a condition does suffice and doesn’t suffice, here we’ve used COUNTIF in it. In other words, we’ve commanded that we want “DUPLICATE” if the number of times a certain value has been repeated more than once, and if it has not been repeated more than one time, we want the cell to be blank, which is indicated by (“”).
The cell address of the data table has to be fixed with the $ sign because otherwise, the reference data will change when we drag the cursor to apply the function in other cells.
How To Remove Duplicates In Google Sheets
- Select the data cell range in the sheet.
- Toggle to the ‘Data’ tab on the screen.
- Click on the ‘Data Cleanup’ option.
- Then click on ‘Remove Duplicates.’ A pop-up will appear on your screen, asking you to filter the data.
- Click on ‘Remove Duplicates.’
- Click on ‘OK.’
Mistakes To Avoid
Here are a few mistakes that people make while working around Excel, even after learning how to find duplicates in excel:
- Usually, people proceed to look for options without selecting the cell range or data, or table. This happens a number of times. Now such a mistake will hamper the process, and you will not be able to locate other options at times. So ensure you’ve selected the cell range from which you want to expunge the duplicate values.
- Selecting the wrong cell range will also result in inaccurate results.
- Don’t press the spacebar while using formulas.
- Leaving a single character out of the line such as a comma can result in error.
Finding duplicates in excel data is an overwhelming and tedious task. That’s where learning how to find duplicates in excel and removing them can help.
The above article has mentioned 3 ways to sort the data easily for duplications in excel. Also, make sure you don’t make the typical mistakes and ruin your time by getting stuck. Hope it helps!
• Content Writer at Mind Mingles
• Has presented her work in International Conference On Guru Nanak Dev Ji: Life, Philosophy and Legacy held at Mata Sundri College For Women and was awarded the title of “Best Paper Presenter” under College Student Category for the same (Dated: 27th and 28th September, 2019)
• Has a strong grip over creating interesting, unique and informative content pieces for a wide range of niches.
• A firm believer that words have an impact.
