Identifying duplicate entries in a large list can be very time consuming but with conditional formatting duplicate entries can be identified very fast.You just need to select the cell range and then click “format” tab and then conditional formatting (Keyboard Shortcut is: Alt+O+D). From the condition1 drop down menu choose “Formula Is” and enter =A3=A2 (Assuming the selected cell range is A2:A23). Click the format button and choose any color and just hit OK.
However the above formula would work only when we have duplicate entries in adjacent cells. If instead we want to identify all the duplicate entries that are there in the entire column we can use the countif function instead of “=A3=A2″. So this time in the formula bar of conditional formatting you enter =COUNTIF($A$2:$A$23,A2)>1. We need to freeze the range( A2:A23) by using F4 key otherwise as we go down from A3 to A4 the range would become (A3:A24) which is not what we need. However cell A2 is an active cell and we want excel to take A3 and so on as move down that is why we have kept A2 as a relative reference instead of making it absolute(Dollar sign).
Share This
Popularity: 16%