Decimal to binary conversion for large numbers in Excel

If we are talking positive number between 0 and 2^32-1 you can use this formula:

=DEC2BIN(MOD(QUOTIENT($A$1,256^3),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^2),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^1),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^0),256),8)

NOTE: =DEC2BIN() function cannot handle numbers larger than 511 so as you see my formula breaks your number into four 8-bit chunks, converts them to binary format and then concatenates the results.

Well, theoretically you can extend this formula up to six 8-bit chunks. Maximum precision you can get in Excel is 15 (fifteen) decimal digits. When exceeded, only the most significant 15 digits remain, the rest is rounded. I.e. if you type 12345678901234567 Excel will store it as 12345678901234500. So since 2^48-1 is 15 decimal digits long the number won't get rounded.

Perhaps a simpler option:

For positive numbers only, just use BASE (as in BASE2) for numbers between 0 to 2^53 in Excel. Here are some examples:

=BASE(3,2)  # returns 11=BASE(11,2)  # returns 1011

Credit for answer goes here:https://ask.libreoffice.org/en/question/69797/why-is-dec2bin-limited-to-82bits-in-an-32-and-64-bits-world/

Negative numbers: Come to think of it, negative numbers could be handled as well by building upon howy61's answer. He shifts everything by a power of two (2^31 in his case) to use the 2's complement:

=BASE(2^31+MyNum, 2)

so (using 2^8 for only 8 bits):

=BASE(2^8+(-1),2)  # returns 11111111 =BASE(2^8+(-3),2)  # returns 11111101

The numbers given by the OP requires more bits, so I'll use 2^31 (could go up to 2^53):

=BASE(2^31+(-12457896),2)  # returns 11111111010000011110100001011000

For either positive or negative, both formulas could be coupled in a single IF formula. Here are two ways you could do it that give the same answer, where MyNum is the decimal number you start with:

=IF(MyNum<0, BASE(2^31+MyNum,2), BASE(MyNum, 2))

or

=BASE(IF(MyNum<0, MyNum+2^32, MyNum), 2)

See VBA posted here

' The DecimalIn argument is limited to 79228162514264337593543950245' (approximately 96-bits) - large numerical values must be entered' as a String value to prevent conversion to scientific notation. Then' optional NumberOfBits allows you to zero-fill the front of smaller' values in order to return values up to a desired bit level.Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String  DecToBin = ""  DecimalIn = CDec(DecimalIn)  Do While DecimalIn <> 0    DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin    DecimalIn = Int(DecimalIn / 2)  Loop  If Not IsMissing(NumberOfBits) Then    If Len(DecToBin) > NumberOfBits Then      DecToBin = "Error - Number too large for bit size"    Else      DecToBin = Right$(String$(NumberOfBits, "0") & _      DecToBin, NumberOfBits)    End If  End IfEnd Function