Excel Functions for Working with Hexadecimal, Octal, Decimal, and Binary Numbers
There is a group of Excel functions that take into account all combinations of conversion among binary, octal, decimal, and hexadecimal. These functions are shown in the following table.
|Function||What It Does|
|BIN2DEC||Converts binary to decimal|
|BIN2HEX||Converts binary to hexadecimal|
|BIN2OCT||Converts binary to octal|
|DEC2BIN||Converts decimal to binary|
|DEC2HEX||Converts decimal to hexadecimal|
|DEC2OCT||Converts decimal to octal|
|HEX2BIN||Converts hexadecimal to binary|
|HEX2DEC||Converts hexadecimal to decimal|
|HEX2OCT||Converts hexadecimal to octal|
|OCT2BIN||Converts octal to binary|
|OCT2DEC||Converts octal to decimal|
|OCT2HEX||Converts octal to hexadecimal|
You can find these functions in the Engineering section of the Insert Function dialog box. Click the Insert Function button on the Formulas tab on the Ribbon.
In certain lines of work, it is desirable or even necessary to work in another base system. Designing computer systems is a good example. The computer chips that run PCs work with a binary system. Circuits are either on or off. This means that there are just two possible states — and they are often expressed as 0 and 1.
In base 2, or binary, all numbers are expressed with the digit 0 or 1. The number 20 as you know it in decimal is 10100 in binary. The number 99 is 1100011. The binary system is based on powers of 2.
In other words, in base 10 you count up through ten digits in one position before moving one position to the left for the next significant digit. And then the first position cycles back to the beginning digit. To make it simple, you count 0 to 9, add a 1 to the next significant digit, and start the first position over at 0. Therefore, 10 comes after 9.
Binary, octal, and hexadecimal each count up to a different digit before incrementing the next significant digit. That’s why when any larger base number, such as a base 10 number, is converted to binary, there are more actual digit places. Look at what happens to the number 20. In base 10, 20 is represented in 2 digits. In binary, 20 is represented in 5 digits.
Octal, based on powers of 8, counts up to 8 digits — 0 through 7. The digits 8 and 9 are never used in octal. Hexadecimal, based on powers of 16, counts up to 16 digits, but how? What is left after 9? The letters of the alphabet, that’s what!
Hexadecimal uses these digits: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, and F. The letters A through F represent the decimal values 10 through 15, respectively. If you have ever worked on the colors for a website, you may know that FFFFFF is all white. The web server recognizes colors represented in hexadecimal notation and responds appropriately.
The number 200 in decimal notation becomes C8 in hexadecimal notation. The number 99 in decimal notation becomes 63 in hexadecimal notation.