Conditional Formatting for Absolute Values in Excel

To conditionally format values by their absolute size in Excel for Microsoft 365 on Windows, create a formula rule using =ABS(B2)>100 for a range that begins at B2. This highlights numbers whose magnitude is greater than 100, whether they are positive or negative; it changes only their appearance, not the values stored in the cells.

What absolute-value formatting means

An absolute value is a number’s distance from zero, without regard to its sign. For example, the absolute values of 125 and -125 are both 125, while the absolute value of -40 is 40. Excel’s ABS function returns that nonnegative magnitude, so it is useful when the size of a change matters more than whether the change was an increase or a decrease.

Conditional formatting applies a visual style when a rule is true. A rule based on ABS can therefore flag unusually large positive and negative results in the same way. This is useful for reviewing variances, temperature changes, measurement errors, or gains and losses against a common tolerance. It does not convert negative numbers into positive numbers, replace them with their absolute values, or change how other formulas calculate.

Excel’s built-in Highlight Cells Rules are designed for direct comparisons such as greater than or less than a number. To compare the magnitude of a value while ignoring its sign, a formula-based conditional formatting rule is generally the clearest option.

Create an absolute-value rule

For example, suppose the values to check are in B2:B100 and you want to highlight magnitudes above 100. Select B2:B100, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format. With the selected range beginning at B2, enter the formula shown here:

With values in B2:B100 and B2 as the active top cell, use =ABS(B2)>100.

Choose Format, select a fill, font, or border, and confirm with OK. The formula returns TRUE for 125 and -125, so both cells receive the format; it returns FALSE for 80 and -80. The comparison is strictly greater than 100, so values exactly equal to 100 or -100 are not highlighted.

Check the rule’s range in Home > Conditional Formatting > Manage Rules. In the rule manager, set Show formatting rules for to the current selection or the relevant worksheet if the rule is not visible. The Applies to box should cover the intended cells, such as =$B$2:$B$100. A correct formula applied to the wrong range can make a rule appear broken.

Choose the right comparison

The comparison operator controls the boundary. Choose it according to what you mean by “large,” “at least,” or “outside the acceptable range.” With values in B2:B100 and B2 as the active top cell, these examples show common choices:

=ABS(B2)>100 highlights magnitudes strictly greater than 100.
=ABS(B2)>=100 includes values whose magnitude is exactly 100.
=ABS(B2)<10 highlights values close to zero, between -10 and 10 but not including either endpoint.
=ABS(B2)=100 highlights values whose magnitude is exactly 100.

Exact equality can be unreliable for calculated decimal values. A result displayed as 100 may internally be 99.999999 or 100.000001 because of how decimal calculations are represented. If the intention is to flag values within a small tolerance of 100, use a tolerance band instead. With values in B2:B100 and B2 as the active top cell, a band from 99.5 through 100.5 in absolute magnitude can be expressed as:

=AND(ABS(B2)>=99.5,ABS(B2)<=100.5)

Excel treats the lower and upper comparisons as inclusive here. Adjust the endpoints to match the precision that matters in the worksheet rather than relying on the number format, which may display fewer decimal places than the cell actually contains.

Use a threshold stored in a cell

A fixed number in a rule is simple, but a threshold in a worksheet cell is easier to change and review. Suppose the limit is in D1 and the values are in B2:B100. Select B2:B100 and create a formula rule that refers to D1. With the limit in D1 and B2 as the active top cell, use:

=ABS(B2)>$D$1

The dollar signs lock the threshold reference, so every row compares against D1 even as the conditional formatting rule evaluates B3, B4, and the remaining cells. If D1 contains 100, values above 100 in magnitude are formatted; changing D1 to 150 updates the rule’s threshold without editing the formula. Make sure the threshold cell contains a number, not text that merely looks like a number.

To include the threshold itself, change the greater-than operator to greater-than-or-equal. You can also place separate limits in separate cells and create multiple rules with different formats, such as one color for values beyond a warning limit and another for values beyond a more serious limit. When rules overlap, review their order and options in Manage Rules so that the intended format is applied.

Format an entire row based on one value

Sometimes the goal is to shade a complete record when the value in one column exceeds a limit. Suppose records occupy A2:D100, and the value to test is in column B. Select A2:D100 before creating the rule. With B2 as the test cell in the first selected row, use this formula:

=ABS($B2)>100

The dollar sign before B locks the column, so Excel always checks column B as the rule moves across columns A through D. The row number is not locked, so each record checks its own B cell. If you used =ABS(B2)>100 across the full row range, Excel would shift the reference as it evaluates cells in other columns; that could cause it to inspect the wrong column. The rule’s Applies to range should be A2:D100, not just B2:B100, for the entire row to be formatted.

Exclude blanks and nonnumeric entries

For a positive threshold, a blank cell usually does not meet a test such as ABS greater than 100. But rules that include zero, or data containing text, may need an explicit check so that only numeric entries qualify. With values in B2:B100 and B2 as the active top cell, use:

=AND(ISNUMBER(B2),ABS(B2)>=100)

ISNUMBER confirms that the evaluated cell contains a number before the magnitude comparison is considered. This helps avoid formatting text labels and makes a rule’s intent clear. It also excludes formulas that return an empty string, because an empty string is not a number. If your worksheet stores numeric-looking values as text, convert them to real numbers or revise the data-import process; a conditional formatting formula should not be expected to repair inconsistent data.

Common problems and fixes

Only positive values highlight: Check that the rule uses ABS around the cell reference. A direct comparison such as =B2>100 tests signed values and will not flag -125.
The wrong cells highlight: Check the active cell used when the rule was created, the formula’s relative references, and the rule’s Applies to range. If the range begins at B2, the formula should normally refer to B2, not B1 or B3.
The whole range behaves like one cell: Avoid locking the row for a rule that should test each row independently. $B$2 always refers to B2; $B2 locks the column but lets the row change.
A value on the boundary is missing: Greater than does not include equality. Use greater-than-or-equal if the threshold itself should be formatted.
Nothing changes after editing a threshold: Confirm that the rule refers to the intended threshold cell and that the cell contains a numeric value. Also check whether another conditional formatting rule overrides the visible format.
Numbers look rounded: Cell display formatting does not change the stored value. Increase the displayed decimal places or use a tolerance range if rounding affects the decision.

When you copy cells, Excel may copy conditional formatting along with other cell properties, creating overlapping or duplicate rules. If a rule appears inconsistent after copying or inserting rows, open Manage Rules and inspect the ranges and order. Removing obsolete rules is often more reliable than adding another rule on top of them.

Formula rule or color scale?

A formula rule is best when there is a meaningful threshold: for example, highlight any absolute variance above 100, or flag values within a defined tolerance. It gives you a clear TRUE-or-FALSE condition and supports decisions such as whether the endpoint counts. The format can be a fill, font change, border, or another option offered by the Format Cells dialog.

A color scale is useful when you want to see relative patterns across a range, such as which values are comparatively high or low. However, a normal color scale evaluates the cell values themselves, including their signs; it does not automatically rank values by absolute magnitude. A range containing -200, 20, and 150 may therefore produce a color pattern that does not represent magnitudes of 200, 20, and 150. You can create a helper column containing absolute values and apply a color scale to that column, or use formula-based rules if the purpose is to identify specific limits.

For routine reporting, consider whether color alone communicates the result adequately. Keep the underlying signed values visible when direction matters, and use a legend, label, or separate status column if readers need to understand why a cell is highlighted. Conditional formatting is a visual aid, not a substitute for explaining what the threshold represents.

Frequently Asked Questions

Can Excel conditional formatting ignore whether a number is positive or negative?
Yes. Use a formula rule with ABS around the relevant cell reference. A comparison such as ABS greater than a limit treats equal-sized positive and negative numbers alike.

Does conditional formatting change a negative number to positive?
No. ABS is used only to evaluate the rule. The cell keeps its original value, and formulas that refer to it continue to use that signed value.

Can I highlight values whose absolute value is less than a limit?
Yes. Use a less-than comparison with ABS. For example, the rule can identify values near zero; choose whether the boundary is included by using less-than or less-than-or-equal.

Why does the formula use B2 when the rule applies to many cells?
Conditional formatting evaluates the formula relative to each cell in the Applies to range. If the range starts at B2, B2 is the reference for its first cell, and Excel adjusts the relative row reference for later cells.

Can I use a cell as the absolute-value limit?
Yes. Put the numeric limit in a cell and use an absolute reference to it, such as $D$1, while leaving the data reference relative. This lets you change the limit without rewriting the rule.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.