Author Message

Joined: 25 Sep 2007
Posts: 272

 Posted: Sat Jan 28, 2017 3:17 pm    Post subject: Find duplicates with conditional formatting in Excel In Excel, you can highlight cell values with colors through conditional formatting, to quickly capture important information in your spreadsheet. We will do it here step-by-step to highlight our data with duplicate entries: Select your data range (A2:C12) Click on Conditional Formatting (under Home tab) > then Highlight Cells Rules > click on Duplicate Values... 'Duplicate Values' window opens > select Duplicate from the drop down menu, and any color of your choice to highlight the duplicate cells > click OK. Note: Here you could also select 'Unique' from the drop down list to highlight cells with unique values only. Try this as well Values that are present more than once are now highlighted with your selected color. This includes duplicate entries as well as values present more than twice in the list. Use a formula to get exactly the same result as above: Select your data range (A2:C12) Go to Conditional Formatting > this time click on New Rule... from the New Formatting Rule window > select Use a formula to determine which cells to format > here enter the formula =COUNTIF(\$A\$2:\$C\$12;A2)>1 and from the Format... select any color to highlight the cells > click OK. You have the same result as above, but this time you did it with a small programming Few words on COUNTIF function: COUNTIF function takes only two arguments - a range, and a criteria (it looks for the criteria within the range). So its syntax is =COUNTIF(range, criteria) So when I enter =COUNTIF(\$A\$2:\$C\$12;A2)>1, the COUNTIF function looks for the criteria (cell A2 = New York) withn the range (A2:C12) and when it finds New York more than once(>1), it marks the cells containing New York with the blue color (that I selected under New Formatting Rule above). As I have selected the range before clicking on Conditional Formatting, so the formula is copied automatically to other cells to look for the next entry and so on. Look the interesting thing here: In my list, Singapore and Hong Kong are present 3 times each. Here comes the point, you can now play with the Countif function . How? First select your cells (A2:C12) – don't forget it, (may be you do it in a new worksheet, just copy your original data) then instead of =COUNTIF(\$A\$2:\$C\$12;A2)>1 enter =COUNTIF(\$A\$2:\$C\$12;A2)=2, it marks cells only with duplicates Result: only duplicate entries are highlighted. Now to find values that are present more than two times: instead of =COUNTIF(\$A\$2:\$C\$12;A2)=2 enter =COUNTIF(\$A\$2:\$C\$12;A2)>2 To find values present only 3 times: instead of =COUNTIF(\$A\$2:\$C\$12;A2)=2 enter =COUNTIF(\$A\$2:\$C\$12;A2)=3 Don't forget to select your cells first!! So now you know, how it works *Recommended: Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena. For comments of suggestions, please contact us: info@shamskm.com
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT + 2 Hours Page 1 of 1

 Jump to: Select a forum Forum Rules----------------General Rules Learn German Easily----------------German as a Foreign Language (Deutsch als Fremdsprache) Category----------------General ForumPolitics - commentsJokesScienceTechnology & How-To ForumEnvironmental Issues forumGeographic Information System (GIS) forumWebsites Related ForumPublic Health & Yoga forum Cappuccino & Espresso----------------Cappuccino & Espresso Job Posting----------------Job offerThe Space travellers
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum