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

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

At a glanceData issues found in a 48-row sales export8 issues flagged across 6 columns, with fixes and Excel formulas
Data rows checked
48
6 columns
Issues flagged
8
Duplicates, missing, impossible, outliers
IQR outliers
2
Units 120 and UnitPrice 189.00
Impossible values
3
Negative units, future date, 150% discount

Example for: a 48-row monthly sales order export with columns OrderID, OrderDate, Region, Units, UnitPrice, Discount%.

Dataset summary
  • 48 data rows, 6 columns
  • Types: OrderID (text), OrderDate (date), Region (text), Units (integer), UnitPrice (currency), Discount% (percent)
Outlier bounds
ColumnQ1Q3IQRLower boundUpper bound
Units4128-824
UnitPrice18.5042.0023.50-16.7577.25
Discount%01010-1525
Issues
RowColumnIssue typeValue foundWhy it is a problemSuggested fixNeeds review
14, 15AllExact duplicateSO-1042 repeatedDouble-counts revenueDelete row 15No
22RegionNear-duplicate value"north "Trailing space splits North in pivotsApply TRIMNo
9UnitPriceMissingblankRevenue cannot be calculatedLook up price in price listYes
31UnitsImpossible-3Negative units on a saleCheck if this is a return; move to returns sheetYes
37OrderDateImpossible2026-11-04Date is in the futureLikely typo for 2025-11-04; confirmYes
40Discount%Impossible150%Discount cannot exceed 100%Probably 15%; confirm with order recordYes
18UnitsOutlier120Above upper bound of 24Verify with customer order; may be a bulk dealYes
27UnitPriceOutlier189.00Above upper bound of 77.25Check for decimal error (18.90)Yes
Top 3 fixes first
  1. Remove the duplicate in row 15: it inflates totals by 1 order.
  2. Correct row 40 discount and row 27 price: both distort margin figures.
  3. Decide how to treat the return in row 31 so units sold are not understated.
Excel formulas to recheck
  • Duplicates: =COUNTIFS(A:A,A2)>1
  • Blanks: =COUNTBLANK(A2:F49)
  • Units outlier flag: =OR(D2<QUARTILE.INC(D$2:D$49,1)-1.5*(QUARTILE.INC(D$2:D$49,3)-QUARTILE.INC(D$2:D$49,1)),D2>QUARTILE.INC(D$2:D$49,3)+1.5*(QUARTILE.INC(D$2:D$49,3)-QUARTILE.INC(D$2:D$49,1)))
  • Extra spaces: =LEN(C2)<>LEN(TRIM(C2))

Assumed valid ranges: Units 1 or more, Discount% 0-100, dates up to today. I could not verify prices against a master price list.

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

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 in Excel & Spreadsheets