How to detect outliers and data entry errors in a spreadsheet

One extra zero is all it takes for an order of 150 to become 1500 and for the monthly total to shoot up. Data entry errors rarely jump out in a sheet with hundreds of rows, but they distort averages, charts and decisions.

Here you will learn to find suspicious values with simple, reliable methods: a first look with basic functions, the interquartile range method, the z-score, conditional formatting and a few formulas for specific errors. At the end you will see how to keep errors from getting into the sheet in the first place.

An outlier is not always an error

An outlier is a value that sits far away from the rest. It may be an error, but it may also be real data: a customer who placed a huge order or a month with exceptional sales. That is why the golden rule is flag, review and then decide, never delete automatically.

The most common data entry errors are:

• Extra or missing zeros: 1500 instead of 150.
• Wrong decimal separator: 1250 instead of 12.50.
• Swapped digits: 516 instead of 156.
• Mixed units: grams in a column of kilos.
• Impossible values: negative quantities, ages of 250 or future dates in a sales log.
• Text in numeric columns: “n/a”, “pending” or a dash.
• Duplicate records: the same row entered twice.

A first look: minimum, maximum and median

Before using finer methods, calculate three values for the column (in the example, amounts in B2:B200):

=MIN(B2:B200)

=MAX(B2:B200)

=MEDIAN(B2:B200)

If the median is 140 and the maximum is 14000, you already know where to start. Also compare the median with AVERAGE: when the average is well above the median, a few extreme values are pulling it up.

Another quick approach is to sort the column from largest to smallest with Data › Sort and review the first and last rows. If you do not want to lose the original order, add a column of sequential numbers first so you can restore it.

The interquartile range method

This is the most robust method for spreadsheets, because it is not swayed by the extreme values themselves. It splits the data into four parts and treats anything far from the middle half as an outlier.

1. Calculate the first quartile in F1: =QUARTILE.INC($B$2:$B$200,1)
2. Calculate the third quartile in F2: =QUARTILE.INC($B$2:$B$200,3)
3. Calculate the interquartile range in F3: =F2-F1
4. Lower limit in F4: =F1-1.5*F3
5. Upper limit in F5: =F2+1.5*F3

Then, in a helper column, flag every row that falls outside those limits:

=OR(B2<$F$4,B2>$F$5)

With sample data it looks like this:

The 1.5 factor is the usual one. If you only want to flag the most extreme cases, use 3.

The z-score

The z-score tells you how many standard deviations each value is from the average. In C2:

=(B2-AVERAGE($B$2:$B$200))/STDEV.S($B$2:$B$200)

And to flag the distant values:

=ABS(C2)>3

An absolute value above 3 is very rare if the data follow a normal distribution. Keep two limitations in mind: the outliers themselves inflate the standard deviation and can hide, and with little data (fewer than 30 values, for instance) the method is unreliable. In those cases, the interquartile range works better.

Highlight suspicious values with conditional formatting

If you would rather see the outliers without helper columns:

1. Select B2:B200.
2. Go to Home › Conditional Formatting › New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =OR($B2<$F$4,$B2>$F$5) and pick a fill color.

For a quick check without calculations, the Top/Bottom Rules in the same menu highlight, for example, the top 10% of values. It is useful as a first exploration, although it will always flag something, whether there are errors or not.

Formulas for specific data entry errors

Text in a numeric column

The difference between non-empty cells and cells containing numbers reveals how many text entries slipped in:

=COUNTA(B2:B200)-COUNT(B2:B200)

Impossible values

Count negative quantities and dates later than today (with the dates in column D):

=COUNTIF(B2:B200,"<0")

=COUNTIF(D2:D200,">"&TODAY())

A shifted zero or decimal point

Divide each value by the median. Results close to 10 or 0.1 usually point to an extra zero or a misplaced decimal:

=B2/MEDIAN($B$2:$B$200)

Repeated records

To find out whether an order number appears more than once:

=COUNTIF($A$2:$A$200,A2)>1

Duplicates and comparing versions

Rows entered twice are among the most frequent data entry errors, and with several thousand records reviewing them by hand is not practical. Our free duplicate remover deletes repeated rows from an Excel, CSV or ODS file in seconds. And once you have finished correcting, you can compare the original file with the corrected one to review exactly which cells you changed.

How to prevent errors with data validation

The best way to reduce errors is to stop them from getting in. With data validation, Excel rejects values outside the range you define:

1. Select the column where quantities are entered.
2. Go to Data › Data Validation.
3. Under Allow, choose Whole number or Decimal, and set a sensible minimum and maximum, for example between 1 and 500.
4. On the Error Alert tab, write a clear message for whoever fills in the sheet.

For fields with fixed values, such as categories or statuses, use the List option: you avoid variants like “Pending”, “pending” and “Pend.”. Note that validation does not check data already in the sheet or data pasted over the cells.

Conclusion

Detecting data entry errors is a matter of method: start with minimum, maximum and median, flag outliers with the interquartile range or the z-score, highlight them with conditional formatting and use specific formulas for text, impossible values and duplicates. Review each case before correcting it and, going forward, protect data entry with validation.

Frequently asked questions