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?


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.

  • Thank you so much, this answered part of my question, upvoted! – Ray Jul 19 '10 at 17:43

You can only deal with copying over validation with VBA:



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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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