Explanation
In this example, the goal is to highlight the 5 bottom values in B4:G11 where the number 5 is a variable set in cell F2.
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don’t want to use named ranges, make sure you use absolute references for both of these ranges in the formula.
This formula is based on the SMALL function , which returns the nth smallest value from a range or array of values. The range appears as the first argument in SMALL, and the value for “n” appears as the second:
SMALL(data,input)
In the example, the input value (F2) is 5, so SMALL will return the 5th smallest value in the data, which is 9. The formula then compares each value in the data range with 9, using the less than or equal to operator:
=B4<=SMALL(data,input)
=B4<=9
Any cell with a value less than or equal to 9 triggers the rule, and the conditional formatting is applied.
Explanation
In this example, the goal is to apply conditional formatting to cells that begin with specific text, which is entered in cell G2. The highlighting is done automatically with a conditional formatting rule applied to the range B4:G12. The rule type is “Use a formula to determine which cells to format”. The formula looks like this:
=SEARCH($G$2,B4)=1
G2 contains the text to search for, and B4 is the cell being tested. The formula is evaluated relative to the active cell at the time the rule is created for each cell in the range, which is B4. Each cell in B4:G12 is evaluated separately. Since B4 is entered as a relative reference , it will change to the cell being evaluated. Since cell G2 is entered as an absolute reference ($G$2), it will not change.
The formula uses the SEARCH function to match cells that begin with “mi”. SEARCH returns a number that indicates a position when the text is found, and a #VALUE! error if the text is not found. When SEARCH returns the number 1, we know that the cell value begins with “mi” because the location of the text is 1. The formula returns TRUE when the position is 1 and FALSE for any other value. For example, in cell B4, the formula evaluates like this:
When this formula returns TRUE, it triggers a conditional formatting rule that applies a bright fill color to cells that begin with “mi”, as seen in the worksheet above. The value in cell G2 can be changed at any time and the conditional formatting will instantly update.
With a named input cell
You can simplify the formula a bit by using a named range for cell G2. For example, if we name G2 “input” the formula can be revised as follows:
=SEARCH(input,B4)=1
The named range automatically behaves like an absolute reference so there is no need to lock any references.
Case-sensitive option
=FIND(input,B4)=1
Like the SEARCH function, FIND returns the position of the text as a number. However, FIND is automatically case-sensitive.
