To apply Excel conditional formatting based on two cells, select the cells you want to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that compares or tests both cells. For example, to highlight values in column A when they differ from the value in column B on the same row, use =$A2<>$B2.
What conditional formatting based on two cells means
A conditional-formatting rule evaluates a condition and changes a cell’s appearance when that condition is TRUE. The rule can compare two values in the same row, test two separate criteria, or use two cells to decide how a third cell should look. The formatting is visual only: it does not change the cell’s value or replace a formula already in the cell.
For example, a list might have an expected delivery date in column A and an actual delivery date in column B. A rule can flag rows where those dates differ. Or a status report could highlight a task in column C only when the owner in column A is “Mina” and the status in column B is “Overdue.” The key is to write the rule as a formula that returns TRUE or FALSE for the cell or row being evaluated.
Use a formula rule when the built-in Highlight Cells Rules are not enough. Those built-in options are convenient for simple tests such as greater than, equal to, or duplicate values. A formula is more flexible because it can combine cells, comparisons, and functions.
Compare two cells in the same row
For a row-by-row comparison, use a relative row reference so Excel checks the appropriate pair on each row. In the example below, selecting only column A means only the first value is colored when it does not match column B.
With expected values in A2:A100 and actual values in B2:B100, select A2:A100 and use =$A2<>$B2. The dollar signs lock the column letters, while the row number remains relative. If A2 is “North” and B2 is “South,” the rule is TRUE and A2 receives the chosen format. If the values match, the rule is FALSE.
To highlight matching pairs instead, use =$A2=$B2. The layout is the same: expected values in A2:A100, actual values in B2:B100, and the rule applies to A2:A100. If both cells in a row contain “North,” the condition is TRUE. This is useful for checking whether two lists, codes, dates, or entered totals agree.
To color both cells in each row, select A2:B100 before creating the rule and use =$A2<>$B2. With values in columns A and B, this comparison checks the row’s A and B values; when they differ, the format applies to both selected cells in that row. Be careful to keep the column references anchored as shown, so the comparison does not shift when Excel evaluates the rule in column B.
Create the rule in Excel for Microsoft 365
- Select the destination range first. For example, select A2:A100 to format only column A, or A2:B100 to format both comparison cells.
- On the Home tab, choose Conditional Formatting > New Rule.
- In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
- Enter a formula beginning with an equals sign. Use the formula that corresponds to the top-left cell of the selected range.
- Choose Format, set a fill, font, border, or number format, and select OK. Select OK again to create the rule.
For the example comparing A and B, the layout is values in A2:A100 and B2:B100, with A2:A100 selected. Enter =$A2<>$B2. After the rule is made, add or change values in those rows to see the formatting update. If the selected range begins on row 5 instead, the formula should normally begin with row 5, such as =$A5<>$B5.
In Excel for Mac, the Conditional Formatting commands are also on the Home tab; choose Conditional Formatting and then New Rule. The dialog wording or layout can vary slightly by release, but select the formula-based rule and set its format. Excel for the web also provides conditional-formatting options, though some rule-editing controls may differ from the Windows desktop dialog.
Use two cells as two separate conditions
Sometimes the goal is not to compare two values with each other. Instead, both cells must meet separate tests before a target cell is formatted. Use AND when every condition must be true, and OR when any one of the conditions is enough.
With sales amounts in A2:A100 and approval labels in B2:B100, select A2:A100 if you want to highlight the sales amount only when it is at least 100 and approved. Use =AND($A2>=100,$B2="Approved"). If A2 is 125 and B2 contains “Approved,” Excel formats A2; if either test fails, it does not. Text comparisons need quotation marks inside the formula.
With priority labels in A2:A100 and status labels in B2:B100, select A2:B100 to highlight both cells when either the priority is “Urgent” or the status is “Blocked.” Use =OR($A2="Urgent",$B2="Blocked"). The dollar signs keep the tests pointed at the intended columns as the rule is applied across the selected range.
You can also use one or both cells to decide the format of a third column. With status in A2:A100, due dates in B2:B100, and task names in C2:C100, select C2:C100 and use =AND($A2="Open",$B2<TODAY()). The rule highlights a task name when its status is Open and its due date is before today. TODAY updates with the current date when Excel recalculates. Blank dates can require an extra check if they should not be treated as overdue.
Understand relative and absolute references
Reference anchoring is one of the most important details in a formula-based rule. A reference such as A2 can shift in both its column and row when Excel applies a rule to other cells. A reference such as $A2 fixes the column but lets the row change. A reference such as $A$2 fixes both. Choose the form that matches the layout and the range being formatted.
For a row comparison across columns A and B, =$A2<>$B2 fixes the two source columns but allows the row to advance. When the rule evaluates row 3, it checks A3 and B3; on row 4, it checks A4 and B4. If you use =$A$2<>$B$2 across a large range, every row checks only A2 and B2, which is usually not what a row-by-row rule should do.
The first row in the formula should correspond to the first row in the Applies to range. If the range is $C$5:$C$30, a row-based test of columns A and B should use row 5 in its references. You can inspect and adjust the target range through Home > Conditional Formatting > Manage Rules. In the manager, check the Applies to field as well as the formula; a correct formula applied to the wrong range can still give confusing results.
Choose the right comparison and formatting range
Before writing the formula, decide what should change visually. The cells tested by a rule do not have to be the cells that receive formatting. Selecting A2:A100 formats only A; selecting A2:B100 can format both comparison cells; selecting C2:C100 can format a result or label based on values in A and B. This separation makes rules useful for dashboards and review sheets, not just direct comparisons.
- Use equality when two entries should match, such as an imported code and a reference code.
- Use not equal to to flag discrepancies, such as planned versus recorded hours.
- Use AND when multiple requirements must all be met, such as a threshold and an approval status.
- Use OR when any qualifying condition should trigger the same format, such as urgent priority or blocked status.
- Use separate rules when different outcomes need different colors, such as green for a match and red for a mismatch.
For two-color comparisons, create one rule for matches and another for differences. With values in A2:A100 and B2:B100 and A2:A100 selected, the matching rule is =$A2=$B2, while the discrepancy rule is =$A2<>$B2. Assign different formats to the two rules. Since the conditions are opposites for ordinary nonblank values, they do not normally compete for the same cell.
Common problems and how to fix them
If every row appears to use the same result, inspect the dollar signs and row numbers. Locking the row accidentally, as in $A$2, makes the formula keep referring to row 2. If the rule seems shifted by one row, make sure the formula’s first row matches the first row of the Applies to range.
If nothing changes, check that the rule formula begins with an equals sign and returns the intended TRUE/FALSE result. Also check whether the cells contain extra spaces, text stored differently from numbers, or formulas returning empty strings. A number 10 and text “10” may not behave as interchangeable values in every comparison. If two text values look the same but are treated as different, inspect the source data for leading or trailing spaces.
If multiple rules apply to the same cells, open Home > Conditional Formatting > Manage Rules and review their order, formats, and Applies to ranges. The Stop If True option can affect lower rules when it is available for the rule type. A fill color may also appear absent if another rule with higher precedence supplies a competing fill. Editing the rules is often easier than deleting and rebuilding them.
Conditional-formatting formulas that need a value from another worksheet can have a limitation: Excel may not accept a direct reference to a different sheet in the rule formula. A common workaround is to define a name for the source cell or bring the value onto the formatted sheet in a helper cell, then refer to that name or helper cell. Keep the formula understandable, especially in shared workbooks where someone else may need to maintain it.
Formula rules versus built-in conditional formatting
Built-in rules are quicker when you need a single-cell test. For example, Home > Conditional Formatting > Highlight Cells Rules offers common comparisons such as Greater Than and Equal To. Color Scales, Data Bars, and Icon Sets are useful for showing relative values without writing a formula.
A formula rule is better when the outcome depends on multiple cells, when the formatting target differs from the tested cells, or when the logic combines conditions. It does require more care with references and the selected range. A practical approach is to use a built-in rule for a simple threshold and a formula rule for row comparisons or combined criteria, rather than forcing a complex formula into a task the standard menu already handles.
Frequently Asked Questions
Can I highlight a cell based on another cell’s value?
Yes. Select the cell or range to format, create a formula-based conditional-formatting rule, and write a formula that refers to the source cell. Use relative or absolute references according to whether the source should move as the rule applies to other cells.
How do I highlight two cells when they do not match?
Select both cells or the repeating two-column range, then use a comparison formula such as =$A2<>$B2. Make sure the first formula row matches the first row of the selected range so each pair is compared on its own row.
Can conditional formatting use AND or OR?
Yes. Use AND when all listed tests must be true, and OR when at least one test should trigger the format. Put each comparison inside the function and use commas to separate its arguments.
Can I format a third cell using two other cells?
Yes. Select the third cell’s range as the Applies to range, then write a formula that references the two cells containing the conditions. For example, a task-name cell can be formatted based on a status in one column and a due date in another.
Why does my rule format the wrong rows?
The formula’s row reference may not line up with the top row of the Applies to range, or it may be locked with a dollar sign. Check both the formula and the Applies to field in Manage Rules, then adjust the row reference so it advances for each row.
Related
- Excel Dropdown: Remove Duplicate Options Easily
- Excel Dropdown List: Create, Edit, and Troubleshoot
- Does Excel Online Have Goal Seek? What to Use Instead
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Freeze Panes in Excel: Keep Rows and Columns VisibleTo lock rows or columns in place while you scroll in Excel for Microsoft 365 on Windows, select…
- How to Record a Presentation in Microsoft PowerPointTo record in Microsoft PowerPoint, open your presentation, choose Record > From Beginning (or From Current Slide), allow…
- Excel File Format Corrupted? Causes, Fixes, and RecoveryIf Excel says a file format or file extension is invalid, or a workbook will not open, the…
- Searchable Dropdown List in Excel: How to Create OneYes. In current Microsoft 365 versions of Excel for Windows, a data-validation dropdown can be searched: open the…
- Disable Circular References in Excel: Causes and FixesTo stop Excel for Microsoft 365 on Windows from calculating circular formulas iteratively, go to File > Options…
- How to Compute a Percentage in Google SheetsTo compute a percentage in Google Sheets, divide the part by the total, then format the result as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.