The COUNTIF Formula to Avoid Duplicate Content in Excel.

Posted on December 12, 2007 
Filed Under Excel Tips & Tricks

New to the World of Office, XP and Vista Tips & Tricks?

  Here are a few posts the other readers recommend you check out.

The next article in the category Excel Tips and Tricks isn’t a real Excel Tip or Trick. I would say it’s more a Tutorial for the advanced Excel users among us. Although it is a pretty cool Trick to know and to use.  

 

This next Excel Tip and Trick will prevent you to enter duplicate content in your Excel spreadsheet. You’ll do this by using the COUNTIF function in your spreadsheet.

 

If you want to have Excel set to automatically avoid duplicate values, you need to follow the next steps.

 

  • Select the cells A1:A10
  • Go to the Data Menu
  • Click on the Data Validation menu (this isn’t necessary in Excel 2003)
  • And select Data Validation

 

Excel Tip Trick

 

  • Now select in the Data Validation window the settings tab
  • And select Custom
  • And enter the following Excel formula =COUNTIF($A:$A;A1)=1

 

Excel Tip Trick

 

 

 

 

 

 

 

 

 

 

 

Now for the fun part go to the Error Alert tab. You have the ability to customize your warning message. You just have to play around with this. As you can see you have several pictures and options like a customize title and/or error message.

 

Excel Tip Trick

 

 

 

 

 

 

 

 

 

 

 

Now click OK and you’re done. Now when you enter duplicate content in one of the select fields you’ll see your error message.

 

Excel Tip Trick

 

Do you want to see more? This is related...

Invite your friends to the World of Office.
Did you ever thought about publishing your own eBook or PDF report?
Invite your friends to the World of Office.
Grab the Easy PDF Toolkit, it was never this easy! If you invite your friends and we will receive at least 3 visitors then you will receive a free copy of this amazing kit and that is including all the cool bonuses. Invite your Friends here!


If you liked this article or you thought it was helpful then you would do us a real big favour by hitting one of the below icons. Thank you!! That’s really cool and we really appreciate your support.





Comments

2 Responses to “The COUNTIF Formula to Avoid Duplicate Content in Excel.”

  1. Ricardo Martínez on April 11th, 2008 3:27 pm

    Very usefull tricks. As a suggestion: would’nt you classify them in a sort of complexity levels (say three) beginner, normal and advanced users tips?

    Thanks

  2. Vincent on April 11th, 2008 3:44 pm

    Hi Ricardo,

    That’s a very good suggestion. I have been thinking about this but I’m not quite sure how to set-up the ranking and how to integrate it into the blog. Not only has it to be a useful ranking, fully integrated in my blog and archives but it also has to look good.

    But it’s for sure something to think about. Thanks for your suggestion, I appreciated.

    Cheers

Leave a Reply