How to Convert Decimal to Binary in Excel

If you’re working with computers or machines, you might need to convert a set of decimal numbers into their binary equivalents.

In this tutorial we will go over how you can go both ways – convert decimal numbers to binary and vice-versa in Excel.

We will also show you how you can use various Excel functions to convert between the decimal and other number systems like octal, hexadecimal, etc.

Table of Contents

What is a Number System?

A number system is a system of representing numbers.

There are a variety of ways that numbers can be represented in this universe, out of which the most common representation is the decimal number system.

The decimal number system uses different arrangements of digits from 0 to 9 in order to represent different quantities.

It is known as the Decimal number system since it has a set of 10 digits to represent any number.

Similarly, computers and machines represent numbers in discrete ‘bits’ of information, where each bit is either a 0 or a 1.

As such, computers and machines are said to use the Binary number system.

In the same way, there exist other number systems, like octal, hexadecimal, etc. The hexadecimal system, for example, is commonly used in representing colors.

There are a certain set of rules to convert numbers from one system to another.

Although we will not go over too many details of this system, we will show you how to take advantage of Excel’s functions to quickly convert numbers from one system to another in the following sections.

Also read: How to Write Scientific Notation in Excel?

Converting Decimal to Binary in Excel

To convert decimal numbers to binary in Excel, you can use the DEC2BIN function.

This function takes just one input, which is a decimal number or a reference to the cell containing a decimal number.

It returns the same number converted to the Binary number system.

So, if you want to convert the number in cell A2 into its binary form, simply use the formula:

=DEC2BIN(A2)

DEC2BIN formula to convert number to binary

That’s all! It’s that easy!

Also read: Convert Time to Decimal in Excel

Converting Binary Number back to Decimal in Excel

Now what if you have a number in binary form and you want to convert it back to its decimal form? In that case you can use the BIN2DEC formula, as shown in the screenshot below:

Converting Decimal to Other Formats like Octal and Hex Format

If you are also interested in conversions to and from other number formats, here’s a list of functions and what they do:

Function	What it does
DEC2OCT	Converts Decimal to Octal Number System
DEC2HEX	Converts Decimal to Hexadecimal Number System
OCT2DEC	Converts Octal to Decimal Number System
HEX2DEC	Converts Hexadecimal to Decimal Number System

Also read: Convert Latitude Longitude to Decimal Degrees in Excel

Converting to Decimal to Binary Manually

As we saw, converting from decimal to binary is really quite easy thanks to Excel’s purpose-built functions.

It does everything under the hood, so you don’t really need to bother with how the conversions are done.

However, just for fun, let’s see how you would have to manage when converting decimal to binary in Excel (assuming that functions don’t exist).

This might help give you an insight into how the conversion actually takes place.

Let us say you want to convert the number 25 (which is in cell A2) into its Binary form manually in Excel.

For this conversion, follow the steps below:

Create 2 columns – one to display the Quotients and another to display the Remainders after division by 2, as shown below:

In the first cell of the ‘Quotients’ column, type the formula: =INT(A2/2). This will return only the integer part of the number obtained when the decimal number in A2 is divided by 2.

In the second cell of the ‘Quotients’ column, type the formula: =INT(B2/2). This will return the integer part obtained when the previous quotient is divided by 2.

INT function to divide previous value to the quotient

Drag this formula down using the fill handle until you get a 0 value.

In the first cell of the ‘Remainders’ column, type the formula: =MOD(A2,2). This will return the remainder obtained when the value in A2 is divided by 2.

In the second cell of the ‘Remainders’ column, type the formula: =MOD(B2,2). This will return the remainder obtained when the first ‘Quotient’ value is divided by 2.

Drag this formula down till you reach the last row (this will be the same row as the last row of the ‘Quotients’ column).

If you now look at the ‘Remainders’ column from the bottom to the top, you get the binary representation of your decimal number (25 in our example).

Binary representation is read from bottom to up

Now it’s time to consolidate these separate cells into a single cell with the binary value obtained.

Select the cell where you want the result to be displayed.
Type in the formula: =CONCATENATE(TRANSPOSE(INDEX(C2:C6,N(IF({1},ROWS(C2:C6)-ROW(C2:C6)+MIN(ROW(C2:C6)))))))
Select the TRANSPOSE function part of the formula and press F9 on your keyboard. This will convert the values in these cells into an array.

Remove the curly brackets that appear in the formula.

Press the return key.

You should now get the binary form of the decimal number that is in cell A2.

Explanation of the Formulas Used

Converting a number from decimal to binary mainly involves finding the remainder by successively dividing the decimal number by 2 till you obtain 0 as the quotient.

This explains the formulae we used in steps 2-7.

But what about the big formula of Step 9?

=CONCATENATE(TRANSPOSE(INDEX(C2:C6,N(IF({1},ROWS(C2:C6)-ROW(C2:C6)+MIN(ROW(C2:C6)))))))

Let’s look at this formula layer by layer, starting from the innermost layer:

(INDEX(C2:C6,N(IF({1},ROWS(C2:C6)-ROW(C2:C6)+MIN(ROW(C2:C6)))))

This part of the formula mainly takes each of the values in the cell range C2:C6 and reverses these values to obtain an array of individual digits (Unfortunately Excel does not yet have an in-built function to reverse cell values, so we had to resort to such a complex formula).

When we press F9, we end up with the following formula:

=CONCATENATE(TRANSPOSE({1;1;0;0;1}))

The array returned is {1;1;0;0;1}, which is actually a column array (separated by semicolons). To convert it to a row array, we use the TRANSPOSE function, which converts the semicolons into commas, as shown below:

=CONCATENATE({1,1,0,0,1})

Now the CONCATENATE function takes this array of binary digits and combines it into a single number.

Here’s a breakdown of how the formula returns the final result:

=CONCATENATE(TRANSPOSE(INDEX(C2:C6,N(IF({1},ROWS(C2:C6)-ROW(C2:C6)+MIN(ROW(C2:C6)))))))

=CONCATENATE(TRANSPOSE({1;1;0;0;1}))

After pressing F9 and removing curly brackets:

=CONCATENATE({1,1,0,0,1})

=11001

And there you have it! Imagine how much hard work the DEC2BIN function saved you!

In this tutorial, we showed you how to use the DEC2BIN function to convert decimal numbers to binary.

We also showed you how you can convert a binary number back to the decimal system, as well as how you can convert to and from other number systems, like Octal and Hexadecimal.

In the end, we showed you how to manually convert a given decimal number to its binary form in Excel.

We hope this was helpful.

Other articles you may also like: