BITAND Function

Returns the bits two numbers have in common (bitwise AND).

Category

Engineering

Available in

Excel 2013+, Microsoft 365

Returns

A whole number

Want to practice BITAND 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

BITAND compares two numbers in binary, position by position, and keeps a 1 only where both numbers have a 1. 13 is 1101 and 25 is 11001; they share only the 8 and 1 bits, so BITAND(13,25) is 9.

It's the standard way to test a flag: if BITAND(code, 4) is 4, the "4" switch is on.

Syntax

=BITAND(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=BITAND(A2,B2)
ABC
1Number 1Number 2AND
213259
3531
412108
=BITAND(A2,B2) Explain

Example 2 — Who can approve?

Approve is the 4 switch. BITAND keeps only that bit, so anything above zero means yes.

C2fx=BITAND(B2,4)>0
ABC
1PersonCodeCan approve?
2Ana13TRUE
3Ben3FALSE
4Cy7TRUE
5Di8FALSE
=BITAND(B2,4)>0 Explain

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: BITOR, BITXOR, BITLSHIFT, DEC2BIN.
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.