How to Highlight Cells Greater Than a Certain Length in Excel

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.

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.

highlight cells based on cell lenght1

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

highlight cells based on cell lenght2

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

highlight cells based on cell lenght3

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.

highlight cells based on cell lenght4

#5 click Ok button to apply those changes.

highlight cells based on cell lenght5

#6 you would notice that all cells in range A1:A6 that contain more than 6 characters have been highlighted in red color.

highlight cells based on cell lenght6

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.

Leave a Reply