How to Use Binary, Octal, and Hexadecimal Conversion Functions in Google Sheets

Working with low-level computer architecture, embedded systems telemetry, network subnetting, or digital logic designs frequently involves handling numerical values expressed in non-decimal formats: binary (base 2), octal (base 8), and hexadecimal (base 16). While basic spreadsheet formulas excel at standard decimal arithmetic, converting between specialized positional numeral systems by hand or through custom scripts is slow and susceptible to sign-bit errors.

Google Sheets includes a comprehensive suite of native engineering functions dedicated to direct base conversion: BIN2OCT, OCT2BIN, BIN2HEX, HEX2BIN, OCT2HEX, and HEX2OCT. These functions perform high-speed radix conversion, handle two’s complement signed negative numbers, and support custom padding using the significant_digits parameter. Understanding how to deploy these functions simplifies digital design, bitmask calculations, and microcontroller data processing.

Overview of Radix Conversion Functions in Google Sheets

Google Sheets provides specialized two-way conversion functions for all primary computer engineering bases:

• Binary to Octal and Hexadecimal: BIN2OCT and BIN2HEX
• Octal to Binary and Hexadecimal: OCT2BIN and OCT2HEX
• Hexadecimal to Binary and Octal: HEX2BIN and HEX2OCT
• Decimal Conversions: BIN2DEC, OCT2DEC, HEX2DEC, DEC2BIN, DEC2OCT, and DEC2HEX

Function Syntax and Parameter Rules

All Google Sheets radix conversion functions follow a standard argument structure:

=FUNCTION_NAME(signed_number, [significant_digits])

Parameter Breakdown

• signed_number: The input string or number to be converted. For binary, this can be up to 10 binary digits (bits); for octal, up to 10 octal digits; and for hexadecimal, up to 10 hex characters.
• significant_digits (Optional): The number of output characters to display. If the converted result has fewer digits than specified, Google Sheets pads the output with leading zeros. If omitted, Google Sheets displays the minimum required digits. This parameter is ignored if the input number is negative.

Converting Binary to Octal and Hexadecimal

In digital electronics, converting binary bit strings into octal (3-bit groupings) or hexadecimal (4-bit nibbles) is a frequent task:

1. Binary to Octal (BIN2OCT)

=BIN2OCT(111101)

Groups the bits as 111 101 and returns the octal string 75.

To pad the output to a 4-digit octal format:

=BIN2OCT(111101, 4)

Returns 0075.

2. Binary to Hexadecimal (BIN2HEX)

=BIN2HEX(10101111)

Converts the 8-bit byte into hexadecimal representation: AF.

With padding for a 16-bit word (4 hex digits):

=BIN2HEX(10101111, 4)

Returns 00AF.

Converting Octal to Binary and Hexadecimal

Legacy Unix file permissions (e.g., 755 or 644) and telecommunication channels frequently use octal notation:

1. Octal to Binary (OCT2BIN)

=OCT2BIN(75)

Returns the binary string 111101.

To enforce an 8-bit byte representation with leading zeros:

=OCT2BIN(75, 8)

Returns 00111101.

2. Octal to Hexadecimal (OCT2HEX)

=OCT2HEX(177)

Converts octal 177 (decimal 127) to hexadecimal: 7F.

Converting Hexadecimal to Binary and Octal

Hexadecimal values represent memory addresses, RGB colors, and network payloads:

1. Hexadecimal to Binary (HEX2BIN)

=HEX2BIN("2F")

Returns binary 101111.

To format as an 8-bit byte:

=HEX2BIN("2F", 8)

Returns 00101111.

2. Hexadecimal to Octal (HEX2OCT)

=HEX2OCT("FF")

Converts hexadecimal FF (decimal 255) to octal: 377.

Understanding Two’s Complement Signed Arithmetic

Google Sheets radix functions support two’s complement representation for negative numbers using a 10-character limit:

• In BIN2HEX or BIN2OCT, if the most significant bit (10th bit) is 1, the number is treated as negative.
• For example, =BIN2HEX(1111111111) represents -1 in 10-bit two’s complement and returns the 10-digit hexadecimal representation FFFFFFFFFF.
• When converting negative numbers, the significant_digits parameter is automatically disabled because Google Sheets must output all 10 characters to preserve the sign bit.

Practical Example: Dynamic Bitmask Conversion Table

Build an interactive decoding table where cell A2 contains an input hexadecimal register (e.g., "3C"):

Metric	Formula	Result
Hexadecimal Value	=A2	3C
8-Bit Binary Representation	=HEX2BIN(A2, 8)	00111100
Octal Representation	=HEX2OCT(A2, 4)	0074
Decimal Equivalent	=HEX2DEC(A2)	60

Error Handling and Boundary Constraints

Observe the following limits when working with radix functions in Google Sheets:

• Character Validation: Passing invalid characters (such as digit 8 in OCT2BIN, or letter G in HEX2BIN) returns a #NUM! error. Wrap formulas with IFERROR for user-facing dashboards.
• Bit Length Overflow: BIN2HEX only accepts up to 10 binary digits. To convert larger 32-bit or 64-bit binary strings, split the string into 8-bit chunks using MID, convert each byte individually, and concatenate the results with &.

Mastering Google Sheets radix conversion functions enables engineers and students to process binary, octal, and hexadecimal telemetry directly within their spreadsheets.