Excel – Setting background colour of cells based on their string length

conditional formattingmicrosoft excelworksheet-function

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:

=IF(LEN(A1)>5,TRUE,FALSE)

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

enter image description here

enter image description here

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

Related Question