This post will guide you how to highlight a cell that contains more than a certain length using Conditional Formatting function in Excel. How do I highlight cells greater than or less than a certain text length in Excel 2013/2016/2019/365.
Table of Contents
1. Highlight Cells Based on Text Length
Assuming that you have a list of data in range A1:A6, in which contain text values. And you want to highlight All cells in red color if the cell length is more than 6 characters in it. How can I do it. You can use the Conditional Formatting function with the LEN function to achieve the result of Formatting Cells in selected range that containing more than 6 characters. Here are the steps:
#1 select the range that you want to highlight cells based on text length.
#2 go to HOME tab, click Conditional Formatting command under Styles group. And click New Rule from the popup menu list. And the New Formatting Rule dialog will open.
#3 choose Use a formula to determine which cells to format option under Select a Rule Type section in the New Formatting Rule dialog box. Then enter this formula =LEN(A1)>6 into the Format Values where this formula is true text box. Note: the Cell A1 is the first cell of your selected range.
Note: If you need to highlight cells with more than 20 characters, you can easily update the formula, and the formatting will adapt accordingly.
#4 click Format button in the New Formatting Rule dialog box, and the Format Cells dialog will open. And switch to Fill tab, choose red color as you need in the Background Color list. Click OK button.
#5 click Ok button to apply those changes.
#6 you would notice that all cells in range A1:A6 that contain more than 6 characters have been highlighted in red color.
The cells that exceed the specified length will now be automatically highlighted, making them stand out in the dataset.
2. Video: Highlight Cells Based on Text Length
This tutorial video will delve into the step-by-step process of using conditional formatting to highlight cells that surpass a defined length in Excel.
3. SAMPLE FIlES
Below are sample files in Microsoft Excel that you can download for reference if you wish.