I'm just trying to clean up a list of email addresses that I exported from my ecommerce store and want to remove all of the junk data that can't be used.

I would think this would be way easier, but I've been searching the web all day trying to find the answer to how you systematically remove cells flagged as invalid by Data Validation, leaving a consolidated list of the rows only containing valid emails. I can flag the invalid cells/rows, but I can't 'sort by invalid' or 'remove all invalid cells'...why is this so complicated?

I do not want to write script as I'm not an engineer/programmer/dev, but if there's a SIMPLE option for laypeople, I'm dying to know. All of the material I've read and watched skirts right around this issue.

  • Craig, you said, "I can flag the invalid cells/rows, but I can't..." Let's start with what you CAN do. If you share a link to a spreadsheet showing the situation as far as you've gotten, it should be pretty easy to illustrate on the sheet how to do the "can't (yet)" part.
    – Erik Tyler
    Jul 1, 2021 at 0:01

1 Answer 1


Google Sheet doesn't include a built-in feature to remove rows having "invalidad data", in Google Sheets the "data validation" is done by cell and this prevent to enter a invalid value or marks the cell with a red triangle in the top-right corner. Removing rows with "invalid data" might be done, manually or automatically by using an add-on, Google Apps Script, the Sheets API.

If you don't to use a add-on, Google Apps Script or the Sheets API, here is one option:

  1. add a column with formula to return certain value for rows to be deleted
  2. apply a filter to show only the rows to be deleted
  3. delete the rows
  4. remove the filter

Here is another option:

Use a formula to return only the rows that you want to keep. For this you might use FILTER or QUERY functions. If you don't want to keep the source data, the do a copy-paste as value only first, then delete the source data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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