Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel
  • How to use Excel OFFSET function
  • Basic INDEX MATCH approximate in Excel
  • Approximate match with multiple criteria in Excel

Data Analysis Examples

  • Reverse List in Excel
  • Conditional Formatting Rules in Excel
  • How to count table columns in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Move chart to a separate worksheet in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation only dates between
  • Excel Data validation unique values only
  • Excel Data validation don’t exceed total
  • Excel Data validation require unique number

Highlight column differences in Excel

by

This tutorial shows how to Highlight column differences in Excel using the example below;

Formula

=$A1<>$B1

Explanation

If you want to highlight the differences between two columns of data with conditional formatting you can do so with a simple formula that uses the” not equal to” operator (e.g. <>) and mixed references.

For example, if you have similar data in B2:B11 and C2:C11, and you want to highlight cells where values differ, select the data in both columns, starting from B2, and use this formula:

=$B2<>$C2

Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B2 in this case.

Worked Example:   Count cells that contain numbers in Excel

How this formula works

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 20 cells in the two columns of data.

Worked Example:   How to test for all values in a range are at least in Excel

The references to $B2 and $C2 are “mixed” – the column is locked, but the row is relative – so only the row number will change as the formula is evaluated. Whenever two values in a row are not equal, the formula returns TRUE and the conditional formatting is applied.

Worked Example:   If NOT this or that in Excel

A case-sensitive option

By the “equals to” and “not equals to” operators (= and <>) are not case-sensitive. If you need a case-sensitive comparison, you can use the EXACT function with NOT, like so:

=NOT(EXACT($B2,$C2))

Exact performs a case-sensitive comparison and returns TRUE when values match. NOT reverses this logic so that the formula returns TRUE only when the values don’t match.

Post navigation

Previous Post:

Highlight cells that equal in Excel

Next Post:

Highlight data by quartile in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains one of many things in Excel
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • Remove line breaks in a cell in Excel
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • UNICODE function: Description, Usage, Syntax, Examples and Explanation
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate percent of year complete in Excel
  • Get first Monday before any date in Excel
  • Get work hours between dates custom schedule in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles