How to Use the BITAND, BITOR, and BITXOR Functions in Google Sheets for Bitwise Operations

In computer engineering, network administration, and embedded IoT data processing, permissions, telemetry codes, and hardware registers are frequently stored as packed bitwise integers. Rather than allocating separate database columns for twenty different boolean flags, developers pack them into a single 8-bit, 16-bit, or 32-bit integer where each individual binary bit represents an independent status flag (e.g. read/write permissions, error alerts, or device state).

To inspect, manipulate, and decode these packed numbers directly in spreadsheets without writing custom Google Apps Scripts, Google Sheets includes native bitwise functions: BITAND, BITOR, and BITXOR. This guide explains their mathematical binary logic, masking techniques, and real-world spreadsheet modeling.

Understanding Bitwise Logic in Spreadsheets

Bitwise operations evaluate two decimal numbers by converting them into their underlying binary representations and applying logical operators bit-by-bit:

• BITAND(value1, value2): Compares each bit position. The resulting bit is 1 only if both corresponding bits are 1; otherwise, it returns 0.
• BITOR(value1, value2): The resulting bit is 1 if either corresponding bit is 1; it returns 0 only when both bits are 0.
• BITXOR(value1, value2): Exclusive OR. The resulting bit is 1 if exactly one of the corresponding bits is 1; it returns 0 if both bits are identical.

Syntax and Parameter Requirements

The syntax for Google Sheets bitwise formulas is consistent across all three functions:

=BITAND(value1, value2)
=BITOR(value1, value2)
=BITXOR(value1, value2)

Parameter Guidelines

• value1, value2: Non-negative integers (must be >= 0). Google Sheets supports bitwise values up to 48-bit integers (2^48 – 1).
• If decimal numbers are passed, Google Sheets truncates the fractional portion automatically.

Practical Example: Decoding Packed Unix File Permissions

In Unix systems, permissions are represented by bit flags:

• Read (r): 4 (Binary 100)
• Write (w): 2 (Binary 010)
• Execute (x): 1 (Binary 001)

Suppose you import a server log containing combined permission values (e.g. 7 = rwx, 5 = r-x, 6 = rw-). To audit whether a specific permission is granted, apply bitwise masking with BITAND:

A1: Mode Value | B1: Read Allowed? (Mask 4) | C1: Write Allowed? (Mask 2) | D1: Execute Allowed? (Mask 1)
A2: 7          | B2: =BITAND(A2, 4) = 4     | C2: =BITAND(A2, 2) = 2      | D2: =BITAND(A2, 1) = 1
A3: 5          | B3: =BITAND(A3, 4) = 4     | C3: =BITAND(A3, 2) = 2      | D3: =BITAND(A3, 1) = 1
A4: 4          | B4: =BITAND(A4, 4) = 4     | C4: =BITAND(A4, 2) = 2      | D4: =BITAND(A4, 1) = 1
A5: 0          | B5: =BITAND(A5, 4) = 4     | C5: =BITAND(A5, 2) = 2      | D5: =BITAND(A5, 1) = 1

Google Sheets evaluates each bitwise check into clean boolean results:

• For Mode 7: Read = TRUE, Write = TRUE, Execute = TRUE.
• For Mode 5: Read = TRUE, Write = FALSE, Execute = TRUE.
• For Mode 4: Read = TRUE, Write = FALSE, Execute = FALSE.

Bitwise Logic Truth Matrix

Bit A	Bit B	BITAND (A, B)	BITOR (A, B)	BITXOR (A, B)
0	0	0	0	0
0	1	0	1	1
1	0	0	1	1
1	1	1	1	0

Companion Bit-Shift Functions

Google Sheets also provides bit-shifting formulas for scaling and bit-packing:

• BITLSHIFT(value, shift_amount): Shifts binary bits to the left (equivalent to multiplying by 2^shift_amount).
• BITRSHIFT(value, shift_amount): Shifts binary bits to the right (equivalent to dividing by 2^shift_amount).