# 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.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Advanced
- Tags: Data & Analytics, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/set-up-data-validation-rules-in-excel-to-stop-bad-entries

## Prompt

```text
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.
```

## What to fill in

- Excel 365 / older Excel / Google Sheets / SharePoint list

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.