excel formula to remove duplicates in a column

Go to the HOME Excel Ribbon Tab and select Conditional Formatting. As a Tip you can Select the First Cell and hit CTRL+ END. Select the Column in which you want to Highlight your Duplicates.

Follow the steps below: How to Highlight Duplicates in Excel using Conditional Formatting Select the Column to Highlight Duplicates Highlight Duplicates using Conditional FormattingĬonditional Formatting in HOME Ribbon TabTo Highlight Duplicates in Excel we need to use the Conditional Formatting feature in the HOME Ribbon Tab in Excel. I recommend separating columns with some Separator (such as the _) as otherwise you may again get False Positive Duplicates 2. =IF(COUNTIF(B$1:B1 '='&B2) 'DUPLICATE' '')ĭrag the COUNTIF Formula from the First Cell down to the Last. In our example assuming that the Last column uniquely identifies records in my table input the following formula in the first cell of your new Is Duplicate? Column: Add COUNTIF Excel Formula to Find Duplicates Excel Formula: Add Is Duplicate? Column to your WorksheetĪdd a Is Duplicate? Column to your Worksheet. Scroll down to the end to learn how to check for duplicates based on all three columns. This will work only on a single column (our unique column identifier). To Find Duplicates in Excel using Formulas we will use the COUNTIF Excel formula to verify if a certain row as been repeated above. Highlight using Conditional Formattingįollowing today’s post we will use the example Excel Table on the right. COUNT DUPLICATES Find Duplicates in ExcelĮxample Table with DuplicatesThere are several ways to go around Finding or Highlighting duplicates in Excel:

Excel formula to remove duplicates in a column