Highlight Min Excluding Zero and Blank Cell in Google Sheets

Published on

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;

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.

  1. How to highlight min value excluding zero and blank in a column range in Google Sheets.
  2. 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:

Highlight Min Excluding Zero and Blank in a Column - Google Sheets

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:

Custom Rule - Where to insert formula in Google Sheets

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:

Highlight Min Excluding Zero and Blank in Each Row in Google Sheets

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:

Highlight Min Value in Each Row

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.

Max Related Conditional Formatting Tips

  1. How to Highlight Max Value in Each Row in Google Sheets.
  2. Conditional Format Max Two Values Based on Criterion in Google Sheets.
  3. Google Sheets – Highlight the Max Value in Each Group.
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.

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

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.