8

I have a Google sheet containing lots of data, and some of it doesn't respect the data validation rules (and the little red triangle appears). Since I have a lot of data, it's quite a long process to find all occurrences of invalid data. I assumed there would be a "jump to next invalid data" feature or something, but I can't find it. Am I missing anything?

  • It depends on what kind of data validation rule you have setup - can you elaborate first on what the rule/condition is that deems it invalid and causes the triangle? – Aurielle Perlmann Jun 18 '16 at 1:09
3

You can use filter view to show only rows containing an error.

On the column you want to check, create a filter, set filter option to "Filter by Condition" > "Custom formula is..." > "=iserror(indirect("r"&row()&"c"&column(),FALSE))"

where indirect("r"&row()&"c"&column(),FALSE) is just a fancy way of saying "this cell" (sorry, no easier way), and iserror() check if the value is invalid.

Here is an example

|improve this answer|||||
  • No, this question is not about errors but about data validation: iserror() checks if the value of the cell has an error, i.e. cannot be displayed, and not if it doesn't respect data validation rules, which are user-defined (e.g. value should be one of several fixed possibilities). – Socce Dec 16 '16 at 11:32
  • There is an easier way of saying "this cell": just directly refer to the first cell to which the formula applies, in a way that the formula could be "expanded" to the following cells. So in your example spreadsheet (where the filter is on column C, with data starting at C2), you can filter by the formula =iserror(C2). – Socce Dec 16 '16 at 11:35
0

"A:A" range contains data validation from "Sheet2!A2:A6"

Global Filter >> Column A >> Custom Formula

=COUNTIF(Sheet2!$A$2:$A$6,A2)<1

This will filter all data which doesn't match the "sheet2!A2:A6" range.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.