How to Convert Decimal to Binary in Excel (3 Quick Methods)

Unquestionably, Microsoft Excel excels at crunching numbers! Now, this means that you can perform tedious calculations in the blink of an eye. In this regard, Excel becomes a convenient and valuable tool. Keeping this in mind, this article demonstrates 3 ways of how to convert decimal to binary in Excel. Moreover, we’ll also learn how to convert text to binary in Excel.

How to Convert Decimal to Binary in Excel: 3 Quick Methods

First of all, let’s consider the List of Decimal Numbers dataset shown in the B4:B17 cells, which shows a random list of Decimal Numbers. Here, we want to convert these decimal numbers into binary numbers. Therefore, without further ado, let’s see each method on how to convert decimal to binary in Excel with the necessary illustrations.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

1. Using the DEC2BIN Function

First and foremost, let’s start with the simplest and most obvious way to convert decimal numbers to binary. Simply put, we’ll utilize the DEC2BIN function which takes integer decimal numbers and converts them to binary.

📌 Steps:

• At the very beginning, go to the C5 cell >> type in the formula given below >> use the Fill Handle Tool to copy the formula into the cells below.

Here, the B5 cell refers to the value of a “Decimal Number” that is 10.

Boom! You’ve converted the decimal numbers to binary. It’s that simple and easy.

2. Utilizing INT and MOD Functions

Alternatively, we can combine the INT and MOD functions to manually convert decimal numbers to binary. Here, the INT function rounds a number down to the nearest integer while the MOD function returns the remainder after division.

📌 Steps:

• First, move to the C5 cell >> enter the formula given below.

In this case, the B5 cell indicates the “Decimal Number” i.e. 40.

• Next, navigate to the adjacent D5 cell >> use the equation below.

For instance, the B5 cell represents the “Decimal Number” i.e. “40” and 2 refers to the divisor.

• Second, type the following equation into the C6 cell.

In this situation, the C5 cell is the “Quotient” i.e. 20.

• Then, copy and paste the formula into the D6 cell.

For example, the C6 cell indicates “Quotient” i.e. 10.

• Third, use the Fill Handle tool to apply the formula to the cell below >> proceed to the C10 cell >> copy and paste the expression into the Formula Bar.

• Following this, select the highlighted part of the formula >> hit the F9 key.

• Now, this returns the output shown in the image below.

• In turn, remove the curly brackets >> press the ENTER key.

At this time, you can observe the steps in real-time in the animated GIF below.

Eventually, the final output looks like the picture given below.

3. Employing Quotient and MOD Functions

Last but not least, the QUOTIENT and MOD functions can also be used to convert decimal numbers to binary. On this occasion, the QUOTIENT function returns the integer part of the division in contrast, the MOD function gives the remainder. So, let’s see it in action.

📌 Steps:

• To begin with, follow the steps shown in the previous method, however, instead of the INT function use the QUOTIENT function.

• Afterward, copy the C4:D9 cells >> move the cursor to the B11 cell >> click on Paste option >> choose the Values & Source Formatting option.

• Later, select the B12:C16 cells >> navigate to the Data tab >> click on Sort >> in the Sort by field, choose Quotient >> select Smallest to Largest Order.

• Not long after, enter the formula below into the C17 cell to obtain the binary numbers.

In the above formula, the C13, C14, C15, C16, and C17 represent the sorted “Remainder” values.

How to Convert Text to Binary in Excel

For one thing, Excel also allows us to convert text to binary using VBA Code. In this scenario, we’ll define a custom function that returns the binary numbers of the specified text. It’s simple and easy, so follow along.
Now, let’s assume the Employee Information dataset shown in the B4:C13 cells depicts the Department and Employee Names respectively. In this case, we’ll convert the “Employee Names” text into binary numbers.

📌 Steps:

• Initially, proceed to the Developer tab >> click the Visual Basic button.

Consequently, this opens the Visual Basic Editor in a new window.

• Next, click the Insert tab >> select Module.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Public Function String_To_Binary(str As String) As String
    Dim j As Long, lng As Long

    lng = Len(str)
    With Application.WorksheetFunction
        For j = 1 To lng
            String_To_Binary = String_To_Binary & .Dec2Bin(Asc(Mid(str, j, 1)))
        Next j
    End With
End Function

• At this time, jump to the D5 cell >> use the function to convert the “Employee Names” text to binary numbers.

On this occasion, the C5 cell indicates the “Employee Name Jim”.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Download Practice Workbook

Conclusion

In essence, this article shows 3 effective methods on how to convert decimal to binary in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

<< Go Back to | Excel Number System Conversion | Excel for Math | Learn Excel