BITAND in Excel: Test Flags and Extract a Bit Mask
Use Excel BITAND to inspect shared bits, distinguish any from all required flags and extract small numeric fields with checked examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: BITAND(number1,number2) returns the decimal value of the bit positions that are 1 in both inputs. Use it to inspect a numeric flag mask; the result is a number, so compare it explicitly when you need TRUE or FALSE.
Availability and version history
- Introduced: the Microsoft function catalogue marks this identity as an Excel 2013 addition. An exact first build or preview date is not established.
- Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
- Checked: October 14, 2026; official BITAND reference. Documentation and independent models; no native Excel execution or wider platform certification.
Syntax and input limits
=BITAND(number1,number2)
Both arguments are required decimal integers from 0 through 281474976710655, or 2^48−1. Microsoft documents #NUM! for negative, noninteger or oversized arguments, and #VALUE! for nonnumeric arguments. Both zero and an absent bit are meaningful values, not missing records.
Each binary position has a weight: 1, 2, 4, 8, 16 and so on. BITAND retains a weight only when both inputs contain that bit. It does not multiply the numbers or perform the logical AND test across two general conditions.
Use integer masks whose definitions you control. The fictional report options below are ordinary worksheet data, not a claim about any operating system or application’s permission format.
Example 1: inspect four report options
Define these bit weights in D1:E5:
| D, option | E, weight |
|---|---|
| Include summary | 1 |
| Include detail | 2 |
| Include chart | 4 |
| Include appendix | 8 |
Enter Selected mask in A1 and 13 in A2. In F2 enter the following and fill through F5:
=BITAND($A$2,E2)
The expected results are 1, 0, 4 and 8. The mask 13 is binary 1101, or 8+4+1. Summary, chart and appendix are present; detail is absent.
For a Boolean result, put =BITAND($A$2,E2)>0 in G2 and fill down. The outputs are TRUE, FALSE, TRUE and TRUE. The dollar signs lock the selected mask while the option row changes.
If A2 changes to 6, the numeric results become 0, 2, 4 and 0. The selected options are now detail and chart. This is a useful check that the report follows the data rather than hard-coded labels.
Example 2: distinguish any required flag from all flags
Enter 5 and 6 in I2:I3. These are combined requirements: 5 means summary plus chart; 6 means detail plus chart. Keep A2 at 13 for this example.
| Required mask | BITAND(13,mask) |
Any present, >0 |
All present, =mask |
|---|---|---|---|
| 5 | 5 | TRUE | TRUE |
| 6 | 4 | TRUE | FALSE |
In J2 enter =BITAND($A$2,I2) and fill to J3. In K2 use =J2>0; in L2 use =J2=I2; fill both down.
Testing only >0 would approve the second row even though detail is missing. Comparing the masked result with the entire required mask answers the all-flags question.
A zero requirement needs an explicit business convention. Mathematically BITAND(13,0)=0 is TRUE, while BITAND(13,0)>0 is FALSE. That difference is expected: there are no required bits to fail, but no shared set bit either.
Advanced example: extract two four-bit fields
A small packed value stores a high field and a low field, each restricted to integers 0–15. Enter 57 in N2. Its binary representation is 00111001: high field 3, low field 9.
=BITAND(N2,15)
=BITRSHIFT(N2,4)
The first formula returns 9 because mask 15 retains the lowest four bits. The second returns 3 by removing those four bits. Reconstruct the original with:
=BITOR(BITLSHIFT(3,4),9)
The result is 57. This uses the documented BITRSHIFT, BITLSHIFT and BITOR rules in a deliberately small range. Keep the field-width restriction: a low value of 25 would overlap the high field and invalidate that layout.
Errors and review checks
=BITAND(13,0)returns 0;=BITAND(13,13)returns 13.=BITAND(13,6)returns 4, not the required mask 6.=BITAND(1.5,1)and=BITAND(-1,1)have documented#NUM!results.=BITAND("pending",1)has the documented nonnumeric#VALUE!rule.
Do not treat a nonzero mask as proof that every input is valid. Validate the source schema and allowed flags separately. The examples use exact small integers; imported text coercion and extreme native boundary behavior were not executed here.
References and verification
Expected outputs are independently calculated examples or documented rules, not captured native Excel results. Formulas use English function names and comma argument separators. Locale, rendering and boundary restrictions are stated where relevant.