Excel Functions for Working with Hexadecimal, Octal, Decimal, and Binary Numbers - dummies

Excel Functions for Working with Hexadecimal, Octal, Decimal, and Binary Numbers

By Ken Bluttman

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.