The HEX2BIN function in Excel converts a hexadecimal (hex) number into binary, the version of the same number written with only 0s and 1s. So 2D becomes 101101, and FF becomes 11111111.
It’s useful when you have hex values in your sheet (color codes, byte values, numbers from a datasheet or a piece of code) and you want to see the actual bits behind them.
There are two catches with this function. It only handles small numbers (up to 1FF, which is 511), and it returns a single #VALUE! error when you give it a whole range of cells instead of one value (Example 2 shows the fix).
Follow along with the example file
HEX2BIN Function Examples.xlsx
HEX2BIN Function Syntax
Here is the syntax of the HEX2BIN function:
=HEX2BIN(number, [places])
- number is the hexadecimal number you want to convert. It can have up to 10 characters, and the letters can be uppercase or lowercase. This argument is required.
- places is the number of characters you want in the result. This argument is optional.
HEX2BIN can only return a binary number of up to 10 digits, and it uses the leftmost digit to mark negative numbers. That leaves nine digits for positive numbers, so the largest positive hex number it can convert is 1FF (111111111, or 511 in decimal).
Anything bigger than 1FF, like 200, gives you a #NUM! error.
When you leave out places, you get the shortest binary result possible. So =HEX2BIN("1F") returns 11111.
When you use places, HEX2BIN adds zeros in front of the result until it’s that many characters long. So =HEX2BIN("1F",8) returns 00011111.
Hex numbers from FFFFFFFE00 to FFFFFFFFFF are read as negative numbers (-512 to -1). I’ll show you how that works in Example 5.
The HEX2BIN function works in all Excel versions, including Microsoft 365, Excel 2024, 2021, 2019, 2016, Mac, and the web. The formulas in this tutorial that fill several cells at once need Microsoft 365, Excel 2024, or Excel 2021.
When to Use the HEX2BIN Function
Use this function when you need to:
- Convert a list of hex numbers into binary.
- Get fixed-width binary values, such as 8-bit codes like 00001010.
- Break a hex color code (like #FF7F50) into the binary value of its red, green, and blue parts.
- See the bits behind hex values you’ve copied from a datasheet or a homework problem.
Let me show you a few practical examples of how to use HEX2BIN.
Example 1: Convert Hex to Binary in Excel
Let’s start with a simple example.
Below is a list of hex numbers, and I want to convert each one into binary in column B.
Here is the formula, entered in cell B2 and copied down to B11:
=HEX2BIN(A2)
The formula converts each hex number in column A into binary. For example, 2D becomes 101101, B4 becomes 10110100, and 13C becomes 100111100.
If you want to see how this works, take each hex character on its own and write it as four binary digits. B is 1011 and 4 is 0100, so B4 becomes 10110100.
Lowercase letters work fine too. That’s why c7 in cell A6 converts to 11000111, exactly as C7 would.
Also notice that the results sit on the left side of the cells. That’s because HEX2BIN always returns text, even though the result is made up of only 0s and 1s.
You’ll get a #NUM! error if the hex number has a character other than 0 to 9 and A to F, a space before or after it, or a prefix like 0x. The same error shows up for any positive number above 1FF.
Important: I’ve stored the hex numbers in column A as text. If you type 1E3 into a regular cell, Excel reads it as 1000 in scientific notation (1.00E+03), and HEX2BIN returns #NUM! instead of 111100011.
To avoid this, format the column as Text before you enter the hex numbers, or start an entry with an apostrophe (‘1E3) so Excel keeps it as it is.
I’ve used one formula per row here because it’s the simplest form, and it works in every Excel version. In the next example, I’ll show you how to convert the whole list with one formula.
Example 2: Convert a List of Hex Numbers With One Formula
Now let’s convert an entire list in one go.
Below is another list of hex numbers, and I want the binary value of each one in column B using a single formula.
Here is the formula:
=HEX2BIN(+A2:A11)
This one formula converts all ten hex numbers, and the results spill down from B2 (Excel fills the cells below automatically). For example, 3A becomes 111010, and 1B0 becomes 110110000.
The plus sign before A2:A11 is what makes this work. Here is what you get without it, which I’ve entered in cell D2:
=HEX2BIN(A2:A11)
This time there’s just one #VALUE! error in D2, and nothing spills. HEX2BIN is one of the older functions that won’t accept a range of cells as its input, even in Microsoft 365.
Adding the plus sign turns A2:A11 into a calculation, so Excel reads the values in those cells first. HEX2BIN then gets a list of values instead of a cell range, and it returns one result for each of them.
You may have seen a double minus (–) used for the same job, but it doesn’t work here. It tries to turn every hex number into a regular number, and a value like E7 or 9C can’t become one, so it returns #VALUE!.
You can read more about the plus-sign trick in my article on Excel functions that won’t spill.
If you are using Excel 2019 or earlier, enter the below formula in cell B2 and then copy it down to B11:
=HEX2BIN(A2)
Example 3: Get Fixed-Width Binary With the Places Argument
Here’s another practical scenario.
Below is a list of one- and two-digit hex values, and I want each one as an 8-bit binary number (always eight digits long) in column B.
Here is the formula:
=HEX2BIN(+A2:A11,8)
The 8 in the formula is the places argument, and it tells HEX2BIN to return eight characters. So shorter results get zeros added in front: 5 becomes 00000101, 0A becomes 00001010, and D becomes 00001101.
Results that already need all eight digits, like FF (11111111) and 80 (10000000), stay as they are. Every hex value from 00 to FF fits in eight binary digits, which is why two-digit hex values are so often shown as 8-bit binary.
Important: If the result needs more characters than you ask for, HEX2BIN returns #NUM! instead of cutting it short. For example, =HEX2BIN("1A0",8) fails because 1A0 is 110100000 in binary, which needs nine digits.
The places value itself has to be between 1 and 10, and a decimal like 8.9 is cut down to 8. A 0, a negative number, or anything above 10 gives you #NUM!, and text like “a” gives you #VALUE!.
Example 4: Convert Hex Color Codes to Binary
Now let’s look at something a bit more interesting.
Below is a list of colors with their hex color codes, and I want the binary value of the red, green, and blue parts of each code in columns D, E, and F.
Here is the formula in cell D2:
=HEX2BIN(MID(B2:B9,{2,4,6},2),8)
A hex color code is a # followed by two characters for red, two for green, and two for blue. So in Coral’s code #FF7F50, FF is the red part, 7F is green, and 50 is blue.
How this formula works:
MID(B2:B9,{2,4,6},2)uses the MID function to pull three two-character pieces out of each color code, starting at the 2nd, 4th, and 6th character (right after the # and then every two characters). For Coral, that gives you FF, 7F, and 50.- The {2,4,6} is written as a row (with commas), so MID returns the three pieces side by side. That’s why the results fill three columns.
HEX2BIN(...,8)then converts each piece into an 8-digit binary number. So Coral’s red, green, and blue parts become 11111111, 01111111, and 01010000.
Notice that this formula doesn’t need the plus sign. MID already returns a list of values (not a cell range), and HEX2BIN accepts that without any trouble.
The places argument keeps every color part at eight digits, so the columns line up and are easy to compare. Without it, Teal’s red part (00) would come back as just 0, and Coral’s green part (7F) as 1111111.
Example 5: Convert Negative Hex Numbers to Binary
Let’s step it up with a trickier case.
Below is a list of 10-character hex numbers (some positive and some negative), and I want their binary values in column B, with the positive ones padded to eight digits.
Here is the formula:
=HEX2BIN(+A2:A9,8)
The hex numbers that start with 0 are positive, so they convert normally and get padded to eight digits. For example, 000000002A becomes 00101010, and 00000000C8 becomes 11001000.
The ones that start with F are negative. HEX2BIN reads hex numbers using a system called two’s complement, where the leftmost bit tells Excel whether the number is negative.
Each negative result is a 10-digit binary number, and there’s an easy way to read it. Count the leftmost digit as -512, and read the other nine digits as a regular binary number.
For example, FFFFFFFFF6 becomes 1111110110. The other nine digits (111110110) are 502 in binary, so the number is -512 + 502, which is -10.
In the same way, FFFFFFFFFF is -1 (1111111111), FFFFFFFF9C is -100 (1110011100), and FFFFFFFE00 is -512 (1000000000).
You’ll notice that every negative result has 10 digits, even though I asked for 8. That’s because HEX2BIN ignores the places argument for negative numbers.
FFFFFFFE00 (-512) is the smallest number HEX2BIN can handle. Anything below it, like FFFFFFFDFF, returns #NUM!, because the result wouldn’t fit in 10 binary digits.
Important: Leading zeros count toward HEX2BIN’s 10-character limit. So 000000002A (10 characters) works, but 0000000002A (11 characters) returns #NUM!, even though it’s the same number.
If you need the decimal value of these numbers instead, the HEX2DEC function reads them the same way and returns -1, -10, -100, and so on.
Example 6: Convert Hex Numbers Larger Than 1FF to Binary
Now let’s look at what to do when your hex numbers are too big for HEX2BIN.
Below is a list of 4-digit hex numbers, and I want each one as a 16-digit binary number in column B.
HEX2BIN can’t do this on its own, because every one of these numbers is bigger than 1FF. Instead, you can combine the DECIMAL function with the BASE function.
Here is the formula in cell B2:
=BASE(DECIMAL(A2:A9,16),2,16)
How this formula works:
DECIMAL(A2:A9,16)reads each hex number (base 16) and turns it into a regular decimal number. For example, 03E8 becomes 1000.BASE(...,2,16)converts that decimal number into base 2, which is binary. The 16 makes every result at least 16 digits long, so 0200 becomes 0000001000000000 and not just 1000000000.
Unlike HEX2BIN, both these functions accept a range directly, so you don’t need the plus sign here.
To show you what HEX2BIN does with these numbers, I’ve entered it in cell D2:
=HEX2BIN(+A2:A9)
Every result is a #NUM! error, because each hex number is larger than 1FF.
The BASE and DECIMAL functions were added in Excel 2013. If you are using Excel 2013, 2016, or 2019, enter the below formula in cell B2 and then copy it down to B9:
=BASE(DECIMAL(A2,16),2,16)
Important: This method reads every hex number as positive, so FFFFFFFFFF gives you forty 1s instead of the negative value HEX2BIN returns. It also works only up to 1FFFFFFFFFFFFF (53 binary digits), and a larger hex number returns #NUM!.
In this tutorial, I showed you how the HEX2BIN function converts hex numbers to binary, how to convert a whole list with the plus-sign trick, and how the places argument gives you fixed-width results.
I also covered how to turn hex color codes into binary, how HEX2BIN handles negative numbers, and what to use when a hex number is bigger than 1FF. I hope you found this tutorial useful.
Other Excel Articles You May Also Like:
