Apply conditional formatting to one column

Question

Is it possible to apply conditional formatting to one column such that when the cell has the same value (either text or number) as other cell in the column, the current cell will be formatted.

For example:

A1 = XXX
A2 = XXY
A3 = XX1
A4 = XXX
A5 = XXX

Cells A1, A4 and A5 will be formatted to the same color

Answer 1

Conditional Formatting(Custom Formula):

=$A$1

Apply to:

A:A

Answer 2

The following solution will require additional formulas in other cells.

• In cell B1, write =unique(A1:A5). The unique values XXX, XXY and XX1 will be collected in column B.

• In C1, write =countif(A$1:A$5;B1) and replicate until C3. Column C will contain the counts of the unique values. In this case, XXX appears three times, and the others appear once.

• In D1, write =if(C1>1;B1;"") and replicate until D3. Column D will contain the non-unique values of column A, in this case only XXX.

• Apply conditional formatting to column A such that values that coincide with column D are highlighted (i.e. "Text is exactly" =D1:D3).