Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't know how.

Say, I'm entering data anyplace within column B, and I want to know if the inputting value is already elsewhere in the column (above or below the current cell) and don't allow if there is a duplicate.


  1. I've seen answers like this one on this site, but they all have an range say from B1 to B20, how do I have this validation on the entire column, is this possible?

  2. Seem like when I drag on the cell, duplicated values can appear, it is only when I try to input the value that this validation works. So, how do I restrict on the dragging as well as manual input on this?

share|improve this question

Also, just a side note.... when referring to particular cells in a column (like in your example) you would refer to them as B1:B20. To refer to the whole column, you can use B:B. That describes an array of all data in the B column. Hope that helps.

share|improve this answer
Thank you so much, this answered part of my question, upvoted! – fanray Jul 19 '10 at 17:43

You can only deal with copying over validation with VBA:

share|improve this answer

I answer only your question 2. You have to write your own Private Sub Worksheet_Change routine.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.