How to Highlight Entire Error Rows in Google Sheets

Published on

There is no built-in rule to highlight entire error rows in a range in Google Sheets. To achieve this, you need to use a custom formula.

This tutorial answers:

  • How to highlight an entire row if one of the cells in that row contains an error.
  • How to apply this to all rows in the range.

The errors can be any type, including #N/A, #DIV/0!, #REF!, and #VALUE!.

That’s exactly what we’ll cover in this tutorial.

Example of Highlighting Entire Error Rows in Google Sheets

Assume you have the following sample data:

Google Sheets table with sample data, showing entire rows highlighted in red where errors like #N/A and #DIV/0! are present

You can use the following custom formula to highlight all error rows in the data, which in this example are rows 4, 7, and 9. The formula will highlight the entire row if any cell in that row contains an error.

=ArrayFormula(XMATCH(TRUE, ISERROR($B3:$G3)))

Steps to Apply the Formula:

  1. Select the range B3:G10.
  2. Click Format > Conditional Formatting to open the Conditional format rules panel.
  3. Under Format Rules, select Custom formula is.
  4. Copy and paste the above formula.
  5. Choose a fill color, such as red.
  6. Click Done.
Google Sheets Conditional Formatting panel with custom formula rule applied to highlight entire rows containing errors

This will highlight all error rows in the selected range.

Adjusting the Formula for a Different Range

Your data range may not be the same as in the example. In that case, you can easily modify the formula.

For example, if your range is A1:Z1000, the formula would be:

=ArrayFormula(XMATCH(TRUE, ISERROR($A1:$Z1)))

In the Conditional format rules panel, set the Apply to range as A1:Z1000.

Breaking Down the Formula That Highlights Entire Error Rows in Google Sheets

The rule works as follows:

  • The ISERROR($B3:$G3) function checks for errors in each cell within the row and returns an array of TRUE and FALSE values.
  • The XMATCH(TRUE, ISERROR($B3:$G3)) function finds the first occurrence of TRUE (an error) in the row.
  • If an error is found, XMATCH returns its relative position within the array as a number, which is interpreted as TRUE in the conditional formatting rule, causing the entire row to be highlighted.
  • Since we use absolute column references ($B:$G) and relative row references, the rule applies to all rows in the selected range (B3:G10).

That’s how you can highlight entire error rows in Google Sheets.

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.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

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.