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

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

At a glanceSetting up validation in the shared trackerSix setup steps for a 12-person purchase request sheet
  1. Create the Lists sheetDepartments in A2:A7, statuses in B2:B5
  2. Enter each validation ruleData > Data Validation for columns A-F
  3. Add input and error messagesFill Input Message and Error Alert tabs
  4. Add the helper formula in G2Fill down, filter G for non-blank rows
  5. Circle invalid dataQuick visual check of existing entries
  6. Protect Lists sheet and column GStops edits to lists and the checker

Example for: a purchase request tracker in Excel 365 shared by 12 people, columns A-F.

1. Validation rules
ColumnRule typeSettings or formulaInput messageError alert style
A Request IDCustom=AND(LEN(A2)=8,LEFT(A2,3)="PR-",ISNUMBER(--RIGHT(A2,5)),COUNTIF($A:$A,A2)=1)Request ID format - Use PR- plus 5 digits, e.g. PR-00412. Must be unique.Stop
B DateDateBetween 1/1/2024 and =TODAY()Request date - Enter as dd/mm/yyyy. No future dates.Stop
C DepartmentList=Lists!$A$2:$A$7Department - Pick from the dropdown.Stop
D AmountDecimalBetween 0.01 and 50000Amount (GBP) - Numbers only, no £ sign. Over 50,000 needs CFO form.Stop
E Requester emailCustom=AND(ISNUMBER(FIND("@",E2)),ISNUMBER(FIND(".",E2,FIND("@",E2))),ISERROR(FIND(" ",E2)))Work email - e.g. j.patel@northwind.co.ukWarning
F StatusList=Lists!$B$2:$B$5 (Open, Approved, Rejected, Paid)Status - Only finance changes this to Paid.Stop
2. Helper column (G2, fill down)
=IF(COUNTA(A2:F2)=0,"",TEXTJOIN(", ",TRUE,
IF(OR(LEN(A2)<>8,LEFT(A2,3)<>"PR-"),"ID format",""),
IF(COUNTIF($A:$A,A2)>1,"Duplicate ID",""),
IF(OR(NOT(ISNUMBER(B2)),B2>TODAY()),"Date",""),
IF(COUNTIF(Lists!$A$2:$A$7,C2)=0,"Department",""),
IF(OR(NOT(ISNUMBER(D2)),D2<=0,D2>50000),"Amount",""),
IF(ISERROR(FIND("@",E2)),"Email",""),
IF(COUNTIF(Lists!$B$2:$B$5,F2)=0,"Status","")))
  • ID format and Duplicate ID: catch typos like PR412 and repeated IDs.
  • Date: catches dates stored as text and future dates.
  • Department and Status: catch spelling variants like "Mktg".
  • Amount: catches text such as "£1,200" and out-of-range values.
  • Email: catches entries missing an @.

To list bad rows on another sheet: =FILTER(Tracker!A2:G500,Tracker!G2:G500<>"","No invalid rows")

3. Setup steps
  1. Create a sheet named Lists and put departments in A2:A7 and statuses in B2:B5.
  2. Select A2:A500, then go to Data > Data Validation and enter each rule from the table.
  3. Fill in the Input Message and Error Alert tabs for each column.
  4. Add the helper formula in G2, fill it down, and filter G for non-blank rows.
  5. Use Data > Data Validation > Circle Invalid Data for a quick visual check.
  6. Protect the Lists sheet and column G.
4. Limits

Pasting values over cells skips validation, so check column G weekly. The email check catches obvious errors only.

Written by AI from this prompt. Your results will vary.

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

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets