Setting background colour of cells based on their string length

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`.

-

migrated from stackoverflow.comOct 6 '12 at 10:02

This question came from our site for professional and enthusiast programmers.

Simplicity of answer depends on which version of Excel you're using. –  Gimp Oct 5 '12 at 15:43
I'm using Excel 2010 –  kei Oct 5 '12 at 15:46
Great! easy to manage/explain, added answer. –  Gimp Oct 5 '12 at 15:57

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`

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`

-
Awesome! This works for me. It was the `=\$A\$1` to `=\$A:\$A` that I missed. –  kei Oct 5 '12 at 16:00
Yea this works too. –  Stepan1010 Oct 5 '12 at 16:01
You can actually select the whole of the range to apply it to, then write the rule, but make sure you write it as if it is only for the active cell. So select A1:A20 (in that direction, so A1 is active) then create the rule as above, then all the cells already have the rule applied, and if you select a cell and look at the rule, it appears to be defined for that cell (relatively). Identical end result, just a step or two less to do. –  AdamV Oct 9 '12 at 12:33

You can use the format painter to paint that conditional format formulato all the other cells in that column(it will change it to be for the relative cell):

And if you click on the "A" header it will automatically put it in the whole column so you don't have to drag it down forever. Good Luck.

With this formula put intitally in A1 (=LEN(A1)>5), and then painted to the other cells( which will automatically do =LEN(A2)>5, =LEN(A3)>5, =LEN(A4)>5 etc.) here is the result you should get:

-
I must have been doing something wrong, after using format painter, every cell now has the rule, but it's still referencing A1. –  kei Oct 5 '12 at 15:50
I'm not sure what you are doing wrong. You set up your conditional format formula in A1. Then you click on A1, then you click the format painter button. Then you click on the cells you want to paint it to. Are you using \$ symbols to fix the reference? You should obviously not do that. See my edit for how the result will look. –  Stepan1010 Oct 5 '12 at 15:53
Format painter is generally a bad practice for re-applying the rule to more cells. If you only paint over the 'extra' cells you now have rule applied to the first cell and another (identical) rule applied to the others, so if one is edited this does not apply to all, so you MUST include the original cell as well as the new ones. If you then use format painter again for the same cells you end up with the rule duplicated which has a performance impact and means if you remove the rule you have to remember to remove it more than once. Use "Applies to" to properly extend the range for the rule –  AdamV Oct 9 '12 at 12:30