BITXOR Function

Returns the bits that differ between two numbers (bitwise exclusive OR).

Category

Engineering

Available in

Excel 2013+, Microsoft 365

Returns

A whole number

Want to practice BITXOR in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

BITXOR compares two numbers in binary and returns a 1 where exactly one of them has a 1 — where they differ.

Use it to flip a flag (on becomes off, off becomes on) or to see which switches changed between two codes.

Syntax

=BITXOR(number1, number2) Explain

Arguments

ArgumentRequired?What it does
number1YesThe first whole number.
number2YesThe second whole number.

Examples

Example 1 — How the bits combine

Each row combines the two numbers bit by bit; the answer is shown as an ordinary decimal number.

C2fx=BITXOR(A2,B2)
ABC
1Number 1Number 2XOR
2536
312106
4770
=BITXOR(A2,B2) Explain

Example 2 — What changed?

Compare last month's access code with this month's. Any non-zero answer shows which switches moved.

D2fx=BITXOR(B2,C2)
ABCD
1PersonBeforeAfterChanged
2Ana1358
3Ben330
4Cy7158
=BITXOR(B2,C2) Explain

8 is the Admin switch: Ana lost it and Cy gained it.

Common mistakes

  • #NUM! Both numbers must be whole numbers from 0 up to 2^48 − 1 (281,474,976,710,655). Negative numbers, decimals and bigger values return #NUM!.

Notes

  • Permission flags are a classic use: give each option a power of two (View = 1, Edit = 2, Approve = 4, Admin = 8) and store a person's access as one number — 13 means View + Approve + Admin.
  • Related functions: BITAND, BITOR, XOR.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.