Highlighting found values

When working with large tables, it is often necessary to make their search easier. One of the ways to do this is by applying the option for conditional formatting, with which we can mark all rows in which a value corresponding to the given criteria was found. In the next “recipe” you will find out how to perform such task.

We’ll search the values in a simple data table. It contains the date of payment, the name and surname of the employees and the amount of the payment. Above the table, in cell B1, there is a field where we enter the search criteria. Our goal is that, after entering the name and surname of the employee or part of it, all the rows that correspond to the given criteria will be marked in the table.

For this purpose, we will use the technique of conditional formatting based on a formula. First, it is necessary to select the range: the entire table. Then we open an option to add a new rule, and choose to create a conditional formatting rule based on a formula.

When specifying a formula, keep in mind that it should always return a TRUE or FALSE value so that conditional formatting can be applied. If the value is TRUE the rule will be applied, if it is FALSE it won’t. Let’s enter a formula based on the SEARCH function. We are searching for B1, we will fix the value as $B$1, and the search is performed in column B, which we will indicate by fixing the name of the column $B4. We will enter the formula:

=SEARCH($B$1,$B4)

If the condition is met, we will set the background color to be one shade of yellow.

We will try how this works. If we enter the criterion “Petar”, only those rows where this name appears will be highlighted in yellow.

As you can see, we have achieved the desired goal, but this formatting rule needs to be somehow refined. Please note that if we have not entered a search criterion, Excel colors all rows in yellow. We don’t want this! We will change the formula by adding another condition, namely that cell B1 is not empty. We examine this condition using the NOT and ISBLANK functions, which is an additional condition for fulfilling the criteria. We will join the two conditions using the AND function. The final formula looks like this:

=IF(AND(SEARCH($B$1,$B4),NOT(ISBLANK($B$1))),TRUE,FALSE)

Now, for each given employee name or its part, Excel highlights only those rows where it can be found within the table.