A custom rule using MIN function is not enough to highlight Min excluding zero/blank in a column/range/each row in Google Sheets.
The reason you need to specify two conditions to get the min value as MIN is known for returning 0 if there is either a 0 or blank in the MIN range. It’s quite annoying some times, right?
The two conditions to specify are;
- How to exclude zero values in Min in conditional formatting (this topic has partially covered in my earlier tutorial – How to Exclude 0 From MIN Function Result in Google Sheets)
- How to exclude blank cells in the Min value calculation in conditional formatting.
In this, the last point, i.e. excluding blank cells in the Min calculation, makes the task a little complex as Min treats blank cells as 0.
To specify the above two conditions and to return Min, we can use MINIFS + AND combo, or MIN + FILTER combo.
You May Like: Google Sheets Function Guide.
There may be other combinations to highlight Min value in a column range or each row. Since these two combinations are simple to read, let me proceed with that and ignore others, if any.
In this tutorial related to conditional formatting in Google Sheets, you will get two types of Min value related highlighting rules.
- How to highlight min value excluding zero and blank in a column range in Google Sheets.
- How to highlight min value excluding zero and blank in each row in Google Sheets.
Let’s first highlight min value in a range (it can be a single column, single row or a table) that excluding 0 and blanks.
Highlight Min Excluding Zero and Blank in a Column Range in Google Sheets
Screenshot # 1:
Custom Rules to Conditional Format Minimum Value (Excluding Blank and Zero) in a Column
As mentioned, I have two conditional format rules (formulas). The following two custom formula rules are for the column range A1:A (column A). You can use either of the formula rules.
Before that, see how to apply the formula as a custom rule in conditional formatting in Google Sheets?
Custom Rule – How to?
To insert the custom rule (formula) go to the Format menu and click (select) Conditional formatting. Then refer to (follow) the image below.
Screenshot # 2:
Get the two formula rules below.
AND + MINIFS Combo Rule (for a Column Range)
=and(A1<>"",A1=MINIFS($A$1:$A,$A$1:$A,">0"))The MINIFS formula returns the minimum value in column A. It only excludes 0 values. To exclude blank cells, I have used AND logical function.
It (the formula) works like this.
The formula tests whether cell A1 is not blank [A1<>""] and A1 is equal to the minimum value in the range A1:A [A1=MINIFS($A$1:$A,$A$1:$A,">0")] excluding 0s.
If both the test return TRUE (conditions satisfy the test), then the AND would return TRUE. This test automatically happens in each and every cell below (A2, A3, A4…).
Once the formula returned TRUE that means, that particular cell contains the min value excluding zero values and blanks.
In conditional formatting no need to specify A1 satisfies this, A2 satisfies this like that. It will automatically happen unless you use absolute cell reference like $A$1 or $A1 instead of A1. The same has illustrated below (screenshot # 3).
Screenshot # 3:
Note: The rule (formula) must be entered within the specific field in the conditional format panel (please refer to Screenshot # 2). The above image is for explanation purposes.
MIN + Filter Combo Rule (for a Column Range)
=A1=min(filter($A$1:$A,$A$1:$A>0))For me, this conditional format rule seems simple to understand and better than the above. So I prefer this formatting rule to highlight Min value excluding zero and blanks in a column/range in Google Sheets (not in Excel).
Looking for the formula explanation? Here you go!
The Filter formula filters the range excluding 0 and blank (no need to specify blank separately as a condition in the filter here). The MIN then returns the minimum value of the filtered range.
The conditional format tests cell A1, A2, A3 … for that Min value. If the formula returns TRUE, then that cell got highlighted.
Conditional Format Min Excluding Zero and Blank in Each Row in Google Sheets
Screenshot # 4:
Custom Rules to Conditional Format Minimum Value (Excluding Blank and Zero) in Each Row
Below, again you can find two formulas (custom formatting rules) to highlight Min excluding zeros and blank cells in each row in Google Sheets.
The formulas are identical to the earlier two formulas. But this time there is one difference, that is in the use of relative/absolute cell reference in the formula.
AND + MINIFS Combo Rule (for Each Row)
=and(C2<>"",C2=MINIFS($C2:$E2,$C2:$E2,">0"))What is the difference between this formula with the earlier similar formula?
Since we want to highlight the min values in each row (not in a single row or column), we must use $C2:$E2 instead of $C$2:$E$2. Then only the conditional format will apply to each row.
- $C2 – Column absolute (fixed) but the row is relative (changing).
- $E2 – Same here, I mean the column reference is absolute (fixed) but the row reference is relative (changing).
That means in conditional formatting $C2:$E2 changes like $C2:$E2, $C3:$E3, $C4:$E4 and so on up to the last row in the selected range C2:E15 (please refer to the image below).
Screenshot # 5:
MIN + Filter Combo Rule (for Each Row)
Here is the second formula (my favorite) to highlight Min in each row in Google Sheets.
=C2=min(filter($C2:$E2,$C2:$E2>0))Try to read/understand this last formula rule yourself. The above relative/absolute cell reference is relevant here also.
