Flag outliers in Excel: values outside an expected range

Before you total, chart or report a list of numbers, check that each one is believable. A claim far over the approval limit, a timesheet with zero hours, a payment ten times bigger than usual: these are the values to catch first. In a spreadsheet you catch them with one comparison, an IF to turn it into a label, and a count to tell you how many need a second look.

Why bookkeepers screen data before analysis

If one expense claim was keyed as 4,820 instead of 48.20, the monthly total is wrong, the average is wrong, and any chart built on it tells a false story.

A range check compares each value with a limit the business already has. Three situations come up again and again:

• Approval limits. Many businesses let staff spend up to a set amount per claim without extra sign-off. Anything above it needs a manager's review. The flag means "look at it", not "it is wrong".
• Typing errors in hours. Timesheets have natural bounds. A line with no hours, or more hours than fit in a working day, is almost always a keying mistake.
• Unusual payments. A vendor payment far smaller or far larger than normal may be a duplicate, a missing digit, or a payment to the wrong account.

The goal is not to judge each value. It is to sort the list into "fine" and "look again", so your attention goes where it is needed.

Why this matters for MO-220 practice

The Excel for Accounting exam (MO-220) includes preparing accounting data for analysis, and under it a skill for verifying data integrity: identifying data outside expected values [Official]. The exam is a 50-minute, performance-based test, and its tasks describe the purpose instead of naming the function [Official]. You might read "mark each claim that needs a manager's review" and have to recognize that IF with a comparison is the tool. The exercises in this lesson are written the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

How it works

IF with a single test

IF takes a test, a result for true and a result for false. To flag a claim in C5 that is over the limit in B2:

=IF(C5>$B$2,"Review","OK")

The test C5>$B$2 is either TRUE or FALSE. IF turns that into a label people can read and filter. The dollar signs lock the limit: when you fill the formula down, C5 becomes C6, C7 and so on, but $B$2 stays put. Keeping the limit in its own cell also means that when the policy changes, you change one cell and every flag updates.

OR for "below the minimum or above the maximum"

A range has two ends. A value is outside it if it is too low or too high. OR returns TRUE when at least one of its tests is true:

=IF(OR(C5<$B$2,C5>$B$3),"Out of range","OK")

It is tempting to reach for AND, because the sentence "between the minimum and the maximum" sounds like two conditions joined together. But AND returns TRUE only when every test is true, and no number can be below the minimum and above the maximum at the same time. AND(C5<$B$2,C5>$B$3) is always FALSE, so the formula never flags anything. The sheet looks clean, which is the worst kind of wrong.

AND does work if you describe the good values instead: =IF(AND(C5>=$B$2,C5<=$B$3),"OK","Out of range") gives the same result.

Boundaries: < versus <=

The hardest part of a range check is not the formula. It is the edge. Is a claim of exactly the limit allowed? Is a shift of exactly the maximum hours fine? The data cannot tell you. The policy has to.

Write the rule down in plain words before you write the formula, for example "claims over the limit need review; a claim at the limit is fine". Then choose the operator to match:

The difference only shows on values exactly on a limit, which is why it is easy to miss.

Counting with COUNTIF and COUNTIFS

Labels are useful row by row. A count tells you the size of the problem at a glance. COUNTIF counts the cells in a range that meet one condition; COUNTIFS does the same with one or more condition pairs. For a single condition they give the same answer.

The condition is written as text, so to compare with a cell you join the operator to the cell with an ampersand:

=COUNTIFS($C$5:$C$20,"<"&B2)

Excel builds the text "<100" (or whatever B2 holds) and counts the cells below it. If B2 changes, the count changes with it. To count values above the maximum, use ">"&B3. The total outside the range is the sum of the two counts.

Related tools

Two other features do similar jobs. Conditional Formatting colors cells that meet a rule, which is good for a quick visual scan. Data Validation stops an out-of-range value from being typed in the first place. Both are covered in other lessons. A formula has one advantage: its results can be filtered, counted and audited.

Worked example 1: claims over an approval limit

Copperleaf Studio lets staff claim up to $200 per item without extra approval. The limit is in B2. Claims are in rows 5 to 20, with the amount in column C. Here are the rows that matter:

In D5, enter =IF(C5>$B$2,"Review","OK") and fill down to D20. Three claims are flagged: rows 6, 10 and 14. The two claims of exactly $200.00 in rows 9 and 20 are OK, because the policy says only amounts over the limit need review.

Worked example 2: hours outside the allowed range

Bluebird Veterinary allows timesheet lines from 0.25 hours (B2) to 8 hours (B3), limits included. These are the lines worth showing:

In D5, enter =IF(OR(C5<$B$2,C5>$B$3),"Out of range","OK") and fill down. Three lines are flagged: the zero-hour line in row 18 is below the minimum, and the two lines of 10.75 hours in rows 16 and 19 are above the maximum. Row 14 (exactly 8 hours) and row 17 (exactly 0.25 hours) sit on the limits, so they are OK.

Worked example 3: counting unusual payments

Bluebird Veterinary also reviews vendor payments. The smallest expected payment is $100 (B2) and the largest is $7,500 (B3). The 16 payments in C5:C20 include these:

Enter the counts:

• F4: =COUNTIFS($C$5:$C$20,"<"&B2) returns 2 (the $34.17 and $41.87 payments).
• F5: =COUNTIFS($C$5:$C$20,">"&B3) returns 1 (the $24,885.16 payment).
• F6: =F4+F5 returns 3 payments outside the range.

The payments of exactly $100.00 and $7,500.00 are not counted, because they are inside the range.

Common mistakes

Flagging amounts exactly at the limit

Writing >= when the policy says "over the limit" flags claims that are allowed. In the Copperleaf Studio list, =IF(C5>=$B$2,"Review","OK") flags five claims instead of three, adding the two claims of exactly $200.00. Read the policy, then pick the operator.

Leaving the limit unlocked

If you write C5>B2 and fill down, the limit reference moves too: row 6 compares with B3, row 7 with B4, and so on. Those cells hold blanks, headers and claim descriptions, not the limit. In the Copperleaf Studio sheet, only one claim ends up flagged, and two real problems are missed. Lock the limit as $B$2.

Using AND instead of OR

AND(C5<$B$2,C5>$B$3) asks for a value that is too low and too high at once. That never happens, so nothing is flagged. In the Bluebird Veterinary timesheet, all 16 lines show OK, including the zero-hour line. Use OR when either failure is enough.

Flagging the limits themselves

When the limits are allowed, <= and >= are the wrong operators. With them, the timesheet flags five lines instead of three, because the 0.25-hour and 8-hour lines are caught too. Use < for the minimum and > for the maximum.

Putting the cell reference inside the quotes

=COUNTIFS($C$5:$C$20,"<B2") does not compare with the value in B2. It compares with the text "B2", and no amount matches, so every count is 0. Join the operator and the cell instead: "<"&B2.

Counting values on the limits

The same boundary mistake shows up in counts. With "<="&B2 and ">="&B3, the payment review counts 3 below and 2 above, for 5 in total instead of 3. The $100.00 and $7,500.00 payments are inside the range and should not be counted.