Research

Find and investigate spreadsheet outliers

Flag unusual values with a method suited to the distribution, then investigate before removal

7 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Metric and business meaningData profileInvestigation and usage

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Design a spreadsheet outlier analysis.

Metric, unit, valid range, and groups:
[metric]

Data profile and representative values:
[distribution]

Investigation purpose and policy:
[policy]

Separate impossible values, data-entry errors, rare but valid events, and influential observations. Choose a transparent method appropriate to skew and sample size, such as business-rule limits, IQR, robust z-score, or within-group comparison. Return formulas, thresholds, an OutlierReason field, and a review workflow. Do not delete or winsorize values automatically. Show how conclusions change with and without flagged observations and how each row can be traced to source evidence.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Metric and business meaning: OrderValue in GBP, nonnegative. Analyze typical order size by CustomerType because Wholesale orders are normally larger than Retail.
Data profile: 2,400 orders. Retail n=2,100: Q1 £28, median £46, Q3 £72, max £8,400. Wholesale n=300: Q1 £410, median £690, Q3 £1,100, max £12,500. Three values are negative; one Retail row is £8,400; several Wholesale rows exceed £4,000.
Investigation and usage: Find data errors and understand whether extreme orders distort the average. Every order can be checked against an invoice. Do not remove a valid large order.

Example output

Apply the nonnegative business rule first: all three negative values are Invalid and require invoice or source correction. Detect unusual values within CustomerType, not across the mixed population.

For Retail, IQR = 72−28 = 44 and the upper fence is 72+1.5×44 = £138. This intentionally flags many high Retail orders for investigation, not deletion. For Wholesale, IQR = 690 and the upper fence is £2,135. Add group quartiles in a summary table and an OutlierReason column that distinguishes Negative-invalid, Above-group-IQR, and Not-flagged.

The £8,400 Retail order is highly unusual; verify customer type, decimal placement, currency, duplicates, and invoice. If verified, retain it as Rare-valid and explain its influence. Wholesale orders above £4,000 receive the same source check but should not be compared with the Retail threshold.

Report median and IQR as the primary typical-size statistics. Show mean order value with all valid rows and again excluding Rare-valid rows as a sensitivity analysis, while keeping the official total revenue unchanged. Record OrderID, flag, evidence checked, decision, reviewer, and date for every changed classification.

Why this works

1
Separating invalid from unusual prevents legitimate high-impact events from being erased.
2
Sensitivity results reveal whether a small number of observations drives the conclusion.