Suppose I have this column of data
Canada
Chile
China
...
How do I set the background colour of each cell if the length of the string in it is greater than say 5? ('Canada' cell would be highlighted in my example)
I know you can do conditional formatting with something using something like LEN(A1)>5
but I want this rule to be implemented on the entire column.
I've tried copying the rule over (or maybe I'm copying it over wrong), but then A1
stays as A1
even when the cell if A2
.
Best Answer
Formula for cell A1:
Note that your formula above does not have the
$
symbol in it.Then go to Conditional Formatting > Manage Rules > Applies to
and change the value
=$A$1
to=$A:$A
PS - note that it isn't necessary to encapsulate the length inside of an
if
statement. It's just part of my personal preference as I find it easier to read. You could alternatively use=len(A1)>5