Outlier analysis in Excel: flag unusual values

Outlier analysis in Excel flags unusual order values by comparing each row with stated lower and upper thresholds. Use a quartile-based screen, retain the original orders and show segment totals both with and without flagged records.

Prepare one row per comparable order

Start with an order export containing order ID, segment, order date, currency and net order value. Sum line items to one row per order before calculating quartiles. Check IDs for duplicates, reconcile the net values to the selected report and exclude canceled orders only under a documented population rule.

Use one reporting period and currency. Returns and credits may need a separate population because their negative values follow a different business process. Missing amounts must be investigated rather than converted to zero.

The worked example is a US distributor's eight completed orders from one product family and purchasing program. East and West use comparable pricing. The small table makes the calculations visible; it does not establish a permanent threshold for the business.

Put IDs in A2:A9, segments in B2:B9 and net order values in C2:C9. Column D will hold the formula-generated flag.

Keep the total outside C2:C9. For how a real exceptional order affects commercial measures, see the one-off sales order; the procedure here focuses on worksheet thresholds and reconciliation.

Calculate quartiles and fences

Use the inclusive quartile convention throughout. Microsoft's quartile reference describes quartiles based on inclusive percentiles. Put the following calculations in J2:J6:

For these sorted observations, the inclusive percentile position is 1+(n-1)*p. Q1 lies at position 2.75: 90 + 0.75 × (100 - 90) = $97.50. Q3 lies at position 6.25: 130 + 0.25 × (140 - 130) = $132.50.

IQR = Q3 - Q1. Lower fence = Q1 - 1.5 × IQR. Upper fence = Q3 + 1.5 × IQR.

The NIST box plot guide describes fences using this multiple of the interquartile range. Here, 97.50 - 1.5 × 35 = $45 and 132.50 + 1.5 × 35 = $185. These are screening boundaries, not approved selling limits or evidence of wrongdoing.

Apply a visible flag to every row

Enter this in D2 and fill down to D9:

=IF(OR(C2<$J$5,C2>$J$6),"Flagged","Unflagged")

The comparison is strict: a value exactly on a fence is unflagged under this rule. Absolute references keep every row tied to the same threshold cells. O108 exceeds the $185 upper fence; every other order lies between $45 and $185.

Add a review column for the source-document check and business explanation. Keep any confirmed project order in actual sales. The outlier flag definition explains the distinction between labeling a record and removing it.

The overall mean is 1,370 / 8 = $171.25, while the median is (110 + 120) / 2 = $115. Without the flagged order, the sensitivity mean is 770 / 7 = $110. These are different summaries of the same retained source records; label the excluded-order view explicitly.

Compare segments and prove the totals

List East and West in F2:F3. Calculate all-order revenue in G2, flagged revenue in H2 and unflagged revenue in I2, then fill down:

=SUMIFS($C$2:$C$9,$B$2:$B$9,F2)
=SUMIFS($C$2:$C$9,$B$2:$B$9,F2,$D$2:$D$9,"Flagged")
=SUMIFS($C$2:$C$9,$B$2:$B$9,F2,$D$2:$D$9,"Unflagged")

Microsoft documents SUMIFS as adding amounts that meet the specified criteria. Covirage is not affiliated with Microsoft.

The proof is 380 + 990 = 1,370 and 600 + 770 = 1,370. Check =SUM(G2:G3)-SUM(C2:C9) and =SUM(G2:G3)-SUM(H2:I3) both return zero. One flagged order plus seven unflagged orders must also match the eight source orders.

The flagged order contributes 600 / 1,370 = 43.8% of total revenue, rounded to one decimal. West's apparent lead changes sharply in the sensitivity view. That is a prompt to investigate O108, not a reason to rewrite reported revenue. For ranking customers rather than screening orders, use decile analysis in Excel.

Where it goes wrong

• Mixing unlike orders. Different product families or contract sizes can create flags that merely reflect the population mix.
• Including total rows. A report subtotal is not an order. Including it changes quartiles and double-counts revenue.
• Deleting flagged transactions. A statistical screen does not reverse a sale. Keep real records and show the sensitivity separately.
• Changing conventions silently. Different quartile methods or multipliers can change the review list without any change in orders.
• Trusting a zero IQR. Repeated identical values can produce identical fences. Inspect the population before applying the rule mechanically.

Compare your flagged order file in Covirage

Bring the order export, segment columns and your documented review flags to Covirage. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See Covirage for Excel analysis for the supporting workbook-analysis workflow.