BITAND, BITOR & BITXOR Bitwise Logic
Learn how to perform bitwise logic in Excel with BITAND, BITOR, and BITXOR. Mask binary permission flags, test sensor registers, and decode bit status fields.
Database administrators and software engineers frequently store complex combinations of permissions inside a single integer called a bitmask. Each bit position in the binary version of that number represents one specific capability:
- Bit 0 (
1): Read Access - Bit 1 (
2): Write Access - Bit 2 (
4): Execute Access - Bit 3 (
8): Delete Access
For example, a user with permission code 11 (8 + 2 + 1 = binary 1011) has Read, Write, and Delete access, but cannot Execute. Excel provides bitwise operations to test and modify these permissions without writing macros: BITAND, BITOR, and BITXOR.
Quick Comparison: Bitwise Operators
| Formula | Bit Logic Rule | Common Application |
|---|---|---|
BITAND(n1, n2) |
Returns 1 only where both bits are 1 | Testing whether a specific permission bit is enabled (masking). |
BITOR(n1, n2) |
Returns 1 if either bit is 1 | Adding new rights or combining permission flags. |
BITXOR(n1, n2) |
Returns 1 if bits differ (exclusive OR) | Toggling a permission switch on/off or finding differences. |
Deep Technical Mechanics
1. Direct Decimal Input
You do not need to convert your numbers to binary first. Excel accepts regular decimal integers, evaluates their underlying binary bits, and returns the resulting decimal integer directly.
2. Testing a Permission (Bitmasking)
To verify if a user has Write permissions (mask value 2), test with BITAND:
=IF(BITAND(B2, 2) > 0, "Can Write", "Read Only")If the user has write permissions, BITAND(B2, 2) returns 2. If not, it returns 0.
3. Bit Shifting
Excel also provides BITLSHIFT and BITRSHIFT to shift bits left or right, effectively multiplying or dividing integers by powers of 2.
Hands-on Practice Playground
Use the user security table below to inspect, grant, and toggle system permissions. Follow each objective to complete the exercise.
In cell C2, test whether user USR-101 has Write permission (mask 2) using BITAND.
In cell C2, test whether user USR-101 has Write permission (mask value 2) by applying the BITAND formula to permission code B2 and mask 2.
In cell D2, grant Delete permission (mask 8) using BITOR.
In cell D2, grant Delete permission (bitmask 8) to user USR-101 by combining current code B2 and 8 using BITOR.
In cell E2, toggle Execute permission (mask 4) using BITXOR.
In cell E2, toggle the Execute permission (bitmask 4) for user USR-101 by applying BITXOR between code B2 and 4.
Share This Lesson
Spread the word with your peers and challenge your friends