Setting background colour of cells based on their string length

microsoft-excel worksheet-function conditional-formatting

10,048

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

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

10,048

kei

Updated on September 18, 2022

Comments

kei over 1 year
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.
- danielpiestrak over 11 years
  
  Simplicity of answer depends on which version of Excel you're using.
- Admin over 11 years
  
  I'm using Excel 2010
- danielpiestrak over 11 years
  
  Great! easy to manage/explain, added answer.
Admin over 11 years

I must have been doing something wrong, after using format painter, every cell now has the rule, but it's still referencing A1.
Admin over 11 years

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.
Admin over 11 years

Awesome! This works for me. It was the =$A$1 to =$A:$A that I missed.
Admin over 11 years

Yea this works too.
TheModularMind over 11 years

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
TheModularMind over 11 years

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.