# How to format a Microsoft Excel 2007 cell in hexadecimal?

I would like a format a cell in Microsoft Excel 2007 in hexadecimal but am not finding how to do this.

Is there a built-in way to change the base from 10 to 16 for a cell?

As mentioned earlier, the formula =DEC2HEX(A1) converts to hex, and DEC2HEX(A1,8) converts to hex with 0 prefix to denote 32 bits. While adding the leading 0's makes the numbers more readable, especially if you use a fixed point font, the hex and decimal versions can get mixed up when all of the digits in the number happen to be 0-9 (e.g. 327701=50015).

An improvement is to add the "0x" prefix. There are 2 ways to do this. ="0x"&DEC2HEX(A1,8) will do the trick, but it changes the field to a text field, so it can no longer easily be used in formulas. Another method is to use a custom format. If you apply the custom format "0x"@ to the cell, then the value of the cell can still be used in an equation. Examples:

```
╔═══╦════════════════╦════════════╦═════════════════════════════╦═══════════════╗
║   ║       A        ║     B      ║                             ║               ║
╠═══╬════════════════╬════════════╬═════════════════════════════╬═══════════════╣
║ 1 ║                ║ Value      ║ Corresponding Formula       ║ Number Format ║
║ 2 ║ Decimal        ║ 11162790   ║ 11162790                    ║ General       ║
║ 3 ║ Hex            ║ AA54A6     ║ =DEC2HEX(B2)                ║ General       ║
║ 4 ║ leading 0's    ║ 00AA54A6   ║ =DEC2HEX(B2,8)              ║ General       ║
║ 5 ║ Text 0x prefix ║ 0x00AA54A6 ║ =DEC2HEX(B2,8)              ║ "0x"@         ║
║ 6 ║ Text 0x prefix ║ 0x00AA54A6 ║ ="0x" & DEC2HEX(B2,8)       ║ General       ║
║ 7 ║ Use B5         ║ AA54A600   ║ =DEC2HEX(HEX2DEC(B5) * 256) ║ General       ║
║ 8 ║ Use B5         ║ 0xAA54A600 ║ =DEC2HEX(HEX2DEC(B5) * 256) ║ "0x"@         ║
║ 9 ║ Try to use B6  ║ #NUM!      ║ =DEC2HEX(HEX2DEC(B6) * 256) ║ General       ║
╚═══╩════════════════╩════════════╩═════════════════════════════╩═══════════════╝
```

However, large numbers with varying digits can still be hard to read, so I like to put commas into decimal numbers and "_"s into hex numbers. First you need to get the upper and lower 16 bits of the number. The upper 16 bits of a 32-bit number can be retrieved with = INT( A1 / 2^16 ). Dividing by 2^16 acts like a right shift of 16 bits, and the INT removes the fractional remainder. MOD can be used to get the lower 16 bits of any 32 or 64-bit number; =MOD(A1, 2^16). The MOD effectively gets the remainder back, which is the lower 16 bits. Here is a table that shows this in action.

```╔═══╦═════════════╦═══════════════╦════════════════════════════════════╦═════════╗
║   ║      A      ║      B        ║                                    ║         ║
╠═══╬═════════════╬═══════════════╬════════════════════════════════════╬═════════╣
║ 1 ║             ║ Value         ║ Corresponding Formula              ║ Format  ║
║ 2 ║ Decimal     ║ 3,098,743,209 ║ 3098743209                         ║ #,##0   ║
║ 3 ║ Upper Bytes ║ B8B3          ║ =DEC2HEX(INT( B2/2^16 ), 4)        ║ General ║
║ 4 ║ Lower Bytes ║ 11A9          ║ =DEC2HEX(MOD( B2, 2^16 ))          ║ General ║
║ 5 ║ Full Number ║ 0xB8B3_11A9   ║ ="0x" & DEC2HEX(INT( B2/2^16 ), 4) ║ General ║
║   ║             ║               ║ & "_" & DEC2HEX(MOD(B2, 2^16), 4)  ║         ║
╚═══╩═════════════╩═══════════════╩════════════════════════════════════╩═════════╝
```

BTW, I have to give a big thanks to https://ozh.github.io/ascii-tables/. I used that link to create the ASCII text tables. I just copied the table from my excel spreadsheet into its Input section and it automatically created the nice unicode text layout.

If you want to format a cell so that you can type in a decimal number and automatically have it displayed as a hexadecimal number, then that's not possible. You can either format the cell as text and enter hexadecimal numbers directly (but note that Excel cannot use those for calculations), or use the `DEC2HEX()` and `HEX2DEC()` functions to convert between base 10 and base 16.

• For reference, there is also `DEC2BIN()` which converts to binary. For fixed width binary, use something like `=TEXT(DEC2BIN(A1), "0000")` Jul 28, 2015 at 14:11
• On a German localized Excel 2013, DEC2HEX and HEX2DEC result in the error message #NAME?. With the Office Help, I could find out that the functions are called DEZINHEX and HEXINDEZ, resp. Jul 18, 2016 at 9:41
• How do I extend =0x1234AB to =0x001234AB (I need fixed width of 8)? Jan 27, 2017 at 8:59
• @Danijel Use the second parameter of DEC2HEX. Feb 1, 2017 at 8:46

If cell to be converted is `A1` use `=DEC2HEX(A1)`.

• You may want to add a leading "0x" to your hex value. Use `="0x"&DEC2HEX(A1,4)` to get a result that looks like like `0x1AF2`. Jun 30, 2016 at 22:47

If you need to perform mathematical operations on numbers converted to hex, first convert them to decimal, perform the operation, then convert back to hex.

For example, if cell `A1` has a hex number, such as would be created from this formula:

``````=DEC2HEX(17)
``````

which would be displayed as 11, and cell `A2` has formula:

``````=DEC2HEX(165)
``````

which would be displayed as `A5`, and you want to add them together, then this formula would get the desired result:

``````=DEC2HEX(HEX2DEC(A1)+HEX2DEC(A2))
``````

which would be displayed as `B6`

``````=REPT("0",4-LEN(DEC2HEX(B3)))&DEC2HEX(B3)
``````

The REPT formula repeats the "0" based on the length of the hex string that is generated by converting it from a decimal, and the desired length of the HEX string. In this example I am looking to generate HEX strings of length 4.

You then concatenate the leading zeros to the actual HEX string, thereby generating the HEX value of the desired length.

``````SWITCH(LEN(DEC2HEX(B6)),1,"000"&DEC2HEX(B6),2,"00"&DEC2HEX(B6),3,"0"&DEC2HEX(B6),4,DEC2HEX(B6))