How to format a Microsoft Excel 2007 cell in hexadecimal?
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.
If cell to be converted is A1 use =DEC2HEX(A1).