iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To highlight differences between two Excel columns, use a formula-based Conditional Formatting rule. For paired rows, compare the cells on each row with =A2<>B2. If the lists are in different orders, use a membership check such as =COUNTIF($B:$B,A2)=0 to flag values in column A that do not appear anywhere in column B.
Choose the comparison that matches your data
| Question | Use this rule or tool | What it finds |
|---|---|---|
| Do corresponding rows represent paired records? | =A2<>B2 |
Unequal values in the same row |
| Should capitalization count as a difference? | =NOT(EXACT(A2,B2)) |
Same-row differences, including capitalization |
| Can the same value appear on different rows? | =COUNTIF($B:$B,A2)=0 |
Values in A absent anywhere in B |
| Are you comparing workbook versions? | Spreadsheet Compare | Cell-by-cell differences in values, formulas, and formatting |
These methods answer different questions. Use a same-row formula only when row positions are meaningful; if the lists are independently ordered, compare membership instead. If you want to highlight the opposite direction, check for values in B missing from A.
Highlight differences in corresponding rows
Use this approach when row 2 in one column is supposed to match row 2 in the other, row 3 with row 3, and so on. A formula-based Conditional Formatting rule applies the chosen format when its logical test is true. See Microsoft’s Conditional Formatting guidance.
- Select the cells to format. Select
A2:B100to highlight both cells in each differing pair, orA2:A100to highlight only the cells in column A. - Open the rule creator. Go to Home > Conditional Formatting > New Rule, then choose the option for using a formula to determine which cells to format. Labels and available options can vary by Excel platform or version.
- Enter the comparison formula. For a selection beginning on row 2, enter
=A2<>B2. Use the first row of your selected range in the formula; Excel adjusts the relative row references for the other rows. - Choose the format. Select a fill or font style, confirm the rule, and check the highlighted cells against a few pairs you know should match or differ.
For example, if the range begins on row 5, use =A5<>B5. If a rule highlights unexpected rows, check that its “Applies to” range is the intended selection and that its formula starts with that range’s first row.
#1 Best Overall
- Over 215 Microsoft Windows Excel Shortcuts
- Two-Sided Durable Laminiated Sheet
- Designed for Excel on a Windows Computer
Make the same-row comparison case-sensitive
Excel’s ordinary not-equal comparison does not distinguish text that differs only by capitalization. If ABC and abc must count as different, use =NOT(EXACT(A2,B2)) as the Conditional Formatting formula instead. Microsoft states that EXACT is case-sensitive but ignores formatting differences: it checks the text, not differences such as cell fill or font formatting.
Find values missing from the other column
When entries may be on different rows, select the data cells in column A and create a formula-based Conditional Formatting rule with =COUNTIF($B:$B,A2)=0. This highlights each selected A value that has no match anywhere in column B. To find values in B absent from A, select the B data cells and reverse the references: =COUNTIF($A:$A,B2)=0.
Rank #2
- Instant Copilot. Unlock new possibilities with the dedicated Copilot key, which gives you instant access to experiences that can enhance your productivity¹.
- Enhance your experience With the new microphone mute key and snipping key
- Full keyboard experience. Features a full mechanical keyset, backlit keys, and a large trackpad for precise navigation and control. Optimal key spacing allows fast, fluid typing.
- Slim and compact Performs like a traditional, full-size keyboard.
- Clicks in place instantly Use in combination with the Surface Pro (11th Edition), Pro 9 and Pro 8* kickstand for a perfect laptop experience anywhere.
COUNTIF matching is case-insensitive and supports wildcard characters. Microsoft also cautions that criteria strings longer than 255 characters can produce incorrect results; extra spaces, inconsistent quotation marks, and nonprinting characters can lead to unexpected matches or misses. See Microsoft’s COUNTIF documentation. If the highlights do not make sense, inspect the source values for those characters and clean or verify the data before relying on the comparison.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Check how blank cells should be treated
With the same-row rule, two blank cells compare as equal, so that pair is not highlighted; a blank on one side and a populated cell on the other is a difference. With the membership rule, a blank in A is tested against column B like any other criterion, so whether it is flagged depends on whether B contains a blank that COUNTIF treats as a match. Decide whether blanks mean “missing data” or a legitimate equal value, and verify a few blank cases in your workbook.
Rank #3
- EXCEL SHORTCUTS. ZERO SEARCHING. – Our bestselling reference mat puts an extensive collection of commonly used commands, formulas and helpful tricks directly beneath your fingertips so you can find answers fast, work smarter and stay in the flow.
- YOUR DESK. SMARTER. – Clearly organized sections for navigation, selection, formatting, data and functions make it easy to find the right Excel command exactly when you need it.
- LEARN, WORK & RESET – Built-in desk-exercise diagrams give you 10 quick ways to stretch, recharge and return to work feeling sharper.
- ROOM TO WORK & CREATE – The extended 31.5 x 11.8-inch Pixiecube desk mat fits a laptop or keyboard and mouse, while the soft 2 mm surface adds comfort and protects your desktop.
- BUILT FOR REAL-WORLD WORKDAYS – A rugged stitched edge helps prevent fraying, and the water-resistant, stain-resistant surface protects against scratches, spills and everyday wear—because smarter desks should work harder.
Compare two workbook versions
If the goal is to inspect changes across workbook versions—not merely compare two columns—Microsoft’s Spreadsheet Compare can report cell-by-cell differences in values, formulas, and formatting. Microsoft says it is available only with Microsoft 365 Apps for enterprise, so check your installed edition before looking for the utility. Details are in Microsoft’s Spreadsheet Compare guidance.
Quick Recap
Best Value
- 💻 ✔️ EVERY ESSENTIAL SHORTCUT - With the SYNERLOGIC Reference Keyboard Shortcut Sticker, you have the most important shortcuts conveniently placed right in front of you. Easily learn new shortcuts and always be able to quickly lookup commands without the need to “Google” it.
- 💻✔️ Work FASTER and SMARTER - Quick tips at your fingertips! This tool makes it easy to learn how to use your computer much faster and makes your workflow increase exponentially. It’s perfect for any age or skill level, students or seniors, at home, or in the office.
- 💻 ✔️ New adhesive – stronger hold. It may leave a light residue when removed, but this wipes off easily with a soft cloth and warm, soapy water. Fewer air bubbles – for the smoothest finish, don’t peel off the entire backing at once. Instead, fold back a small section, line it up, and press gradually as you peel more. The “peel-and-stick-all-at-once” method only works for thin decals, not for stickers like ours.
- 💻 ✔️ Compatible and fits any brand laptop or desktop running Windows 10 or 11 Operating System.
- 💻 ✔️ Original Design and Production by Synerlogic Electronics, San Diego, CA, Boca Raton, FL and Bay City, MI, United States 2020. All rights reserved, any commercial reproduction without permission is punishable by all applicable laws.
Rank #4
- Efficient Media Controls: The Wired Keyboard 600, designed by Microsoft, features a Media Center with four hot keys for easy control of play/pause, volume up, volume down, and mute functions.
- Quiet and Responsive Keys: Enjoy a comfortable typing experience with quiet, thin-profile keys that are both responsive and efficient.
- Convenient Shortcuts: Quickly access common tasks with dedicated shortcut keys, including a calculator hot key and a Windows start screen key.
- Spill-Resistant Design: Work confidently with a spill-resistant design that protects your keyboard from accidental messes.
- Plug-and-Play Simplicity: No software needed—just connect the keyboard to your PC and start using it right away, with a full number pad for efficient data entry.
Troubleshoot unexpected highlights
- Every row appears different: Confirm that the selected columns really contain paired records, then check that the formula’s row number matches the first row of the rule’s applied range.
- Only one side is highlighted: The applied range determines which cells receive formatting. Select both columns if both cells in a differing pair should be colored.
- List values appear missing despite looking alike: Check for leading or trailing spaces, nonprinting characters, inconsistent quotation marks, and text longer than 255 characters when using COUNTIF.
- Capitalization-only differences are missed: Use the EXACT formula for aligned rows; COUNTIF remains case-insensitive for list membership.
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.
