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?

  • 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 !!
  • 1
    Nice solution. I really enjoyed it. – Saeed Neamati Sep 21 '13 at 14:49

The simplest way may be with Data validation (under Data). Assuming the unnamed column in question is ColumnA, a Custom formula validation rule of:


WA29823 example


The little red triangles are the warning signs.

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.