Making best use of Conditional Formatting to format cells based on character length:
If you have a list of words that needs to be formatted (highlighted in yellow, marked as bold, etc) based on the cell content's length (the length of the word or sentence), then Conditional Formatting will be our rescue partner.
Let's see how to get this done, step by step:
1. In this e.g. I have a list under Column A and the length of each cell content under Column B. I am using 'LEN' formula here on cells B1:B5.
=LEN(A1)
2. Select Column A cells A1:A5
3. Select Conditional Formatting > New Rule. In the 'New Formatting Rule' window that opens up, select the last option under 'Select a Rule Type' – which is 'Use a formula to determine which cells to format'.
You'll see an option to 'Edit the Rule Description:'
4. There in the box that reads 'Format values where this formula is true:' type in the following formula:
=B1>=5
This is because we have cells under Column B that has the numerical values showing the length of words input under Column A. So, whichever cell has 5 or greater than 5, then the corresponding cells under Column A will be formatted.
As you can see now, we're trying to format cells in Column A that meets the above condition – i.e. any cell that is greater than or equal to five characters in length. But for that to work, we first need to set some format settings.
5. Hit on the 'Format' button and select whatever formatting option you want to make.
In this e.g. I am going to highlight the cells in light green and format text as 'Bold'.
6. Click on 'OK'. In this window now, you can also read the formula under 'Applies to' to see if it shows up correctly.
i.e. =$A$1:$A$5
Click on 'Apply'. and then on 'OK' again to close this window.
Note: you'll notice that the cells that are highlighted in Bold and in light green, will not show as 'bold' on the formatting toolbar nor as a green filled cell. They'll show up as normal in font – which is NOT strange. The reason is given under Note 2 below.
Note 2: even if you try to change the format settings which is part of your 'formatting rule' under your selected 'conditional formatting' rules, it will not override your conditional formatting. In other words, your conditional formatting settings takes precendence over the manual settings made.
Hope this Excel tip helped you to accomplish whatever made you to land on this page. If yes, please do leave a comment to let me know your thoughts or any other questions you may have. Will be glad to help.
Leave a Reply