How to Find Every Formula Error in Google Sheets

A single #DIV/0! in a price column can push the same error into every average and total that uses it. Scrolling for red triangles works on a small tab. On a big one you want Sheets to show you the cells. These three methods are free and take a minute each. The examples use a small sales tab in A1:F7: one row per product (Mugs, Posters, Tote bags, Stickers) with units sold, revenue and price per unit, a total row, and a lookup for the poster price. Posters sold 0 units, so its price per unit divides by zero. Change the range to fit your sheet.

1. Highlight every error cell

Select the range, open Format, then Conditional formatting. Under "Format cells if", pick "Custom formula is" and enter:

=ISERROR(A1)

Use the top-left cell of your range in the formula. Choose a fill colour and click Done. Every cell that shows an error now stands out, and the colour goes away by itself once the cell is fixed.

2. Count the errors

In an empty cell outside the range:

=SUMPRODUCT(--ISERROR(A1:F7))

This gives the number of error cells. To count one type only, swap the test. ISNA counts only #N/A, which usually means a lookup did not find its value:

=SUMPRODUCT(--ISNA(A1:F7))

3. List the address of every error cell

This one formula returns the addresses as text, for example F2, D3, D6, D7:

=TEXTJOIN(", ",TRUE,ARRAYFORMULA(IF(ISERROR(A1:F7),ADDRESS(ROW(A1:F7),COLUMN(A1:F7),4),"")))

To check another tab, put its name in front of each range, for example 'Q3 sales'!A1:F7. The list reads row by row, left to right.

Which cell started it?

The list above shows every cell with an error, but most of them only pass on an error from somewhere else. In the example, D6 averages the price column and D7 adds tax to D6. Both show #DIV/0! only because D3 divides revenue by zero units. Fix D3 and the other two clear.

To trace it by hand, click an error cell and read its formula in the bar at the top. Follow each reference that also shows the same error, and repeat until you reach a cell whose inputs are all fine. That is where the problem starts. Sheets passes an error on without changing its type, so a #N/A usually does not come from a #DIV/0! cell.

Error	What it usually means
`#REF!`	The formula points to a cell, row or tab that was deleted.
`#N/A`	A lookup did not find its value. Check spelling, extra spaces, or numbers stored as text.
`#DIV/0!`	Division by zero or by an empty cell.
`#VALUE!`	Text where a number is expected.
`#NAME?`	A misspelled function or a named range that does not exist.

A note on IFERROR

Wrapping a formula in IFERROR(…, "") makes the error disappear, but it does not fix the cause. In the example, wrapping D3 turns the average in D6 into 14.33, a number that looks fine and quietly leaves out the Posters row. Fix the starting cell when the numbers matter, and keep IFERROR for cells where a blank really is the right answer.