Set up data validation rules in Excel to stop bad entries
Creates a validation framework for your spreadsheet: allowed values, dropdowns, check columns, and an exceptions report that catches bad data.
The prompt
Help me set up a data validation framework for a spreadsheet that several people fill in or that I import regularly.
What the sheet holds: [e.g. "project tracker updated by 10 managers", "monthly vendor data import", "expense claims log"]
Columns: [PASTE headers and 10-20 sample rows, including any bad entries you've seen]
Rules I know: [e.g. "status must be one of five values", "end date after start date", "cost center from the finance list", "email must be filled"]
Problems we keep having: [e.g. "free-text statuses", "dates typed as text", "duplicate IDs"]
My tool: [Excel 365 / older Excel / Google Sheets / SharePoint list]
Build a validation rule set:
| Column | Rule type (required, list, range, format, uniqueness, cross-field) | Rule | How to enforce | Error message to show |
Then give me:
1. Prevention: Data Validation settings for each column (dropdowns from a Lists tab, whole number or date ranges, custom formulas such as =COUNTIF($A:$A,A2)=1 for unique IDs), plus input messages that tell people what to enter.
2. Detection: a check column with one formula that returns OK or the reason a row fails, for data that gets pasted in (pasting bypasses Data Validation).
3. Visibility: conditional formatting to highlight failing cells, and a summary of failures by column and by person or source.
4. Process: who fixes what, how often to review, and how to protect the sheet structure (locked headers, protected formula columns) without blocking entry.
5. Edge cases my rules may wrongly reject, so we don't block legitimate data.
Keep the rule set short enough that people won't work around it - flag any rule that may be too strict.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- Excel 365 / older Excel / Google Sheets / SharePoint list
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Explain a dataset or report in plain business languageExplains what a dataset or report contains, what each column or metric means, and what it shows, in words anyone can follow.
- Find what my dashboard is not telling meReviews a dashboard for blind spots, missing comparisons, vanity metrics, and misleading views, and suggests what to add.
- Flag impossible or suspicious values in my dataChecks your data against logic and business rules for impossible, placeholder, and implausible values, and gives reusable validation rules.
- Create a data-quality checklist for a recurring report or datasetA reusable pre-publish checklist with checks, formulas, pass thresholds, owners, and what to do when a check fails.