#How to have excel find duplicates 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.

How to have excel find duplicates