Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →For rows that represent matching records, use a formula-based Conditional Formatting rule: =A2<>B2. For lists that may be in a different order, check whether each value appears anywhere in the other column with COUNTIF. The right method depends on whether you are comparing row by row or looking for missing list entries.
Compare corresponding rows and highlight mismatches
Use this method when the value in each row of column A is meant to match the value in the same row of column B. For example, it works when two columns contain fields for the same records in the same order.
As an Amazon Associate I earn from qualifying purchases.
- Select the cells you want formatted. Select
A2:B100to highlight differences on either side, orA2:A100to highlight only column A. - Choose Home > Conditional Formatting > New Rule, then choose the option for using a formula to determine which cells to format. Excel’s menus can vary by platform or version.
- Enter
=A2<>B2, then choose a fill or font format and confirm the rule. - Check a few known matches and mismatches. Confirm the rule applies to the intended range and that the formula’s row number matches the first row in that range.
Conditional Formatting applies the chosen format when the formula evaluates to TRUE. When the rule is applied to both columns, Excel adjusts the relative references for each cell; the comparison still checks the two cells in the same row. If you select only column A, the rule compares each A cell with its B counterpart.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Make text comparisons case-sensitive
The standard comparison treats capitalization as equivalent for text. If ABC and abc must count as different, use =NOT(EXACT(A2,B2)) as the Conditional Formatting formula, keeping the references aligned with the first row of the selected range. Microsoft Support says EXACT is case-sensitive but ignores formatting differences.
Find values missing from a differently ordered list
If the columns contain the same kind of list but their rows are not paired, comparing A2 with B2 can flag ordinary ordering differences rather than missing values. Instead, check whether each entry in one column appears anywhere in the other.
- Select the data cells in column A that you want to check.
- Create a formula-based Conditional Formatting rule and enter
=COUNTIF($B:$B,A2)=0, replacing the row number with the first selected data row if needed. - Choose a format and apply the rule. It highlights A values for which no match is found in column B.
- To find values in B that do not appear in A, select the B data cells and use
=COUNTIF($A:$A,B2)=0, adjusting the row reference to the selection’s first row.
COUNTIF matching is case-insensitive and supports wildcard criteria. Microsoft notes that text strings longer than 255 characters can produce incorrect results; extra spaces, inconsistent quotation marks, and nonprinting characters can also cause unexpected matches. If results look wrong, inspect and clean the source values before relying on the highlights. See Microsoft’s COUNTIF guidance.
Rank #2
- Used Book in Good Condition
Check how blank cells should be treated
Before applying either formula, decide what an empty cell means in your data. In a same-row comparison, two empty cells compare as equal, while an empty cell paired with a nonempty value is a mismatch. In a membership check, a blank being checked can match a blank elsewhere in the searched column, so the zero-count rule will not flag it as missing if that column contains a blank. If blanks should be excluded, select only populated data rows or handle blanks separately.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchCompare entire workbook versions
For two workbook versions, rather than just two columns, Microsoft’s Spreadsheet Compare can inspect differences in values, formulas, and formatting cell by cell. Microsoft lists it as available only with Microsoft 365 Apps for enterprise; check your installed edition before looking for the utility. See Microsoft’s Spreadsheet Compare guidance.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choose the formula that fits the comparison
| Question | Conditional Formatting formula | What it identifies |
|---|---|---|
| Do the two cells in each row differ? | =A2<>B2 |
Same-row mismatches; capitalization is not distinguished. |
| Do the two text cells differ, including capitalization? | =NOT(EXACT(A2,B2)) |
Same-row differences that are case-sensitive. |
| Does each A value appear somewhere in B? | =COUNTIF($B:$B,A2)=0 |
A values absent from B, without regard to capitalization. |
| Does each B value appear somewhere in A? | =COUNTIF($A:$A,B2)=0 |
B values absent from A, without regard to capitalization. |
In every formula, adapt the row reference to the first data row in the selected range. Use a row-by-row formula only when the rows correspond; use COUNTIF when list order does not matter.
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
