Have you ever had to check to make sure certain lines of text aren’t too long? For example, maybe you need to check values being imported to a database or website that only allows a certain number of characters per field? In a case like this, what’s a good way use Excel to flag values that need to be shortened? One approach I like is to use conditional formatting with simple functions. Let’s take a look. Here’s a list of over 300 latin titles I generated on the lorem ipsum website. Let’s say we need to check each title to make sure it’s not more than 70 characters. To start off, let’s use the Excel function called LEN to calculate the length of each title. The LEN function just takes one argument - the text to evaluate - so we just need to enter a reference to cell C5, and double click the fill handle to copy the formula down the table. Now we can use a data filter to show only values with a length greater than 70. This works well , and we can even copy this list and send it to someone else, which is handy. But let’s look at another way to flag titles, this time with conditional formatting. First, we’ll un-filter the list, and select all data rows in the table. Then we add a conditional formatting rule to highlight any row with a title that has more than 70 characters. To do this, we need to use formula to trigger the rule. The formula is simple, just “= D5 > 70”. However, because this same formula will be evaluated for all cells in the table, we need to lock the column reference so that only cells in column D are compared to 70. We do this by adding a dollar sign before the “D”. For the format, let’s just use an orange fill. Back in our table, the rows that are too long are highlighted. Let’s add one more refinement to our model. Let’s expose the value we’re checking as a user input, and modify the conditional formatting rule to use this input instead of a hard-coded value. First let’s name the input cell max_length, and give it an orange fill. Next, we’ll edit the conditional formatting rule to use the named range “max_length” instead the value 70. After we update the rule, we can set max length to anything we like and our table responds accordingly. While this is a simple example, I hope you can see the power of using simple formulas with exposed inputs to build useful and flexible tools in Excel.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
