In low-level firmware engineering, communications protocol design, cryptography, and network subnetting, calculations frequently depend on binary logic operations evaluated at the individual bit level. Historically, performing bitwise operations in Microsoft Excel required complex combinations of mathematical powers, modulo arithmetic, and custom VBA macros. Modern versions of Excel solve this natively by providing a dedicated suite of bitwise engineering functions: BITAND, BITOR, BITXOR, BITLSHIFT, and BITRSHIFT.
In this guide, you will learn the mechanics, syntax rules, and practical business use cases of bitwise functions in Microsoft Excel, including bitmask filtering, flag extraction, and binary bit shifting.
Understanding Bitwise Operations in Excel
Bitwise functions operate on numbers converted into their underlying binary representations. Each bit position in one operand is compared directly against the corresponding bit position in the second operand according to boolean logic rules.
Important rules regarding Excel’s bitwise functions:
- Decimal Inputs and Outputs: Although the functions compute logic at the bit level, you provide standard decimal numbers as arguments, and Excel returns the calculated result as a decimal number.
- Input Range Limits: All arguments must be integers greater than or equal to 0 and less than
2^48(numbers from0to281,474,976,710,655). Supplying negative numbers returns a#NUM!error. - Non-Integer Truncation: If fractional decimal values are provided, Excel automatically truncates them to integers before evaluation.
1. The BITAND Function (Bitwise AND)
The BITAND function compares two numbers bit by bit. A bit in the result is set to 1 if and only if both corresponding bits in the inputs are 1; otherwise, the bit is set to 0.
Syntax
=BITAND(number1, number2)Truth Table Comparison
| Bit 1 | Bit 2 | BITAND Result |
|---|---|---|
| 0 | 0 | 0 |
| 0 | 1 | 0 |
| 1 | 0 | 0 |
| 1 | 1 | 1 |
Example: Comparing decimal 13 (binary 1101) and decimal 11 (binary 1011):
=BITAND(13, 11)Result: 9 (binary 1001).
Practical Use Case: Testing Configuration Flags
In software logging and permissions, multiple boolean settings are commonly stored in a single integer bitmask. If bit 4 (value 8) represents “Admin Access”, you can verify if a user with permission code 27 has this flag enabled:
=IF(BITAND(A2, 8) = 8, "Admin Enabled", "Standard User")2. The BITOR Function (Bitwise OR)
The BITOR function returns a bit of 1 if either (or both) of the corresponding bits in the inputs is 1.
Syntax
=BITOR(number1, number2)Example: Combining decimal 9 (binary 1001) and decimal 6 (binary 0110):
=BITOR(9, 6)Result: 15 (binary 1111).
Use BITOR to combine separate flag masks into a consolidated configuration setting.
3. The BITXOR Function (Bitwise Exclusive OR)
The BITXOR function returns a bit of 1 if exactly one of the corresponding bits is 1, and 0 if both bits are identical (both 0 or both 1).
Syntax
=BITXOR(number1, number2)Example:
=BITXOR(10, 6)- 10 in binary:
1010 - 6 in binary:
0110 - XOR result:
1100(decimal12)
In network diagnostics and error-detection parity schemes, BITXOR is essential for checksum calculations and toggle states.
4. Bit Shifting: BITLSHIFT and BITRSHIFT
Bit shifting moves all binary digits of a number to the left or right, effectively multiplying or dividing by powers of 2 with high computational efficiency.
BITLSHIFT (Bitwise Left Shift)
Shifting bits to the left multiplies the number by 2^shift_amount:
=BITLSHIFT(number, shift_amount)Example: Shift decimal 5 (binary 101) left by 3 positions:
=BITLSHIFT(5, 3)Result: 40 (binary 101000, which is 5 × 2³ = 40).
BITRSHIFT (Bitwise Right Shift)
Shifting bits to the right divides the integer by 2^shift_amount, discarding any remainder:
=BITRSHIFT(number, shift_amount)Example: Shift decimal 40 right by 2 positions:
=BITRSHIFT(40, 2)Result: 10 (which is 40 / 2² = 10).
Summary of Function Differences
| Function | Core Bitwise Operation | Common Use Case |
|---|---|---|
BITAND(n1, n2) |
Sets bit to 1 if both bits are 1. | Extracting flags and applying submasks. |
BITOR(n1, n2) |
Sets bit to 1 if at least one bit is 1. | Merging attributes and combining permission masks. |
BITXOR(n1, n2) |
Sets bit to 1 if bits are different. | Parity checking and bit toggling. |
BITLSHIFT(n, s) |
Shifts bits left (multiplication by 2^s). | Encoding byte positions and fast scaling. |
BITRSHIFT(n, s) |
Shifts bits right (integer division by 2^s). | Decoding high-order bytes and subnet masking. |
Troubleshooting Bitwise Errors
#NUM!Error: Occurs if an argument is negative, if an argument is greater than or equal to2^48, or if the shift amount causes an overflow beyond the 48-bit limit.#VALUE!Error: Occurs when non-numeric text strings are supplied as arguments.