Outliers
How to Find and Calculate Outliers in Excel
To calculate outliers in Excel, find the first and third quartiles, take the interquartile range (IQR) between them, and flag values more than 1.5 × IQR below the first quartile or above the third. A flagged value may be a typo, a rare but valid event, or the most important observation in the file, so review it before you change or remove anything. In TidyX, the outlier flag marks IQR candidates and leaves every value in place.
By the TidyX team · Updated
How to calculate and flag outliers in Excel
This example uses numeric observations in A2:A101. Adjust the range to your data, inspect blanks and errors separately, and confirm that numbers stored as text have been handled intentionally.
- In D2, calculate the first quartile with =QUARTILE.INC($A$2:$A$101,1). In D3, calculate the third quartile with =QUARTILE.INC($A$2:$A$101,3).
- In D4, calculate the interquartile range with =D3-D2. In D5, enter the lower fence =D2-1.5*D4. In D6, enter the upper fence =D3+1.5*D4.
- In B2, enter =IF(ISNUMBER(A2),OR(A2<$D$5,A2>$D$6),FALSE) and fill down. Filter B to TRUE to inspect candidates while leaving the original values unchanged.
- Check units, entry mistakes, unusual but valid events, and whether different groups should be assessed separately. Record any decision to correct, retain or exclude a candidate before making a new output copy.
A flag is a reason to review
The 1.5 multiplier is a conventional screening choice, not proof that a value is wrong. NIST calls points beyond the 1.5 × IQR fences mild outliers and points beyond 3 × IQR extreme outliers. Small samples, an IQR of zero, skewed distributions and mixed populations need extra context. Different quartile conventions can produce different boundaries. This Excel example uses QUARTILE.INC; it does not promise identical flags from every statistical tool.
How to exclude outliers in Excel without deleting them
Removing a row is rarely the first step. Review each candidate, decide whether to correct it, keep it or leave it out of a particular calculation, and record the reason.
- To see how much the candidates move a result, enter =AVERAGEIFS($A$2:$A$101,$A$2:$A$101,">="&$D$5,$A$2:$A$101,"<="&$D$6) in D7. It averages only the values inside the fences; compare it with =AVERAGE($A$2:$A$101) in D8.
- If column B has no header yet, type one such as Flag in B1. Select Data → Filter, select the arrow in column B’s header, clear (Select All) and check TRUE. Only the candidate rows stay visible; filtering hides the other rows without deleting them.
- In a new column, mark each candidate keep, correct or exclude, and note why.
- If you decide to leave candidates out of the final analysis, copy the data to a new sheet and remove them there, so the original values stay available for review.
TRIMMEAN trims a fixed share, not outliers
TRIMMEAN works differently: =TRIMMEAN($A$2:$A$101,0.1) leaves out 10% of the data points, half from each end, whether or not they fall outside the fences, and rounds the number it leaves out down to a multiple of 2. Use it only when a fixed trim is what your analysis calls for.
Use a clear, parameterized detection rule
The supported outlier operation uses the interquartile range with a multiplier of 1.5 or 3, the inner and outer fences in the NIST description, to identify candidates. Its action is flagging, not deletion: the output gains a new column, such as Amount_outlier_flag for an Amount column, marked true for each candidate. The plan shows the target column, rationale, risk, and preliminary impact before confirmation.
Because the operation is deterministic, the same numeric input, parameters, and engine version produce the same flags. The completed report can show the number of rows flagged and any warnings returned by execution.
The rule tests only cells that read as plain numbers, so values such as $1,200 need a number-parsing rule earlier in the plan. A column with fewer than eight numeric values, or an IQR of zero, gets no flags.
Do not confuse detection with treatment
Winsorization, clipping, replacement, and row deletion answer a different question from detection. Those treatments can distort a distribution or remove legitimate edge cases, so they need distinct schemas, risk rules, impact reporting, and Worker support.
The product supports bounded winsorization, setting IQR outliers to missing and removing IQR outlier rows. Each treatment requires an explicit high-risk acknowledgement; flagging remains available when changing values is not justified.
- Supported now: IQR candidate flagging
- Editable now: a multiplier of 1.5 or 3
- High-risk review: winsorize, set missing, or remove outlier rows
- Always review domain meaning before treatment
Preserve context through report provenance
The run report connects flagged counts to a frozen rule snapshot, source hash, output hash, and engine version. That record helps distinguish a data change from a change in thresholds or tooling.
Saved recipes preserve a successful run snapshot. Before reuse, check the target schema and review the outlier parameters again. Compatible column names do not establish that the same thresholds suit a new distribution.