Set Up Data Validation Rules That Stop Bad Entries in a Shared Excel Sheet
You get a column-by-column validation plan with allowed values, custom formulas, input and error messages, plus a helper formula that flags rows already holding bad data.
Prompt
Act as a senior Excel analyst who builds shared trackers for teams.
Your job: design data validation for my shared sheet so people cannot enter bad data, and give me a way to find invalid rows that are already there.
First, ask me these questions in one message and wait for my answers:
1. Paste the column headers and 5-10 sample rows, including any messy ones.
2. Excel or Google Sheets, and which version?
3. Which columns are required, which must be unique, and are there fixed lists such as departments or statuses?
Rules:
- Give every column a rule. Use the Data Validation built-in type such as List, Date or Decimal when it is enough, and a custom formula only when it is needed.
- Write every custom formula for row 2 with correct $ anchoring, ready to paste.
- Keep input message titles under 32 characters and the message text under 255 characters. Make each message say what a valid entry looks like.
- Put list values on a separate Lists sheet, not typed into the rule.
- Build one helper-column formula that names every problem in a row and stays blank when the row is clean.
Output format:
1. Table with these columns: Column | Rule type | Settings or formula | Input message | Error alert style
2. Helper column formula with a one-line explanation of each check
3. Setup steps, numbered
4. One line on limits, such as pasted data skipping validation.Example output
Tips and common edits
Why this works
- Asking for sample rows and fixed lists first means the rules match the user's real columns instead of generic examples.
- Requiring a helper formula catches bad data already in the sheet, which validation alone ignores.
- Character limits and anchoring rules mean the messages and formulas paste in without errors.
Use this before sharing a tracker or after cleanup in ChatGPT, Claude or Gemini. Have your headers, a few real sample rows and any fixed lists ready to paste.
Common edits
- Ask for Google Sheets syntax and dropdown chips instead of Excel steps.
- Ask for conditional formatting that highlights invalid cells in red as a second layer.
Prompt details
- By
- Prompts-GPT editors
- Published
Keep exploring
More like this
Design a Clean Spreadsheet Layout for a Tracker or Budget That Scales
You get a complete sheet blueprint with tabs, column specs, data types, dropdown lists, summary formulas and setup steps that keep working as rows grow.
Set Up Dependent Dropdown Lists in Excel or Google Sheets Step by Step
Get click-by-click setup for dropdowns and dependent dropdowns in your app, with exact named ranges, INDIRECT or FILTER formulas, and rules for blank or invalid entries.
Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel
You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.
Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts
You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.
Build an Invoice Template in Excel or Google Sheets with Automatic Totals
You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.
Calculate Year-over-Year and Month-over-Month Growth in Excel Without Misleading Percentages
You get a growth table, exact Excel formulas that handle zero and negative baselines and partial months, plain-language takeaways, and a list of percentages that would mislead.