To format cells that do not match a value in Excel, select the range, then choose Home > Conditional Formatting > New Rule. Use the Cell Value condition not equal to, or create a formula rule such as =A2<>"Complete" when the selected range starts at A2. The formula approach also lets you exclude blanks or compare against another cell.
What “not” means in Excel conditional formatting
Excel does not use the word “not” as a single general-purpose conditional-formatting command. Instead, you define the condition that should trigger the format. For a comparison, the operator is <>, which means “not equal to.” You can also test whether a cell is empty, whether text is missing, or whether several conditions are true at once.
This is useful for more than marking mistakes. You might highlight orders whose status is not Complete, products that are not in a chosen category, or rows with a missing review date. Conditional formatting changes how matching cells appear; it does not change their values, delete data, or prevent someone from editing them.
A key decision is whether blanks should count as “not matching.” If you want every cell other than Complete highlighted, a simple not-equal rule may be enough. If empty cells should stay unformatted, use a formula that expressly excludes them.
Use the built-in not-equal rule
For a straightforward comparison against a fixed value, use a cell-value rule. In Excel for Microsoft 365 on Windows:
- Select the cells to format, such as A2:A100.
- Choose Home > Conditional Formatting > New Rule.
- Select Format only cells that contain.
- Under Edit the Rule Description, set the first list to Cell Value and the condition list to not equal to.
- Enter the comparison value, choose Format, set the font, fill, or border, and select OK.
For example, setting the comparison value to Complete makes the rule apply to cells whose values differ from Complete. The comparison can be text, a number, or a date, provided the value is entered in a form Excel recognizes. Text comparisons in Excel are generally case-insensitive, so complete and Complete are normally treated as equal in this kind of comparison.
The New Rule dialog offers other useful operators, including equal to, greater than, less than, and between. For an existing rule, open Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule. The Mac ribbon has the same Conditional Formatting command on the Home tab, though the dialog layout can vary slightly by Excel release.
Use a formula for more control
Formula rules are helpful when you need to control blank behavior, refer to another cell, or combine tests. Select the range first, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for cells that should receive the format.
With the range A2:A100 selected, and with each row holding one status in column A, use =A2<>"Complete" to highlight values that are not Complete. A2 is the first cell in the selected range, so Excel adjusts that reference as it evaluates each row. Choose a format and confirm the rule. If A2 contains Pending, it is highlighted; if it contains Complete, it is not.
To compare each cell in A2:A100 with the value in D1, use =A2<>$D$1. The dollar signs keep the reference fixed on D1 while Excel evaluates different rows. This makes it easy to change the comparison later by editing D1 rather than editing the rule.
Formula references need to match the selection. If the selected range starts at B5, write the formula relative to B5, not A2. A reference shifted by a row or column can make the formatting appear inconsistent because Excel evaluates the formula relative to the top-left cell in the selected range.
Highlight values that are not blank
To highlight nonempty cells in A2:A100, select that range and create a formula rule with =A2<>"". The two quotation marks represent an empty text string. To highlight empty cells instead, use =A2="".
These tests are useful when data may be genuinely empty or produced by a formula that returns an empty string. A formula result of "" is not the same thing as a cell containing a space. A cell with one or more spaces is not empty, so the nonblank test can format it. If imported data may contain stray spaces, clean the source data or build a rule that tests a trimmed value.
To highlight entries that are neither blank nor Complete in A2:A100, use =AND(A2<>"",A2<>"Complete"). This combines two requirements: the cell must contain something, and that content must differ from Complete. It avoids flagging empty cells as exceptions.
Format cells that do not contain particular text
“Not equal to” and “does not contain” are different tests. The first compares the whole cell value with a chosen value; the second asks whether a piece of text occurs anywhere inside a cell. For example, Pending review is not equal to Review, but it does contain Review.
For a case-insensitive test that highlights nonblank cells in A2:A100 that do not contain the word urgent, use =AND(A2<>"",ISERROR(SEARCH("urgent",A2))). SEARCH returns a position when the text is found and an error when it is not; ISERROR turns the latter case into TRUE. AND prevents blank cells from being highlighted. SEARCH ignores letter case. If case matters, Excel’s FIND function is the case-sensitive alternative; it can be used in the same pattern.
For a simpler task, the built-in Highlight Cells Rules menu includes Text that Contains, but it does not provide every possible “does not contain” variation. A formula rule is more flexible when you need to exclude matches, ignore empty cells, or combine criteria.
Apply rules to the right range and manage overlaps
Conditional formatting rules have an Applies to range. When you create a rule after selecting cells, Excel usually fills this in for you. Check it if the formatting appears on only part of the table or spills into unrelated cells: choose Home > Conditional Formatting > Manage Rules and review Applies to.
For a formula rule across multiple rows, use a relative reference for the cell being evaluated, such as A2, and select a range whose first row is row 2. Use dollar signs only where a reference should stay fixed. For example, =A2<>$D$1 changes the row reference as the rule moves down but continues to compare against D1.
More than one rule can apply to the same cells. In Manage Rules, check the order and the Stop If True setting. The order can affect the result when rules set competing formats, and Stop If True can prevent lower rules from being evaluated after a higher rule matches. If a cell has an unexpected color, temporarily disable or reorder rules to identify which one is responsible.
When applying a rule to an Excel table, think about whether the Applies to range should cover only current rows or also future entries. Excel commonly extends formatting as table rows are added, but verify the range if the rule was created before the data was converted to a table or if copied formatting has produced extra rules.
Common reasons a not-equal rule seems wrong
- Blanks are being treated differently than expected: Add an explicit nonblank test with AND if empty cells should not be highlighted.
- The formula points at the wrong cell: The formula should refer to the top-left cell of the selected range, with relative or absolute references chosen deliberately.
- Values look equal but are not: Extra spaces, text stored as a number, or different underlying dates can affect comparisons. Check the actual cell contents and number formats.
- Another rule is changing the appearance: Review rule order, Applies to, and Stop If True in Manage Rules.
- The wrong rule type was chosen: A cell-value not-equal test checks the whole value; a text search checks whether a substring occurs.
Conditional formatting can make exceptions easier to spot, but color alone is not a reliable data check. Keep the underlying values available, use clear labels, and consider a filter or a separate validation step when the consequences of missing an exception are important.
Frequently Asked Questions
Is there a not-equal symbol in Excel conditional formatting?
Yes. In a formula rule, use <>, as in =A2<>"Complete". The New Rule dialog also offers a not equal to condition for cell-value rules.
How do I highlight cells that are not blank?
Select the range and create a formula rule using the first cell in that range. For A2:A100, use =A2<>"". This also treats a formula result of an empty string as empty for this test, but a cell containing spaces is not blank.
Can I compare a cell with another cell?
Yes. For A2:A100 compared with D1, use =A2<>$D$1. The fixed reference ensures every row is compared with the same value in D1.
Does not equal mean the text does not contain the word?
No. Not equal compares the complete cell value. To test whether a cell lacks a word within longer text, use a SEARCH-based formula such as =AND(A2<>"",ISERROR(SEARCH("urgent",A2))).
Will conditional formatting change or remove my data?
No. It changes the cell’s displayed formatting when a rule is true. It does not edit the value, delete the cell, or replace a data-validation rule.
Related
- Can You Rotate an Excel Sheet? What Actually Works
- Can You Rotate Text in Excel? How to Change Its Angle
- Remove Duplicates in Excel: Safe Methods and Checks
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query VLOOKUP: How to Match Tables in Excel InsteadPower Query does not have a VLOOKUP function; use Merge Queries to match rows from two tables and…
- IFERROR with VLOOKUP in Excel: Formulas and FixesTo use IFERROR with VLOOKUP in Excel, wrap the lookup formula inside IFERROR and provide the value to…
- Compare Data in Excel With VLOOKUP: A Step-by-Step GuideTo compare two lists in Excel, use an exact-match VLOOKUP to check whether each item in one list…
- How to Find the Mode in Excel: Formulas and ExamplesTo find the most frequently occurring number in Excel, enter =MODE.SNGL(A2:A11) in an empty cell, replacing A2:A11 with…
- Different Labels in Word: How to Make Each Label UniqueTo create different labels on one sheet in Microsoft Word, start a label mail merge, connect a recipient…
- Excel Text Left of a Character: Formulas and ExamplesTo return the text before a character in Excel, combine LEFT with FIND: if A2 contains INV-2048, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.