Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

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

How can I have a kind warning if I am adding a text to the cell which was already added somewhere in previous cells of the same column?

share|improve this question
Did you had the time to evaluate my solution? – Jacob Jan Tuinstra Apr 28 '13 at 19:19

I prepared the following solution.


Not case sensitive:


Case sensitive:



If you set conditional formatting in A1 on Invalid to be marked red, then a visual change will make you realize that a duplicate value has been entered, see pic:
enter image description here

In combination with Google Apps Script, you can really make it more visual. With the following script, that uses the onEdit trigger function, you can make the font color turn red:


function onEdit() {
  var sh = SpreadsheetApp.getActive()
  var cell = sh.getActiveCell();
  var A1Notation = columnToLetter(cell.getColumn()) + '1';
  var color = sh.getRange(A1Notation).getValues() == "Invalid" ? 'red' : 'black';

// stackoverflow.com/a/21231012; Adam Luske
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  return letter;


I've prepared an example file for you: Avoiding Duplicates.


  • you need to fix the headers, in order to keep seeing visual change (without using the script).
  • you need to pay attention, because all of this isn't bullet proof !!
share|improve this answer
Nice solution. I really enjoyed it. – Saeed Neamati Sep 21 '13 at 14:49

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.