A user has permissions: read, write, and delete. A game character carries status flags: poisoned, shielded, invisible. A hardware register packs eight on-off switches into a single byte. In each case, many yes-or-no settings hide inside one number. To read or change them, you work at the level of individual bits. Excel gives you the tools with BITAND, BITOR, and BITXOR. These bitwise functions compare two numbers bit by bit, which is exactly how flags and permissions are managed under the hood.
This guide explains bitwise operations in plain language. First, it shows what happens at the level of single bits. Then it covers each function and the shift operators that go with them. Seven worked examples and a full troubleshooting section follow. By the end, you will pack, read, and toggle flags with confidence.
What Bitwise Operations Do
Every whole number has a binary form underneath. Bitwise functions line up two numbers by their bits. Then they compare each pair of bits in place. The infographic below shows all three operators at once. Each column of bits is judged on its own.
AND keeps a bit only when both inputs have it. OR keeps a bit when either input has it. XOR keeps a bit when exactly one input has it. Consequently, each operator answers a different yes-or-no question about the bits. This bit-level logic is the foundation of flags and masks. It is far more efficient than storing many separate columns.
The Three Core Functions
Each function takes two numbers as input. Both must be non-negative whole numbers. The result is a new number built bit by bit. The rule for each operator is easy to state plainly.
Example 1: Combine Flags with BITOR
Start by packing several flags into one number. You assign each flag a power of two first. BITOR then merges them together. The single result holds every chosen flag. This is how one field stores many options.
Example 2: Check a Flag with BITAND
Next you need to test whether a flag is set. BITAND masks the value against a single flag. A non-zero result means the flag is present. This is the standard permission check everywhere. You will use it constantly with packed values.
Example 3: Toggle a Flag with BITXOR
Sometimes you want to flip a setting quickly. BITXOR toggles a flag on or off. Applying it once turns the flag on. Applying it again turns it back off. That reversibility is exactly what a switch needs.
Example 4: Remove a Flag
Turning a flag off cleanly needs a small trick. You combine BITAND with the complement here. The idea is to keep every bit except the one you drop. This removes a permission safely and repeatably. It works even if the flag was already off.
Example 5: Shift Bits with BITLSHIFT
Two more functions move bits sideways along the number. BITLSHIFT shifts bits left, which doubles the value each step. BITRSHIFT shifts right, which halves it instead. Shifting is a fast way to multiply or divide by powers of two. Low-level code uses it heavily for speed.
Example 6: A Permissions Table
A table documents a permission scheme clearly. You list each user permission number down a column. Then BITAND checks each flag in its own column. The layout reads like an access matrix.
Each Yes or No comes from a BITAND test against that flag. The permission value drives every column. This turns a cryptic number into a readable access list. Auditors and admins can scan it at a glance. One stored number expands into a full permission view.
Example 7: Guard Negative or Fractional Inputs
Bitwise functions reject negatives and fractions. Both inputs must be non-negative whole numbers. A short guard catches bad values first. This keeps a shared model stable.
Troubleshooting Bitwise Functions
All three problems below are the most common. Each has a clear cause and a quick fix.
You get a #NUM! error
This error means an input is out of range. Bitwise functions require non-negative whole numbers, so a negative value fails at once. A fractional number also causes the error, since bits are only defined for integers. Check both arguments and make sure they are whole and zero or above. There is also an upper limit: the inputs must be below 2 to the power 48. If a value exceeds that ceiling, the function returns the error. Once every input is a valid non-negative integer within range, the calculation succeeds.
A flag check always returns the wrong answer
If a permission test never behaves, the flag values may not be powers of two. For bitwise flags to work, each one must own a unique bit, so use 1, 2, 4, 8, and so on. A value like 3 is really two flags combined, not a single flag, so testing against it gives confusing results. Confirm that each individual flag is a distinct power of two. Then use BITAND against that single flag and check for a non-zero result. Assigning clean power-of-two values to each flag resolves almost every logic problem.
Adding flags sometimes gives a wrong total
If combining flags by adding them occasionally misfires, the cause is usually a doubled flag. Simple addition works only when each flag is present at most once, because adding the same flag twice carries into the next bit and corrupts the result. The safe way to combine flags is BITOR, which sets a bit without ever carrying. Similarly, use BITAND with a complement to remove a flag rather than subtracting, which fails if the flag was absent. Preferring the bitwise functions over plain addition and subtraction keeps flag maths reliable.
Frequently Asked Questions
- What do the BITAND, BITOR, and BITXOR functions do?Essentially, these three functions compare two whole numbers at the level of their individual binary bits. BITAND returns a bit as 1 only when both numbers have that bit set, which is ideal for testing a flag. BITOR returns a bit as 1 when either number has it, which combines flags into one value. BITXOR returns a bit as 1 when exactly one number has it, which toggles a flag on or off. All three require non-negative whole numbers below 2 to the power 48. Together they let a single number store and manage many yes-or-no settings, exactly as permissions and hardware flags work.
- How do I check whether a specific flag is set?Specifically, you use BITAND to mask the combined value against the single flag you want to test. If the result is non-zero, the flag is present; if it is zero, the flag is absent. For example, if permissions equal 3 and the WRITE flag is 2, then =BITAND(3, 2) returns 2, confirming write access, while =BITAND(3, 4) returns 0, showing no delete access. Wrapping this in an IF makes it readable, such as =IF(BITAND(perms, 2) > 0, "Yes", "No"). This masking technique is the standard, reliable way to read a single setting out of a packed value.
- Why must my flags be powers of two?Because each power of two occupies its own single bit, using values like 1, 2, 4, 8, and 16 ensures that every flag is independent and never overlaps another. When flags each own a distinct bit, you can combine them with BITOR and test them with BITAND without any interference. If you instead used a value like 3, it would actually represent two bits at once, making it impossible to treat as a single flag and producing confusing test results. So assigning a unique power of two to each flag is the foundation of the whole technique, and it keeps combining, checking, and toggling flags completely predictable.
- What do BITLSHIFT and BITRSHIFT do?Notably, BITLSHIFT and BITRSHIFT move the bits of a number left or right by a chosen amount. A left shift moves bits toward higher place values, which doubles the number for each position shifted, so =BITLSHIFT(1, 3) returns 8. A right shift moves bits toward lower place values, halving the number and discarding bits that fall off the end, so =BITRSHIFT(16, 2) returns 4. In effect, shifting left by n multiplies by 2 to the power n, and shifting right divides by the same amount. These operators are a fast, low-level way to scale by powers of two and to position flags within a packed value.