How to Highlight the Smallest N Values in Each Row in Google Sheets

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.

Highlight Smallest N Values in Each Row in Google Sheets

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)))))
        &"$"
)
Format Rules - Where to Insert the SMALL Formula

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:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.