avoid to repeated data values in excel formula

New Contributor

I understand there's a formula you can write down manually in a single cell of a column and then drag down, to avoid repeated data values ​​in excel with the "=COUNT.SI(XXXXXX)=1" but i forgot how to do it correctly. I know there's a function in Data menu > Data validation, but the rank i will use is the WHOLE COLUMN, not just a short rank. That is why I want to manually write the formula down in the second cell (my chart has headers) and then drag the formula down, as my chart fills up. Can anyone can help me, please?

7 Replies


Are you referring to using this formula:



To do this?




Hello @Patrick2788! No, that formula is to count items. I'm looking for the avoid repeating data formula when you are filling up a database. Something like this, see column B, please...



the name of the client will repeat, but the code of the item in column B must not That is the formula I'm looking for =count.if(x:x;x,x)=1, but I don't remember how to write it down inside column B manually and then drag the formula down alongside the whole B column, without the data validation option in the data menu



best response confirmed by DifferentMink01 (New Contributor)


The screenshot is a bit blurry, but I believe I understand your request. Please see attached workbook.

I think that is what I'm looking for! ( i did not understand the attached workbook, but I searched in excel for the phrase "conditional formatting" and I got this result: "=if(logical_test,value_if_true,value_if_false") do you know how to do it, please? :3
The conditional formatting is using a formula to determine if the ID counted in a given cell is the first instance encountered. If it's not instance 1, the conditional formatting applies custom format ;;; which is a way of saying don't show cell contents.

@Patrick2788 can you help me make it please? I've tried and tried and i cannot doing it alone :'(


I may have to see the workbook but using this sample for discussion:


There are 3 things critical to getting this to work.

1. Select the entire range that will be affected by the conditional formatting. Do not include the header.

2.  The correct referencing styles for the COUNTIF as seen above.

3. Starting the formula at the correct row number.  Typically, if the header begins in row 2, the formula must start at row 2.  Note how the formula only references row 2.  If your selection is correct and the referencing styles are tight, Excel will apply the rule correctly to the entire selection.