Excel BITAND and BITOR Functions

last modified October 5, 2026

The BITAND and BITOR functions perform bitwise operations in Excel. They work directly with the binary representation of numbers, which makes them useful for flags, permissions, and any data that is naturally packed into bits. This tutorial covers their syntax, the binary arithmetic behind them, valid input ranges, and detailed examples.

BITAND/BITOR Function Basics

BITAND performs a bitwise AND operation, while BITOR performs a bitwise OR operation. Both functions compare two numbers bit by bit and return a decimal result.

Function	Description	Syntax
BITAND	Returns the bitwise AND of two numbers	=BITAND(number1, number2)
BITOR	Returns the bitwise OR of two numbers	=BITOR(number1, number2)

Each function takes exactly two arguments. Supplying one argument, or three or more, produces Excel's "too few arguments" or "too many arguments" error. Both arguments must be non-negative integers, and the result is always returned as a decimal number.

Argument	Meaning
number1	The first non-negative integer, from 0 to 2^48-1 (281,474,976,710,655). A decimal value is truncated toward zero.
number2	The second non-negative integer, subject to the same range and truncation rules.

How Binary Representation Works

A decimal number is stored as a sequence of bits. The decimal value 13, for example, is 1101 in binary, which is 8 + 4 + 1. Bitwise functions line the two numbers up at their least significant bit and apply a logical rule to each column independently.

A	B	A AND B	A OR B
0	0	0	0
0	1	0	1
1	0	0	1
1	1	1	1

BITAND keeps a bit only when it is 1 in both numbers, so it is the natural tool for masking. BITOR sets a bit when it is 1 in either number, so it is the natural tool for combining flags. For example, 13 (01101) AND 25 (11001) is 01001, or 9, while 13 OR 25 is 11101, or 29.

Basic BITAND Example

This example demonstrates a simple BITAND operation between two numbers.

Basic BITAND formula

=BITAND(5, 3)

The formula compares binary 101 (5) and 011 (3). BITAND returns 1 (001) because only the least significant bit is 1 in both numbers. This shows how BITAND identifies common bits.

Basic BITOR Example

This example shows a simple BITOR operation between two numbers.

Basic BITOR formula

=BITOR(5, 3)

The formula compares binary 101 (5) and 011 (3). BITOR returns 7 (111) because all bits are 1 in at least one number. This demonstrates how BITOR combines bits from both numbers.

Truncation of Decimal Inputs

Excel does not round the arguments; it truncates them toward zero before the operation. The fractional part is simply dropped, so 13.9 becomes 13 and 25.9 becomes 25. The bitwise result therefore matches the one produced by the truncated integers.

Decimals are truncated, not rounded

=BITAND(13.9, 25.9)     ' 9, the same as BITAND(13, 25)
=BITOR(13.9, 25.9)      ' 29, the same as BITOR(13, 25)
=BITAND(-13.9, 25.9)    ' #NUM!, negative arguments are rejected

Truncation happens before the sign check, so a negative decimal such as -13.9 becomes -13 and still triggers #NUM!. Round the values first with ROUND when you want the nearest integer instead of the truncated one.

Valid Input Range

Both arguments must fall in a specific range. Values outside it, or values that are not numbers at all, produce errors rather than being clamped.

Input	Behavior
0 to 2^48-1 (281,474,976,710,655)	Accepted
13.9	Truncated toward zero to 13
-1	#NUM! error
2^48 (281,474,976,710,656) or larger	#NUM! error
"abc" (non-numeric text)	#VALUE! error

Because both inputs are limited to 48 bits, the AND and OR results can never exceed 2^48-1 either, so a valid pair of inputs always yields a valid result.

BITAND with Cell References

A practical use of BITAND involves referencing cells containing values to compare. Here's an example with cell references.

A	B	C
9	5
		=BITAND(A1, B1)

The table shows values in cells A1 (9) and B1 (5), with a BITAND formula in C2 that compares them. The binary representations are 1001 and 0101.

BITAND with cell references

=BITAND(A1, B1)

This formula performs BITAND on 9 (1001) and 5 (0101). The result is 1 (0001) because only the least significant bit matches. This shows how BITAND works with cell references.

BITOR with Multiple Operations

BITOR can be combined with other functions for more complex operations. This example shows BITOR with arithmetic.

A	B	C
6	3
		=BITOR(A1+B1, A1-B1)

The table demonstrates combining arithmetic with BITOR. It calculates (6+3) OR (6-3), which is 9 OR 3 in decimal.

BITOR with arithmetic

=BITOR(A1+B1, A1-B1)

This formula first calculates 6+3=9 (1001) and 6-3=3 (0011), then performs BITOR. The result is 11 (1011) because all bits are set in either number. This shows BITOR's flexibility.

BITAND for Flag Checking

BITAND is commonly used to check specific bits (flags) in a number. This example demonstrates checking whether the bit worth 4 is set.

A	B
13
	=BITAND(A1, 4) = 4

The table shows how to test the bit worth 4 (2^2) in the number 13 (1101). The formula returns TRUE when the bit is present and FALSE when it is not.

BITAND flag check

=BITAND(A1, 4) = 4

This formula checks whether the bit worth 4 is set in 13. BITAND(13,4) returns 4, so the comparison is TRUE. This technique is useful for working with bitmask flags.

BITAND/BITOR with Error Handling

These functions reject negative inputs. This example shows error handling when an invalid input is provided.

A	B	C
5	-3
		=IFERROR(BITAND(A1,B1), "Invalid input")

The table demonstrates handling a negative number with IFERROR. Since BITAND requires non-negative integers, -3 would normally cause an error.

BITAND with error handling

=IFERROR(BITAND(A1,B1), "Invalid input")

This formula attempts BITAND but returns "Invalid input" if an error occurs. This is important because BITAND and BITOR only work with non-negative integers between 0 and 2^48-1.

Combining BITAND and BITOR

These functions can be combined for complex bit manipulations. This example shows setting a specific bit while clearing others.

A	B	C
9	6
		=BITOR(BITAND(A1, B1), 4)

The table demonstrates first performing BITAND on 9 and 6, then BITOR with 4. This technique is useful for precise bit manipulation.

Combined BITAND and BITOR

=BITOR(BITAND(A1, B1), 4)

This formula first does BITAND(9,6)=0 (1001 AND 0110=0000), then BITOR(0,4)=4. The AND operation clears the bits that the two numbers do not share, and the OR operation then forces the bit worth 4 to be set.

Related Bitwise Functions

Excel ships a small family of bitwise functions that share the same non-negative-integer rules as BITAND and BITOR.

Function	Description	Example	Result
BITXOR	Bitwise exclusive OR; a bit is 1 only when the two bits differ	=BITXOR(13, 25)	20
BITLSHIFT	Shifts bits left by n places, multiplying by 2^n	=BITLSHIFT(13, 1)	26
BITRSHIFT	Shifts bits right by n places, dividing by 2^n	=BITRSHIFT(13, 1)	6

For 13 (01101) and 25 (11001), the differing bits give 10100, so BITXOR returns 20. Shifting 13 left by one place turns 01101 into 011010, or 26, and shifting it right by one place discards the last bit, giving 0110, or 6. The shift amount can also be negative, which shifts in the opposite direction.

Combining with Other Functions

Because the results are ordinary numbers, BITAND and BITOR can be nested with other functions. The DEC2BIN function displays a small decimal number as binary text, and BIN2DEC performs the reverse conversion.

Decimal and binary conversion

=DEC2BIN(13)                    ' "1101"
=BIN2DEC("1101")                ' 13
=BITAND(BIN2DEC("1101"), 4)     ' 4
=IF(BITAND(A1, 4) = 4, "on", "off")

The last formula turns the flag test into readable text, returning "on" when the bit worth 4 is set in A1 and "off" otherwise. Note that DEC2BIN only handles the small range -512 to 511, so it is useful for illustrating the operation but not for displaying full 48-bit values.

BITAND/BITOR Errors and Troubleshooting

Cause	Symptom	Fix
An argument is negative	#NUM! error	Use values of 0 or greater; bitwise functions are defined only for non-negative integers
An argument is 2^48 (281,474,976,710,656) or larger	#NUM! error	Keep every argument at or below 2^48-1 (281,474,976,710,655)
An argument is non-numeric text	#VALUE! error	Convert the value with VALUE or correct the source cell
Wrong number of arguments	"too few arguments" or "too many arguments"	Supply exactly two arguments
A decimal is expected to round	Result looks one lower than expected	Arguments are truncated toward zero; round first with ROUND if needed
A referenced cell holds an error	The same error is returned	Fix the upstream error; BITAND cannot mask it

The BITAND and BITOR functions bring true bitwise processing to Excel. BITAND is the tool for masking and testing flags, while BITOR is the tool for combining them. Both accept exactly two non-negative integers between 0 and 2^48-1, truncate decimals toward zero, and return a decimal result. Once you are comfortable with the binary view of numbers, these functions make flag handling and packed-data workflows far simpler and more readable.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.

Ebooks