Excel formula to remove duplicates in a cell

#Excel formula to remove duplicates in a cell how to#

Type вЂ =COUNTIF($A$1:A1,A1)=1вЂ in the formula bar.

Select the range with the duplicate values (i.e.

Instead of highlighting only the values that appear once in the range, sometimes we want to highlight only the first occurrence of each value.

#Excel formula to remove duplicates in a cell how to#

You will see that the unique values in the selected range are highlighted as shown in figure 3 below.ДЇigure 3: Highlighted Unique Values How to Highlight the First Occurrence of Each Value Click on the down arrow and select вЂ UniqueвЂ as shown in figure 2 below. The default option selected is вЂ DuplicateвЂ. You will see a window shown in Figure 1 below.By unique values I mean values that only appear once in a range.ДЇollow these steps to highlight unique values in a range: You can follow almost the same steps to highlight unique values in a range. If you read the first section, you already know how to highlight duplicates in Excel. You will see that duplicate values are highlighted after the second occurrence as shown in figure 2 below.ДЇigure 2: Highlighted values after the second occurrence How to highlight unique values Steps are shown below for your convenience.ДЇigure 1: Steps to highlight the values after the second occurrence Change the format as desired for duplicate values, you can change the format for Numbers, Font, Borders, and Fill.

The general pattern for the formula is =COUNTIF( first cell of range locked with dollar signs : first cell of range unlocked, first cell of range unlocked ) > 1

Click on вЂ Use a formula to determine which cells to formatвЂ.

Go to вЂ HomeвЂ™ вЂ Conditional FormattingвЂ.

Select the range with duplicate values (i.e.

This can be accomplished by following the next steps: However, sometimes we want to highlight the second occurrence or later occurrences of the duplicate values. If you use the steps shown above, youвЂ™ll be able to highlight ALL the occurrences of the values with duplicates. You will see that duplicate values are highlighted as shown below:ДЇigure 4: Highlighted duplicate values How to highlight cell rules after second occurrence

You will see a window as shown below, Click вЂњ OKвЂњ.

Click on вЂ Conditional FormattingвЂ вЂ Highlight Cells RulesвЂ вЂ Duplicate ValuesвЂ.ДЇigure 2: Steps to highlight duplicate values.

Now if we have the duplicate values in a column or columns (as we have in columns A and B in figure2 below), the following steps can be carried out to highlight the duplicate values. TheВ вЂConditional FormattingвЂ™ menu option can be found on the Home tab of Excel 2013 as shown below.

You can do it with just a few clicks through the вЂConditional FormattingвЂ™ menu option. Highlight the first occurrence of each valueДЇinding duplicates in existing data is one of the important features of MS Excel.Highlight duplicate values starting at the second occurrence of each value.In this post IвЂ™ll cover all you need to know to deal with duplicate/unique values in MS Excel.ДЄfter reading this post youвЂ™ll be able to: If you have worked with MS Excel for a while I bet you have had the need to highlight/count unique or duplicate values.