It’s not practical to use multiple SMALL formulas to highlight the smallest n values in each row in Google Sheets. Do you know why?
If you want to conditional format the smallest one or two numbers, then it’s OK to use the SMALL formula twice.
I mean we can use 2 SMALL formulas to highlight the smallest two values. But what about highlighting the smallest ten numbers in each row?
For this, you may need to use 10 conditional formatting rules using ten SMALL function based formulas. Not practical, right?
In this post, you will get my formula (single conditional rule) to highlight the smallest n values in each row in Google Sheets.
I have two versions of the same formula. One for including 0 and another for excluding 0 in the calculation. Please note that the ‘n’ in my example is 3.
Without wasting any time let me start the tutorial.
Highlight the Smallest N Numbers Including Zeros in Google Sheets
For this test, the numbers to highlight including 0s are in the range B2:J8. So select this range and key the following formula in the conditional format panel as per the screenshot that you can see just below the formula.
Formula # 1:
=regexmatch(
B2&"",
"^"&textjoin("$|^",1,
iferror(ArrayFormula(small($B2:$J2,sequence(1,3)))))
&"$"
)
In the above formula, the ‘n’ is 3. That means the above formula conditional formats the cells containing the smallest three values in each row.
To change 3 to smallest 5 values, change sequence(1,3) part of the formula to sequence(1,5). I hope now you could understand how to change ‘n’ in the formula.
Let’s see how the formula highlights the smallest n values in each row in Google Sheets. Here is the formula explanation.
Logic – Matches Min | Smallest N Values Using Regexmatch
Small Array Result
Assume the ‘n’ is 3. Without using multiple SMALL formulas, we can get the smallest 3 values in Google Sheets as below.
=IFERROR(ArrayFormula(small($B2:$J2,sequence(1,3))))Actually the SEQUENCE feeds the numbers 1, 2, and 3 to the SMALL. The above single formula is equal to the following three formulas: =small($B2:$J2,1), =small($B2:$J2,2), and =small($B2:$J2,3).
We want multiple numbers, i.e. the 3 smallest numbers, using one single formula. So the ArrayFormula function is necessary with SMALL.
Then what about the use of the IFERROR with SMALL?
If any of the rows contain less than 3 numbers (less than ‘n’ numbers), then the formula would return #NUM! error.
For example, if a row has only 2 numbers, then the formula would return the smallest 2 numbers and one #NUM! error.
The IFERROR formula makes that error to blank. Otherwise, the conditional format would skip that row in formatting.
Textjoin to Join the Smallest N Numbers
The TEXTJOIN converts the above SMALL array output to a regex ‘regular_expression’ (please refer to the Regexmatch syntax below) to match in each cell in the row $B2:$J2.
Syntax: REGEXMATCH(text, regular_expression)Here is that ‘regular_expression’ formula.
"^"&textjoin("$|^",1,
iferror(ArrayFormula(small($B2:$J2,sequence(1,3)))))
&"$"The ‘text’ in the Regexmatch to match using the above ‘regular_expression’ is the number in cell B2 (the first cell in the formatting range).
We can’t use B2 as the ‘text’ argument in Regexmatch. Instead, use it as a text by adding a null character to it like B2&"" (see formula # 1).
The cells that contain the matching numbers got highlighted.
Multiple Rules to Highlight the Min 3 Values Including Zero
Instead of using the above single formula, we can use the below three formulas as three separate rules (custom formulas) in conditional formatting.
=and(len(B2),B2=small($B2:$J2,1))
=and(len(B2),B2=small($B2:$J2,2))
=and(len(B2),B2=small($B2:$J2,3))In case we want to highlight the 4th smallest value, we require to include one more rule. So I am not recommending this method.
Highlight the Smallest N Values Excluding Zeros in Google Sheets
Let’s see how to highlight (apply fill color) the smallest n values in the range B11:J17 that excluding zeros in each row in Google Sheets.
Here is the formula for that.
Formula # 2:
=regexmatch(
B2&"",
"^"&textjoin("$|^",1,
iferror(ArrayFormula(small(filter($B11:$J11,$B11:$J11>0),
sequence(1,3)))))
&"$"
)Formula # 2 above has only one major difference with formula # 1. What’s that?
In formula # 2, the SMALL formula ‘data’ (refer to the syntax below) is filtered to skip 0 values.
Syntax: SMALL(data, n)Here is the SMALL formula part. The range reference $B11:$J11 as per formula # 1 (there it’s $B2:$J2) is replaced by filter($B11:$J11,$B11:$J11>0).
=IFERROR(ArrayFormula(small(filter($B11:$J11,$B11:$J11>0),sequence(1,3))))Since there are no any other changes, I am skipping the (further) formula explanation part.
Alternative Multiple Rules to Conditional Format Min 3 Excluding Zeros
To highlight the smallest 3 values excluding zeros in Google Sheets, you can also use the following formulas as 3 separate rules in conditional formatting.
=and(len(B2),B2=small(filter($B2:$J2,$B2:$J2>0),1))
=and(len(B2),B2=small(filter($B2:$J2,$B2:$J2>0),2))
=and(len(B2),B2=small(filter($B2:$J2,$B2:$J2>0),3))Not ideal if the ‘n’ is more than three. So better to stick with formula # 2 which is better to handle ‘n’ excluding 0s. That’s all. Enjoy!
Resources:
