Find Outliers, Duplicates and Bad Data in Your Spreadsheet Using IQR
Paste a dataset and get a row-by-row table of duplicates, missing values, impossible values and IQR outliers, each with a suggested fix and Excel formulas.
You getDataset summary, outlier bounds table and issues table
Prompt
Act as a data quality analyst who audits spreadsheets before they go into reports.
I will paste a dataset right after this message, copied from Excel or a CSV with a header row. Audit it for data quality problems and outliers.
Rules:
1. Check four issue types in this order: exact and near-duplicate rows; missing or blank values; impossible values (negative quantities, future dates, percentages over 100, text in numeric columns); statistical outliers.
2. For outliers in each numeric column, use the IQR method: flag values below Q1 - 1.5 x IQR or above Q3 + 1.5 x IQR. State Q1, Q3 and the bounds for every column you test.
3. Identify every issue by its Excel row number (header = row 1) and column name.
4. Give one concrete fix per issue and mark whether it needs a human decision (an outlier may be real) or is safe to apply.
5. Do not change or delete any data yourself; only recommend.
Output format:
- Dataset summary: row count, column count, detected column types.
- Outlier bounds table: Column | Q1 | Q3 | IQR | Lower bound | Upper bound.
- Issues table: Row | Column | Issue type | Value found | Why it is a problem | Suggested fix | Needs review (Yes/No).
- Top 3 fixes to make first.
- Excel formulas I can use to recheck these issues.
End with one line stating the valid ranges you assumed and anything you could not check.Example output
Tips and common edits
Why this works
- Naming the IQR method and requiring stated bounds makes outlier flags checkable instead of guesswork.
- Excel row numbers and column names let you jump straight to each issue in your sheet.
- The Needs review column separates safe fixes from real values a human should confirm.
Use before building a report or pivot table. Copy your sheet including the header row and paste it after the prompt in ChatGPT, Claude or Gemini. Works best under about 500 rows; split larger files.
Common edits
- Swap IQR for z-scores above 3 if your data is roughly normal.
- Tell the model your own valid ranges, such as prices between 5 and 500, to tighten impossible-value checks.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Build a Power Query Workflow That Combines and Cleans Monthly Excel Files
You get numbered Power Query steps with paste-ready M code, a full query, and a flag on every step that breaks when column names change.
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.
Clean and Standardize Messy Spreadsheet Data with Exact Formulas
Get a column-by-column cleaning plan with ready-to-fill formulas for spaces, case, dates and duplicates, plus a list of rows that need a manual check.
Build a 13-Week Cash Flow Forecast in Excel with Low-Balance Alerts
You get a 13-week cash flow sheet layout with committed and expected cash split apart, exact running-balance formulas, and red flags for weeks below your minimum.
Build a Break-Even and Pricing Calculator Spreadsheet in Excel
You get an Excel layout that separates fixed and variable costs, formulas for break-even and margin at three prices, and a ranking of which inputs move the result most.
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.