RowPilot · Guide
How to check a spreadsheet before entering it into a form
A single bad row can stop you midway through a data entry job. Spending a few minutes checking your sheet first means fewer rejected forms and less rework. These formulas work in both Excel and Google Sheets. Change the cell references to fit your columns.
Find problems with formulas
| Problem | Formula | Bad result |
|---|---|---|
| Empty required cell | =COUNTBLANK(A2:D2) | Above 0 |
| Duplicate ID or email | =COUNTIF(B:B,B2) | Above 1 |
| Email with no @ | =ISNUMBER(SEARCH("@",C2)) | FALSE |
| Too many characters | =LEN(D2) | Over the form's limit |
| Extra spaces | =LEN(D2)=LEN(TRIM(D2)) | FALSE |
The email formula only catches the most obvious mistake. An address can contain an @ and still not work.
Compare your values with the form
- Dropdowns: copy the form's choices into a spare column and use them as a dropdown list in your sheet (Data validation in both Excel and Google Sheets). This stops "USA" appearing where the form wants "United States".
- Number limits: try the form with a high and a low value to find what it accepts.
- Dates: make sure the whole column uses one format. A mix of day-first and month-first dates is hard to spot. See the guide to dates and numbers.
Check against the form itself
Formulas only know your sheet. RowPilot's Check your rows step reads the form you've opened and compares every row with what that form states, before anything is filled. It lists each problem by row:
- Empty cells in required fields, and required fields with no column
- Email addresses in the wrong format
- Dates and numbers it can't read, and numbers outside the form's limits
- Values that aren't among a dropdown's or radio button's choices
- Text that's too long or doesn't match a format the form requires
- Files that aren't in the folder you chose
Click a row in the list to jump to it. Then fix your sheet and load it again. Do this before you start filling, because changed data starts a new progress count.
What no pre-check can catch
- Rules on the website's server: a form may only reject a value after you submit, such as an account number it doesn't recognise. Pre-checks only see the rules the page states.
- Duplicates: RowPilot doesn't look for repeated rows, so use the
COUNTIFformula above. - Correct-looking wrong data: a valid email for the wrong person passes every check. That's why you should still review each form before you submit it.
Try it on your own form. RowPilot is a Chrome extension. The free plan covers up to 25 rows per file from a CSV or pasted rows, and every install starts with a 7-day trial of Pro.
Add to Chrome