How to highlight all error cells or the entire rows containing errors in Google Sheets?

Honestly, it’s not a tough task to write a custom conditional format rule to highlight all the error cells in Google Sheets. But it’s not easy to highlight all the error rows.

Assume cell F13 contains an #N/A! error. I want to highlight the entire row 13. Similarly, I want to highlight all the rows that contain errors in Google Sheets.

It’s possible in Google Sheets with a custom conditional format rule and I am detailing the same in this post.

Let me begin with Error functions (please refer to my Google Sheets function guide).

Google Sheets offers five info (category) types and two logical (category) types of functions to handle errors. They are;

Error Function Category – Info

1. ERROR.TYPE ✔
2. ISERR
3. ISERROR ✔
4. ISNA
5. TYPE ✔

Error Function Category – Logical

1. IFERROR ✔
2. IFNA

See the tick marks against some of the functions. You can use those functions to highlight all the error cells in Google Sheets.

I will explain this first. Then we can go to the main topic of how to highlight all error rows (the rows containing errors) in Google Sheets.

Highlight All Error Cells in Google Sheets

Use either of the below conditional format rules to highlight all error cells in a sheet.

Different Conditional Error Format Rules (Custom Formulas)

There are four formulas. Use any of the ones to highlight total error cells.

=error.type(A1)>0
=iserror(A1)=true
=type(A1)=16
=iferror(A1,true)

I know all of you are well familiar with applying a custom rule in Google Sheets Conditional formatting. Still, I am explaining the steps.

First of all select all the cells. Use Ctrl+A (Windows) or ⌘ + a (Mac).

Conditional formatting is a menu option. It’s under the Format menu. So after selecting all the cells or the array you want, go to Format > Conditional format.

Then follow the settings below. I am going to use the first formula to highlight all the cells containing errors.

If you set the font color as well as the background cell color to white, then all the error values will be made hidden from the views.

Highlight All Error Rows in Google Sheets

By placing a dollar sign before the column letter, for example, $A1 instead of A1, we can extend the row highlighting from a single cell to that entire row.

The same topic detailed here – Highlight an Entire Row in Conditional Formatting in Google Sheets.

It, I mean placing the dollar sign, alone won’t work to highlight all error rows. Then?

We must use the dollar sign but we may also use a combination formula to highlight all error rows in Google Sheets.

I have suggested four functions above to highlight error cells. Among them, we can use all the functions except the TYPE function.

There are three functions other than the TYPE. They are ERROR.TYPE, ISERROR, and IFERROR.

We can use the function IFERROR as a combination with the other two functions. Here are those combinations.

Recommended Error Function Combinations

iferror(ISERROR($A1:1))
iferror(ERROR.TYPE($A1:1))

I am using the first combination to write a rule to highlight all error rows in Google Sheets.

Some of you may be interested to know why I have excluded the function TYPE. It’s because this function only takes a single value as the argument, not a total row.

Formula to Conditional Format All Error Rows in Google Sheets:

=ArrayFormula(mmult(n(iferror(ISERROR($A1:1))),sequence(columns($A1:1),1)^0))

As I have detailed above, please select all the cells (rows and columns) before key this rule in conditional formatting.

How this Custom Rule Works?

I have used the first row as the reference in my formula which is $A1:1.

Since I have omitted the end column letter, the reference takes infinite columns in the first row.

I mean the above reference is equal to selecting the entire row # 1.

Return 1 If Cell Value is an Error

Take a look at this formula. If you want to understand what this formula returns, insert this in cell A2.

=ArrayFormula(n(iferror(ISERROR($A1:1))))

As you can see the formula returns 1 if the cell value is an error, else 0.

You can use =ArrayFormula(ISERROR($A1:1)*1) as an alternative to the above combo.

If the sum of the above output is 1 or >1, that means the row # 1 contains an error.

How to sum these error values then?

We can use MMULT.

Syntax: MMULT(matrix1, matrix2)

Replace ‘marix1’ with either of the just above formulas like;

=ArrayFormula(mmult(ISERROR($A1:1)*1,matrix2))
or
=ArrayFormula(mmult(n(iferror(ISERROR($A1:1))),matrix2)).

The ‘matrix2’ will be sequence(columns($A1:1),1)^0.

Scroll up to see the formula to conditional format all error rows in Google Sheets. Actually that formula returns the count of errors in row # 1.

If the error count is 1 or greater than 1, Google Sheets highlight row # 1.

This rule will be automatically applied to all rows since we have used absolute cell reference (dollar sign) in reference ($A1:1).