To conditionally format values by absolute value in Excel, select the cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula such as =ABS(B2)>100. If the selected range starts at B2, this highlights values whose magnitude exceeds 100, whether they are positive or negative.
What absolute value means in Excel
Absolute value measures how far a number is from zero, without regard to its sign. Excel’s ABS function returns that nonnegative magnitude: ABS of 125 is 125, ABS of -125 is also 125, and ABS of 0 is 0. This is useful when the size of a difference matters more than whether it is above or below zero.
For example, a report might show actual results minus targets. A result of 125 and a result of -125 represent equally large deviations in opposite directions. A standard conditional-formatting rule that checks whether a cell is greater than 100 catches the positive result but not the negative one. Wrapping the reference in ABS makes the test look at magnitude instead.
Excel does not have a separate built-in conditional-formatting category called “absolute value.” The usual approach is a formula-based rule that uses ABS. This changes only how Excel decides whether to apply the format; it does not change the values stored in the cells.
Create an absolute-value formatting rule
Suppose your measurements are in B2:B100 and you want to highlight any value more than 100 units away from zero. Select B2:B100, then create a formula-based conditional-formatting rule. Because B2 is the first cell in the selected range, use this rule formula:
With measurements in B2:B100 and the rule applied to B2:B100, enter =ABS(B2)>100.
Choose Format in the New Formatting Rule dialog to set a fill, font, border, or other available formatting, then confirm the dialog. On Windows in Microsoft 365, the path is Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. On Mac, start from Home > Conditional Formatting > New Rule and select the formula-based rule option; dialog labels or layout can vary slightly by Excel version.
The comparison is strict: values with an absolute value greater than 100 qualify, while 100 and -100 do not. To include the boundary, use a greater-than-or-equal comparison instead.
- 125 qualifies because its absolute value is 125.
- -125 qualifies for the same reason.
- 100 and -100 do not qualify when the rule uses greater than 100.
- 75 and -75 do not qualify because their magnitudes are below 100.
After creating the rule, check the Applies to range in Conditional Formatting > Manage Rules. It should cover the intended cells. If the selection was not correct when the rule was created, edit Applies to rather than rebuilding the rule unnecessarily.
Choose the comparison that matches your goal
The comparison operator determines the boundary behavior. Use greater than when only values beyond a limit should be highlighted; use greater than or equal to when the limit itself should count. Likewise, a less-than test can flag values whose magnitudes are small, such as measurements close to zero.
With measurements in B2:B100 and the rule applied to B2:B100, use =ABS(B2)>=100 to include both 100 and -100. With the same cell layout, use =ABS(B2)<10 to highlight values strictly between -10 and 10, including zero but excluding -10 and 10.
To highlight values whose magnitudes fall in a band, combine two tests. With measurements in B2:B100 and the rule applied to that range, use =AND(ABS(B2)>=50,ABS(B2)<=100). This includes -50, 50, -100, and 100, as well as values between those endpoints. The two comparisons make the intended inclusive boundaries explicit.
If the limit changes often, store it in a worksheet cell instead of embedding a number in the rule. With measurements in B2:B100, the threshold in E1, and the rule applied to B2:B100, use =ABS(B2)>$E$1. The dollar signs keep the threshold reference fixed as the rule is evaluated for each measurement. Changing E1 then changes the cutoff without editing the rule.
Use cell references correctly across a range
Formula-based conditional formatting evaluates the formula relative to the upper-left cell of the Applies to range. For a rule applied to B2:B100, B2 is the appropriate starting reference. Excel adjusts the row as it checks B3, B4, and the remaining cells. If you instead use B1, the rule checks the wrong row for the first cell and can appear shifted by one.
For a single-column range, a relative reference such as B2 is generally what you want. For a multi-column range, decide whether each cell should be tested independently or whether a particular column should control the formatting. Dollar signs lock parts of a reference: $D2 locks the column while allowing the row to change; D$2 locks the row while allowing the column to change; $D$2 locks both.
For example, suppose each record occupies columns A through F, and the amount to evaluate is in column D. To color the entire record when that amount’s magnitude exceeds 100, select A2:F100. With the amount in D2 on the first selected row, use =ABS($D2)>100. The fixed column makes every cell in a row check column D, while the relative row lets the rule move to D3, D4, and so on.
A common mistake is to lock both parts of a reference when the rule needs to move down the rows. If the formula uses $B$2, every cell in the applied range tests B2, so the whole range may receive one uniform result. Use absolute references only for the parts that truly need to stay fixed.
Handle blanks, text, and errors
Blank cells usually do not become highlighted by a rule such as ABS(cell reference) greater than a positive threshold, because a blank effectively contributes zero to that comparison. However, imported data can contain text, error values, or numbers stored in unusual formats. A text entry that cannot be treated as a number can cause ABS to return an error, so it is worth checking the data if a rule behaves unexpectedly.
With values in B2:B100 and the rule applied to B2:B100, use =IFERROR(ABS(B2)>100,FALSE) if nonnumeric entries or errors should simply remain unformatted. IFERROR returns FALSE for a failed test, preventing that cell from meeting the formatting condition. This does not repair the source data; it only makes the rule tolerant of problematic entries.
Also distinguish a real numeric value from text that looks like a number. A cell may display 125 but be stored as text after an import. If it is not being evaluated as expected, check its number format and data type, and consider converting the source values to numbers. A conditional-formatting formula is not a substitute for cleaning a data column.
Combine magnitude tests with other conditions
Sometimes magnitude alone is not enough. You may want to highlight a large deviation only when a row is marked as active, or only for a particular category. AND can require multiple conditions to be true, while OR can allow either of several conditions to qualify. Keep the references aligned with the top-left cell of the applied range, just as with a single-condition rule.
For instance, imagine measurements in B2:B100 and a status in C2:C100. If only rows marked Review should be highlighted when the measurement exceeds 100 in magnitude, apply a formula rule to B2:B100. With those columns and that applied range, use =AND(ABS(B2)>100,C2="Review"). The text must match the status as stored, including any spaces; Excel’s ordinary text comparison is generally not case-sensitive.
When multiple conditional-formatting rules apply to the same cells, their order and the Stop If True setting can affect the final appearance. Open Home > Conditional Formatting > Manage Rules to inspect the rules for the current selection or worksheet. If a qualifying cell shows a different fill than expected, check whether another rule also sets a fill and whether rule priority is producing the result you want.
Absolute value rules versus built-in rules
Built-in Highlight Cells Rules, such as Greater Than and Between, are convenient when a test should use the cell’s actual signed value. They do not automatically treat -125 and 125 as equivalent magnitudes. A formula rule is the appropriate option when the condition must treat positive and negative deviations symmetrically.
There are alternatives, but they serve different purposes. You can calculate absolute values in a helper column and apply a standard rule to that column. This makes the comparison visible and can be easier to audit, but it adds a column and does not format the original cells by itself. You can also replace the original values with ABS results, but that removes their signs and is usually inappropriate when direction matters.
Conditional formatting is often the best choice when you want to preserve the original signed data while making large deviations easy to spot. It is a visual cue, not a data transformation: sorting, formulas, and other calculations still use the values as entered.
Troubleshoot rules that do not highlight as expected
If no cells change appearance, first verify that the comparison is correct and that the rule’s Applies to range includes the cells you selected. Then check that the formula refers to the top-left cell of that range. A threshold of 100 with a strict greater-than operator will not highlight exactly 100 or -100, even though those values may be what you expected to catch.
- Check signs and boundaries: greater than 100 is different from greater than or equal to 100.
- Check the reference: a rule applied to B2:B100 should normally start with B2.
- Check fixed references: locking a row or column can make an entire range test the same cell.
- Check for text and errors: imported values may not be numeric, and errors can disrupt the test.
- Check other rules: a higher-priority rule may apply a competing format.
If you copy cells or extend a table, inspect the rule afterward. Excel may adjust ranges during copy and paste, and separate actions can sometimes create overlapping rules. Manage Rules provides a useful view of each rule’s formula, scope, and priority, making it easier to remove redundant entries or correct an Applies to range.
Frequently Asked Questions
Can Excel conditional formatting highlight positive and negative values equally?
Yes. Use an ABS-based formula rule, such as =ABS(B2)>100 for a range that begins at B2. It evaluates the distance from zero, so -125 and 125 meet the same threshold.
Does Excel have an Absolute Value conditional-formatting button?
No. Excel does not provide a dedicated absolute-value button in the conditional-formatting menus. Create a formula rule and use the ABS function in its condition.
How do I highlight values between -100 and 100?
Apply a formula rule that tests whether the absolute value is at most 100. With values in B2:B100 and the rule applied to that range, use =ABS(B2)<=100. This includes both endpoints and zero.
Can I use a threshold stored in another cell?
Yes. Reference the threshold cell with dollar signs so it stays fixed while Excel evaluates other rows. For example, with data in B2:B100 and the threshold in E1, use =ABS(B2)>$E$1.
Will this rule change my numbers?
No. Conditional formatting changes how qualifying cells look; it does not replace their contents with absolute values. The original positive or negative numbers remain available for calculations and analysis.
Related
- Conditional Formatting in Excel: Questions Answered
- Conditional Formatting Rows in Excel: A Practical Guide
- Conditional Formatting in Excel: Yes, and Here’s Why
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Multi-Criteria XLOOKUP in Google Sheets: How to Use ItIn Google Sheets, use XLOOKUP with multiplied condition tests to find a row matching more than one criterion.…
- Select All in Google Sheets: Shortcuts and OptionsTo select all cells in a Google Sheets tab, click the blank square at the top-left corner of…
- How to Double Space an Essay in WordTo double space an essay in Microsoft Word, select the essay text, then choose Home > Line and…
- Excel New Paragraph in Cell: Line Breaks ExplainedTo start a new paragraph inside an Excel cell in Microsoft 365 for Windows, edit the cell, place…
- Google Sheets Filter: Find Cells Containing TextYes. In Google Sheets, select your data, choose Data > Create a filter, open the filter menu on…
- Google Sheets Conditional Formatting Rules ExplainedIn Google Sheets, conditional formatting rules automatically change a cell’s appearance when its value meets a condition. Select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.