Highlight Smallest N Values in Each Row – Google Sheets

Before highlighting the smallest N values in a row—or in each row—you should decide how to handle duplicates and ties at the Nth position. The table below outlines the options:

ValueStrictly Lowest NLowest N + Ties of NthDistinct Lowest N
1
1
2
2
3

Here are the custom formula rules to highlight the smallest N values in a row or each row in Google Sheets.

Rule 1: Strictly Lowest N in a Row or Each Row

=AND(LEN(B2), XMATCH(CELL("address", B2), SORTN(TOCOL(ADDRESS(ROW($B2:$J2), COLUMN($B2:$J2))), 3, 0, TOCOL($B2:$J2), TRUE)))

This formula highlights the smallest N values in each row, excluding ties at the Nth position.

Replace $B2:$J2 with your desired row range, B2 with the first cell in the range, and 3 with your preferred N value.

You can apply this to a single row (e.g., $B2:$J2) or multiple rows (e.g., $B2:$J8) without modifying the formula. Just update the “Apply to range” field accordingly in the Conditional Formatting Rules panel.

Example:

  1. Select the range B2:J8
  2. Go to Format > Conditional formatting
  3. Under Format rules, select Custom formula is, and paste the formula
  4. Choose a formatting style and click Done
Smallest N values highlighted per row

How it works:

  • TOCOL(ADDRESS(ROW($B2:$J2), COLUMN($B2:$J2))) returns the cell addresses of each value in the row.
  • SORTN(..., 3, 0, TOCOL($B2:$J2), TRUE) gives the addresses of the lowest 3 values.
  • XMATCH checks if the current cell’s address is among them.
  • AND ensures the cell isn’t blank and that it’s one of the lowest 3.

This works row-by-row because the formula uses absolute column references and relative row references.

Rule 2: Lowest N + Ties of Nth in a Row or Each Row

=RANK(B2, $B2:$J2, TRUE)<=3

Use this to highlight the lowest 3 values plus any values tied at the 3rd lowest spot.

Update $B2:$J2 with the first row of your data and B2 with the first cell in that row.

Follow the same steps as in Rule 1 to apply this rule.

Highlighted smallest N values including ties

How it works:

  • The RANK function assigns the smallest value a rank of 1, the next smallest 2, and so on.
  • Tied values share the same rank, and subsequent ranks skip accordingly.
  • All cells with rank ≤3 get highlighted.

Rule 3: Distinct Lowest N in a Row or Each Row

=AND(XMATCH(B2, SORTN(TOCOL($B2:$J2), 3, 2, 1, TRUE)), COUNTIF($B2:B2, B2)=1)

Use this to highlight the smallest 3 distinct values in each row—ignoring duplicates.

Replace $B2:$J2 with the first row in your data, B2 with the first cell, and $B2:B2 with a corresponding range.

Distinct smallest N values highlighted in rows

How it works:

  • SORTN(TOCOL($B2:$J2), 3, 2, 1, TRUE) returns the smallest 3 unique values.
  • XMATCH(B2, ...) checks if the current cell is among them.
  • COUNTIF($B2:B2, B2)=1 ensures only the first occurrence of each value is considered.
  • AND returns TRUE only if both conditions are met.

Resources

This tutorial has shown multiple ways for how to highlight the smallest N values in each row in Google Sheets. Depending on your preference—strictly N, tied Nth, or distinct—you can choose the right rule for your spreadsheet.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

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.