How to Find Outliers in Numeric Data Without Code

You're looking at a revenue column. Most orders are between $20 and $300. But there are three orders over $50,000. Are those enterprise deals? Bulk purchases? Or did someone accidentally enter 50000 instead of 500?

You can't answer that question by scrolling through rows. You need to see the distribution first, then drill into the extremes. Here's how to do that without writing any code.

Start with the histogram

Click any numeric column header in ExploreMyData to open the Column Explorer. The top section shows six stats: Sum, Avg, StDev, Min, Max and Nulls, the last of which only appears when there are any. Below that is an interactive histogram with a Bin size selector.

The bins are equal width. Every bar covers the same span of values, and the bin size buttons let you choose how wide. That's worth internalising before you read your first histogram, because equal-width bins behave in a specific way when outliers are present: one extreme value stretches the range, the bars get wide, and the entire main cluster collapses into a single tall column.

Which is itself the tell. If 99% of your rows fall in one bar and there are two or three lonely bars far to the right with a wide empty gap in between, you have outliers. A smooth taper across many bars means you probably don't.

Look at what the equal widths cost you. One bar holds 31,976 of 32,000 rows, so the histogram tells you almost nothing about the shape of the ordinary data. It tells you a great deal about the extremes, which is what you came for.

Adjust the bin size

The bin size buttons sit just above the chart, and the options are computed from your column's range rather than being a fixed list. With revenue running from -$3,200 to $52,400, a range of $55,600, you get 1,000 / 2,000 / 5,000 / 10,000 / 20,000, and the middle one is selected by default. That's the $5,000 view above.

Now try to zoom in on the main cluster. Click 1,000, the finest option available, and you still get one bar holding roughly 31,900 rows, because $1,000 bins are far wider than the $20-to-$300 band you're trying to see. The finer sizes you actually want, $50 or $100, aren't offered, and that isn't an oversight: they'd produce over 500 bars across this range.

The fix is to shrink the range, not the bins. Add a Filter for revenue < 5000, reopen the explorer, and the range collapses to a few thousand dollars. New bin size options are computed against that, and now you can see the distribution of the main cluster properly. Delete the filter step when you're done. This is the standard move: one histogram for the extremes, a filtered one for the body.

Read the summary stats

The stats panel above the histogram gives you quick diagnostic numbers:

• Min and Max show the full range. If Min is negative in a column that shouldn't have negatives, like quantity or age, that's a problem on its own.
• Avg against Max tells you about skew. If the average is $312 and the max is $847,000, the max is roughly 2,700 times the average. Worth investigating.
• StDev quantifies spread. A high standard deviation relative to the mean suggests either genuine variation or outlier contamination, and you can't tell which from this number alone.
• Nulls matters too, and only shows up when the count is above zero. Missing values in a numeric column are sometimes the real outliers: records where someone left the field blank because the number didn't make sense.

What isn't there: a median, and no quartiles. Every stat on that panel is one DuckDB aggregate over the column, and the panel is deliberately small. That matters for the next section.

Put a number on it: the 1.5 x IQR rule

"Looks far out" is a judgement call. The standard way to make it a rule is the interquartile range: take the middle 50% of your values, measure how wide that band is, and treat anything more than one and a half band-widths beyond either edge as an outlier. Upper fence is Q3 + 1.5 x IQR, lower fence is Q1 - 1.5 x IQR.

The stats panel won't hand you Q1 and Q3. But two operations will, and this is the part worth knowing: Smallest and Largest in the Aggregate group return the nth smallest or nth largest value in a column. Feed them a rank and you have a percentile.

With 32,000 non-null revenue values, a quarter is 8,000. So:

• Smallest on revenue with N = 8,000 gives Q1
• Largest on revenue with N = 8,000 gives Q3

Both write the value into a new column, the same figure on every row, which is exactly what you want for a threshold. Say they come back as Q1 = $96.40 and Q3 = $341.20. The arithmetic from there is four lines you can do in your head:

• IQR = 341.20 - 96.40 = 244.80
• 1.5 x IQR = 367.20
• Upper fence = 341.20 + 367.20 = 708.40
• Lower fence = 96.40 - 367.20 = -270.80

Now filter: revenue > 708.40 OR revenue < -270.80. That's your outlier set, defined by a rule you can defend rather than by where the chart looked odd.

Compare that with the rule you'd get from the stats panel alone, average plus three standard deviations: 284.73 + 3 x 1,847.22 = 5,826.39. Eight times looser, and for a specific reason. The standard deviation is computed over the whole column, so the three $50,000 orders inflate it, and the fence they widen is the one meant to catch them. IQR doesn't have that problem: quartiles don't care how extreme the extremes are. That's the whole argument for the IQR rule, and it's why it's worth the two extra operations.

Pull up the extreme values

Once you know outliers exist, you need to see the actual rows. Use the Top / Bottom Rows operation to show the top N rows by a specific column. Set it to show the top 10 rows sorted by revenue descending. Now you can see the full context of each outlier: who's the customer, what's the product, when did the order happen.

Context is everything. An order for $52,000 from "Acme Corp" for "Enterprise License - Annual" is probably legitimate. An order for $52,000 from "John Smith" for "Widget (Blue)" is probably a data entry error.

Do the same for the bottom. Show the bottom 10 by revenue. Negative values, zeros, and unusually small amounts often tell you as much as the large outliers. A $0.01 order might be a test transaction. A -$3,200 order is likely a refund that someone entered as a new order instead of an adjustment.

Filter to isolate them

Now that you've identified the threshold, whether that's the IQR fence of 708.40 or just the obvious gap in the histogram, use Filter to isolate the outliers. A condition like revenue > 708.40 gives you the high end. revenue < 0 gives you the negatives, which are a separate question and usually a different root cause.

With the outliers isolated, you can examine each one. Look at the other columns for patterns. Are all the $50K+ orders from the same customer? The same date? The same sales rep? Patterns in the context columns help you decide whether these are real data or errors.

Common outlier patterns

After looking at enough datasets, certain patterns keep showing up:

• Powers of 10: A value of $5,000 when everything else is around $50 usually means a missing decimal point.
• Round numbers: $100,000 exactly, in a column of precise dollar-and-cent values, often indicates a placeholder or estimate.
• Negatives in positive-only fields: Quantity, age, or price columns shouldn't have negative values. These are usually refunds, corrections, or entry errors.
• Zeros: A zero in a revenue or price column might mean "free" or might mean "unknown." Check the context.
• Same extreme value repeated: If you see 999999 or -1 multiple times, those are likely sentinel values used to mean "not applicable" or "missing."

Decide, then act

Finding outliers is step one. Deciding what to do with them is step two. Your options are usually:

• Keep them: They're real data points. Enterprise deals really do happen.
• Fix them: $50,000 should be $500. Use Find & Replace or a formula to correct.
• Remove them: They're test data or errors. Filter them out.
• Flag them: Add a column that marks them for review by someone who knows the business context.

The histogram and stats tell you the outliers exist. Top/Bottom and Filter let you see what they are. The decision about what to do with them requires judgment, but at least now you have the information to make that call.

Explore your numeric data →