![excel find duplicates in another column excel find duplicates in another column](https://www.automateexcel.com/excel/wp-content/uploads/2021/01/Find-Duplicates-MATCH-and-IF.png)
Proficiency in Excel is a sought-after skill in various positions and being able to use Excel effectively can help you improve your career or your job application.
![excel find duplicates in another column excel find duplicates in another column](https://cdn.extendoffice.com/images/stories/doc-excel/highlight-if-duplicate-another-column/doc-highlight-duplicates-another-column-2.png)
#Excel find duplicates in another column how to
One way an employee can use Excel more efficiently is to learn how to find duplicates in this program.
#Excel find duplicates in another column software
If we use COUNTIF with IF, then we can label them the way that we want.Īfter we write COUNTIF in the first row, just copy the formula down until it can check all of our data.Excel is a commonly used spreadsheet software in the workplace for many applications. If we only use COUNTIF, then the duplicates will be marked with TRUE logic values. The screenshots also show the difference of using only COUNTIF and using COUNTIF with IF. We also write the condition “>1” after the COUNTIF to get the duplicate result from our formula. The COUNTIF inputs are the cell range of the “First Name” column and the first row’s first name. For that, we write a COUNTIF in the first row of the helper column. In these screenshots, we try to identify the duplicates in the “First Name” column. As you can see, we set up a helper column to be the place where we write our COUNTIF. You can check the formula writing for each screenshot in the formula bar. To understand easier about the COUNTIF use to find duplicates in excel, look at the screenshots below. You can change the “Duplicates” and “Not Duplicates” there with the labels you prefer. =IF(COUNTIF(data_cell_range, data_to_check_for_duplicates)>1, “Duplicates”, “Not Duplicates”) We can write the COUNTIF and IF combination as follows. If you want, then you can also label your duplicates with your own text (not TRUE/FALSE) by combining COUNTIF with IF. You will identify the duplicates in your cell range in no time! Get the TRUE and FALSE from all the data in your cell range using this formula. If FALSE, then there is no duplicate for the data. If the COUNTIF result is more than one, then your formula result becomes TRUE, meaning there is at least one duplicate. This is to check whether there is a duplicate for the data that becomes your COUNTIF input. This is so you can easily copy your COUNTIF later to other cells in your helper column after you write one.Īfter that, don’t forget to write the “>1” condition after you write your COUNTIF. Write this COUNTIF in the first cell of your helper column.ĭon’t forget to add dollar symbols in front of your row and column coordinates in your COUNTIF cell range input. Then, input the cell which is in parallel with the cell in the helper column where you currently type your COUNTIF. Input the cell range which contains all the data where you want to identify the duplicates of to your COUNTIF. =COUNTIF(data_cell_range, data_to_check_for_duplicates) > 1 It is pretty easy to use the conditional formatting to identify those duplicates, right? Use conditional formatting if you prefer it compared to the other methods given in this tutorial! The duplicates on your cell range will have been highlighted using the style you choose! You can also modify the marking style on your duplicates by choosing it from the Format with… dropdown.Īfter you have chosen, click OK. Make sure the dropdown there indicates that you want to mark the duplicates in your cell range. Click the one with the Duplicate Values… label on it.Įxcel will show you the New Formatting Rule dialog box. This will show other choices inside the Highlight Cell Rules choice. Put your pointer on the Highlight Cell Rules choice in the list shown. Then, click on the Conditional Formatting button in the Home tab. In the example, that means we highlight the data in the first name column in our data table. How do we do it using conditional formatting?įirst, you need to highlight the cell range where you want to check the duplicates on. Say we want to identify the duplicates in the “First Name” column there. We will explain the steps below with an example to help you understand them better.įor the example itself, let’s say we have a data table like this in our excel file. The steps to utilize this method are pretty simple too and you should be able to master it fast. That makes it easier for us to find duplicates using this feature.
![excel find duplicates in another column excel find duplicates in another column](https://www.exceldome.com/wp-content/uploads/2018/06/Find-duplicate-values-in-a-range.png)
The first method to find duplicates in excel is by using the conditional formatting feature.Ĭonditional formatting has a special menu to identify the duplicates we have in an excel cell range. How to Find Duplicates in Excel Using Conditional Formatting