Conditional Formatting For Duplicates in Excel Go here if you are not familiar with Conditional Formatting Conditional Formatting can be found by going to Format>Conditional Formatting. The purpose of this function is very similar to "Validation" except instead of stopping or warning the user when they enter a entry that does not meet the set criteria it formats the cell to how it's format condition has been set. For the purpose of all examples we will assume the range of cells that users will be entering data in is A1:H10. Highlighting Duplicates in Excel When you have large list or table of data you may need to go through the list to identify some or allduplicate entries. This can be a very time consuming and tedious task, but with Conditional Formatting they can be identified in seconds or better still already identified. Follow the steps below to see what I mean.
Click in cell A1 and holding down the left mouse button highlight down and across to cell H10. Notice that cell A1 is the only active cell, this is for reasons I will explain in step 3 Go to Format>Conditional Formatting and you will see the "Conditional Formatting" dialog box.
From the box directly below "Condition 1" select "Formula is"
In this Formula box type this formula: =COUNTIF($A$1:$H$10,A1)>1 It is important to note the absolution of A1:H10 with the dollar signs. The reason why we haven't absoluted A1 is because it is the active cell and leaving it as a relative reference will let Excel know we want B1 to have the same formula as A1 but replace A1 with B1, C1 to have C1 etc. Click the "Format" button and then select the format you want to use on all duplicate entries. Probably changing the Background colour on the "Patterns" tab to a nice bright colour will do the trick. When you are happy with the format conditions you have set click "OK". You will no doubt notice the "Add>>" button, this will allow you to set up to 3 Conditional Formats and I will show you an example of this next..
Now if you already have duplicates within your table or list they will all appear formatted as you set, or if the range has no entries, try typing in 2 entries the same within your range. If you only need one color, see Highlight Duplicates in Excel Colour Code Highlighting Duplicates in Excel Supposing you want separate the duplicates from the triplicates and the entries that occur more than 3
times . This can easily be done by using Conditional Formatting.
Click in cell A1 and holding down the left mouse button highlight down and across to cell H10. Notice that cell A1 is the only active cell, this is for reasons I will explain in step 3. Go to Format>Conditional Formatting and you will see the "Conditional Formatting" dialog box From the box directly below "Condition 1" select "Formula is" In this Formula box type this formula: =COUNTIF($A$1:$H$10,A1)>3 It is important to note the absolution of A1:H10 with the dollar signs. The reason why we haven't absoluted A1 is because it is the active cell and leaving it as a relative reference will let Excel know we want B1 to have the same formula as A1 but replace A1 with B1, C1 to have C1 etc. Click the "Format" button and then select the format you want to use on all entries that occur more than 3 times. Probably changing the background colour on the "Patterns" tab to red will do the trick. When you are happy with the format conditions you have set click "OK". Now click the "Add>>" button and again select "Formula is" in the Formula box type this formula: =COUNTIF($A$1:$H$10,A1) =3 Click the "Format" button again and set the format you
want, e.g. changing the background colour on the "Patterns" tab to yellow. Now click "OK".
Click the "Add>>" button again and repeat step 5 above, but type this formula: =COUNTIF($A$1:$H$10,A1) =2 and set the background color on the "Patterns" tab to blue. Now click "OK" for the last time.
Now all your entries that occur twice will appear in one format eg; blue background, all entries that occur three times will have another format eg; yellow background and all entries that occur more than three times will have yet another, eg; red background.
In one colum we find duplicate
=COUNTIF($A$1:$A$5000,A1)>1 =COUNTIF($B$1:$B$5000,B1)>1 =COUNTIF($C$1:$C$5000,C1)>1 =COUNTIF($D$1:$D$5000,D1)>1 =COUNTIF($F$1:$F$5000,F1)>1 =COUNTIF($E$1:$E$5000,E1)>1
rows=5000 Extra: =COUNT(D1:D60000) =E3+(E3*15/100) For (cmd) type dir and press enter and choose one option type and enter for cd desktop. dir > abc.txt =COUNTIF(K1:K601,"