If min value in a row is 5, the normal conditional format formula will highlight all the numbers 5 in that row. To avoid that you may want to know how to skip duplicates in a min as well as small value highlighting in Google Sheets.

To know what happens in a normal min or small number highlighting across the rows, you can refer my post – How to Highlight the Smallest N Values in Each Row in Google Sheets.

First of all, I am going to share the logic behind highlighting min or small values skipping duplicates in Google Sheets.

Logic – Find the Cell IDs of the Smallest Numbers and Match It with the Cell IDs in the Format Range

The logic of how to skip duplicates in a min or small value highlighting in Google Sheets lies in the above subtitle. I am going to explain that logic.

Assume one of my row (B2:H2) contains the below numbers.

In this row, the min value is 1. A normal MIN conditional format formula,i.e. =B2=min($B2:$H2), will highlight all the numbers 1 in this row which are in column 1 (B2), column 4 (E2), column 6 (G2) and column 7 (H2).

But what my new formula will do is like this.

Min or Small 1 will highlight the number in column 1, small 1 and 2 will highlight the numbers in column 1 and column 4, small 1, 2, and 3 will highlight the numbers in column 1, column 4, and column 6.

The below images better explain what’s skipping duplicates in min | small value highlighting in Google Sheets.

Example to Highlight Min | Small 1 Skipping Duplicates in Google Sheets:

Highlight Small 1 and 2 Values Skipping Duplicates in Google Sheets (Example):

Example to Highlight/Conditional Format Small 1, 2, and 3 Skipping Duplicates:

There will be a total of 3 rules (formulas). 1 for small/min 1, another for small 2, and the last one for small 3. You can get that under three subtitles below with a detailed explanation.

Formula to Skip Duplicates in Min or Small 1 Highlighting in Google Sheets

In conditional formatting, no need to use multiple formulas for each row. You just need to specify the formula to highlight the min excluding blank for the first row in the range. Keeping this in mind, let’s start writing the formula.

To get the min (small 1) value in the first row of the range, we can use the following SMALL formula.

=small($B2:$H2,1)

Result: 1

As per our logic, we want to find the cell address of the above formula output, right? How can we get that?

It’s actually quite tricky. Please follow the below step-by-step instructions.

Conditionally Filtering (Small 1) Cell IDs Across the Row

First, we must filter the cell addresses of the range B2:H2 wherever the value in the range B2:H2 matches min/small 1.

The below ADDRESS formula can return all the cell addresses in the range B2:H2.

=ArrayFormula(address(row(),column($B$2:$H$2)))

Result (when you insert the formula in row # 2, for example in cell J2):

The following is the said Filter formula (the ArrayFormula removed from the Address function because within Filter it’s optional to use).

=FILTER(
     address(row(),column($B$2:$H$2)),
     $B2:$H2=small($B2:$H2,1),
     len($B2:$H2)
)

The above filter formula filters the cell IDs wherever the values in those cells match the small/min 1 [$B2:$H2=small($B2:$H2,1)] and also the cell is not blank [len($B2:$H2)].

Result:

Index to Extract the Cell IDs Correspond to the Smallest 1 Number

The cell B2 contains the smallest 1 value in the range B2:H2. We just need to extract the first cell address from the above result.

Let’s see how can we do that using the Index function. Please have a look at the syntax of this function.

INDEX(reference, [row], [column])

Arguments:

• reference: the filter formula above.
• row (row offset): 0
• column (column offset): 1

=index(
     FILTER(
        address(row(),column($B$2:$H$2)),
        $B2:$H2=small($B2:$H2,1),
        len($B2:$H2)
     ),
     0,1
)

Result: $B$2

Highlighting Rule (Avoid Duplicates in Min)

To skip duplicates in the min | small value highlighting in B2:H2 in Google Sheets we need to just match the cell addresses of the range B2:H2 with the above result.

But in conditional formatting no need to specify B2:H2. Cell B2 is enough like =cell("address",B2). I’ll come to that.

Formula Rule # 1:

=cell("address",B2)=
index(
     FILTER(
        address(row(),column($B$2:$H$2)),
        $B2:$H2=small($B2:$H2,1),
        len($B2:$H2)
     ),
     0,1
)

Even though the above formula is written for the range B2:H2, in conditional formatting we can use this for more than one row like B2:H9 (a specific range) or B2:H (infinite range).

How to insert this rule?

Select the range B2:H9. Click Format > Conditional formatting. Insert the above rule in the blank field below the label ‘Custom formula is’.

Highlight the Small 2 Numbers Skipping Duplicates in Row Wise in Google Sheets

Here we can learn how to skip duplicates in small 2 value highlighting in Google Sheets. The formula is almost the same as the above. Of course, there are two changes.

The first change is in the Small formula within the Filter to filter the cell IDs correspond to the smallest 2 values.

=FILTER(
     address(row(),column($B$2:$H$2)),
     $B2:$H2=small($B2:$H2,2),
     len($B2:$H2)
)

The second change is in the index column offset. Earlier we have used 1 as the column offset.

Here we need to first check whether the smallest 1 values is equal to the smallest 2 value. If yes, offset 2, else offset 1. Didn’t get?

See the data in B2:H2 below that followed by the smallest 2 filtered output.

Filtered Output (if the filter formula is in row # 2, example when in cell J2):

Since small 1 ($B$2) and small 2 ($E$2) are the same numbers (1), we must extract the second matching cell ID, i.e. $E$2.

Here is the IF logical formula to use as Index column offset.

if(small($B2:$H2,2)=small($B2:$H2,1),2,1)

So we can use the following formula to highlight the smallest 2 values excluding any duplicate in Google Sheets.

Formula Rule # 2:

=cell("address",B2)=
index(
     FILTER(
        address(row(),column($B$2:$H$2)),
        $B2:$H2=small($B2:$H2,2),
        len($B2:$H2)
     ),
     0,if(small($B2:$H2,2)=small($B2:$H2,1),2,1)
)

Conditional Format Small 3 Values without Duplicates in Row Wise in Google Sheets

Here again the changes are in the Small formula and the column offset in Index.

Change 1:

=FILTER(
     address(row(),column($B$2:$H$2)),
     $B2:$H2=small($B2:$H2,3),
     len($B2:$H2)
)

Change 2: If logical test in column offset.

if(
     small($B2:$H2,3)=small($B2:$H2,1),3,
     if(small($B2:$H2,3)=small($B2:$H2,2),2,
     1)
)

If you decode the ‘change 2’ formula, you can read it as;

If the smallest 3 value is equal to the smallest 1 value, offset 3, if the smallest 3 value is equal to the smallest 2 value, offset 2, else offset 1.

Formula Rule # 3:

=cell("address",B2)=
     index(
        FILTER(
           address(row(),column($B$2:$H$2)),
           $B2:$H2=small($B2:$H2,3),
           len($B2:$H2)
        ),
        0,
        if(
             small($B2:$H2,3)=small($B2:$H2,1),3,
             if(small($B2:$H2,3)=small($B2:$H2,2),2,
             1)
        )
     )

That’s all about how to skip or exclude duplicates in min | small value highlighting in Google Sheets in a horizontal range.

Demo Sheet

Resources:

• How to Highlight Max Value in a Row in Google Sheets.
• How to Highlight Largest 3 Values in Each Row in Google Sheets.
• Conditional Format Max Two Values Based on Criterion in Google Sheets.
• Highlight Min Excluding Zero and Blank Cell in Google Sheets.