Data Validation Manager
Free Google Sheets Add-on

Red triangles only tell you about one cell at a time. Here is how to get a list of every cell that fails data validation, on every tab.

How to Find All Invalid Entries in Google Sheets at Once

Data validation in Google Sheets is good at stopping bad input as it is typed, and much worse at telling you what slipped through. A cell that breaks a rule gets a small red triangle in its top-right corner, and that is the whole report: no list, no count, no view across tabs. On a workbook with a dozen tabs, "find all the invalid entries" turns into scrolling and squinting. This guide covers the methods that actually work, from a helper formula to a script that uses the same check as the triangles, and finishes with a one-click option.

Why a validated sheet still contains invalid entries

Knowing how invalid values get in tells you where to look.

One detail that matters for every method below: blank cells count as valid. A rule never flags an empty cell, so the problem is always a non-blank value that does not fit.

Method 1: Look for the red triangles

For a single short tab, the honest answer is to look. Zoom out and scan the validated columns for red triangles; hover over one to read the warning, then click the cell and open Data > Data validation to see which rule it fails.

This stops working quickly: the triangles are tiny, hidden rows and filtered views hide them altogether, and you repeat the scan on every tab. Treat it as a spot check, not an inventory.

Method 2: A helper column that re-tests the rule

If you know what the rule says, re-state it as a formula in a spare column and filter on the result, one formula per validated column, filled down:

Wrap each one in IF(B2="", FALSE, ...) so blanks stay out of the way, then filter for TRUE. The helper column gives you a real list you can sort, count and work through.

The cost is a second copy of every rule. If someone edits the dropdown in Data > Data validation and forgets the formula, the helper column starts lying. It also covers one range at a time, so 30 validated ranges need 30 helper columns, and custom-formula rules have to be translated by hand.

Method 3: Highlight with conditional formatting

The same formulas work as a highlight instead of a column. Select the validated range, open Format > Conditional formatting, choose Custom formula is, and enter the test for the top-left cell, for example =AND(B2<>"", COUNTIF(Lists!$A$2:$A$20, B2)=0). Pick a fill color and every non-matching cell lights up.

A full orange cell is far harder to miss than a triangle. The limits are those of the helper column, though: one rule per range, a copy of the logic that can drift, and still no list and no count. You still open every tab and look.

Method 4: Ask Google Sheets itself with Apps Script

Google Sheets exposes its own validity verdict to Apps Script. range.isDataValid() checks the top-left cell of a range against its rule, and range.isDataValidForAll() returns a grid of results for the whole range: true for a valid cell, false for an invalid one and null for a cell with no rule. Both are documented in the Apps Script Range reference. Because this is the check that draws the red triangles, the result agrees with the sheet, including custom-formula rules you would struggle to re-create by hand.

Open Extensions > Apps Script, paste the function below and run it. It only reads and logs, and changes nothing.

function listInvalidCells() {
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function (sheet) {
    var rows = sheet.getMaxRows(), cols = sheet.getMaxColumns();
    var valid = sheet.getRange(1, 1, rows, cols).isDataValidForAll();
    for (var r = 0; r < rows; r++) {
      for (var c = 0; c < cols; c++) {
        if (valid[r][c] === false) {
          Logger.log(sheet.getName() + '!' + sheet.getRange(r + 1, c + 1).getA1Notation());
        }
      }
    }
  });
}

The execution log then lists every invalid cell on every tab as Sheet!A1 addresses. Blank cells never appear, because Google counts them as valid. The first run asks you to authorize the script; that prompt is normal for a script you wrote yourself.

The limits are practical. The log is plain text, so you still copy addresses out and navigate to them one by one, large tabs take a while because the script walks every cell, and the people who most need the list often will not open the script editor.

Or check every tab from a sidebar

Data Validation Manager, a free Google Sheets add-on, turns Method 4 into one click. Open Extensions > Data Validation Manager > Launch Data Validation Manager and press Find invalid entries. The add-on checks every tab using Google's own validity check (the same verdict as the red triangles) and shows the results grouped by tab and by rule. Each cell address is a chip that jumps to the cell so you can fix the value, rules with problems get a badge such as "4 invalid" in the rule list, and Check again re-runs the check after you have cleaned up. As with the script, blank cells are not reported.

The add-on never changes cell values, so the fixing is still done in the sheet. What it removes is the hunting.

What to do with the list

Once you can see the invalid cells, each is a small decision:

  1. Fix the value when it is a typo or an outdated spelling. Edit > Find and replace with "All sheets" selected handles a renamed option across the whole file in one go.
  2. Fix the rule when the value is right and the rule is too narrow. Add the missing item to the list in Data > Data validation, or widen the number range. The triangle disappears as soon as the rule accepts the value.
  3. Fix the source when the invalid values keep coming back from a formula or an import. Validation cannot stop them; the upstream sheet or the formula has to change.

If the rule itself is broken, for example a dropdown pointing at a list range that does not exist because it was pasted in from another file, see how to copy a data validation rule to another spreadsheet without breaking it.

Frequently asked questions

My rule is set to reject the input. How can there be invalid entries?

Rejecting only applies when someone types or pastes into the cell. Values that were there before the rule, values produced by formulas and values that arrive through imports are never checked at entry time. They are evaluated afterwards, which is why they carry the red triangle, but nothing stopped them from landing.

Do empty cells count as invalid?

No. Google Sheets treats a blank cell as valid under every rule type, so neither the red triangles, nor isDataValidForAll(), nor Data Validation Manager will report one. If "required" is what you need, a helper formula such as =B2="" with a filter finds the gaps.

Is there a built-in command that lists all invalid cells?

Not in Google Sheets itself. There is no menu item or filter for "cells that fail validation", and the Data > Data validation panel lists the rules of the current tab, not the cells that break them. The helper column, the highlight and the script above are the native workarounds; a sidebar add-on is the shortcut.

Back to all guides