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.
=DEC2BIN(B5)
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.
Read More: How to Convert Binary to Decimal in Excel
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:
=INT(B5/2)
In this case, the B5 cell indicates the “Decimal Number” i.e. 40.
- Next, navigate to the adjacent D5 cell >> use the equation below.
=MOD(B5,2)
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.
=INT(C5/2)
In this situation, the C5 cell is the “Quotient” i.e. 20.
- Then, copy and paste the formula into the D6 cell.
=MOD(C6,2)
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.
=CONCATENATE(TRANSPOSE(INDEX(D5:D9,N(IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9)))))))
Formula Breakdown:
- ROW(D5:D9) → the ROW function returns the row number of a reference. Here, the D5:D9 range points to the reference argument.
- Output → {5;6;7;8;9}
- MIN(ROW(D5:D9)) → the MIN function returns the smallest number in a set of values, for example, the MIN function returns the smallest value from the {5;6;7;8;9} range which 5.
- Output → 5
- ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9))
- 5 – {5;6;7;8;9} + 5 → {5;4;3;2;1}
- IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9))) → the IF function checks whether a condition is met if TRUE and another value if FALSE. Here, {1} is the logical_test argument which represents TRUE so the function returns the value_if_false argument that is ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9)).
- Output → {5;4;3;2;1}
- INDEX(D5:D9,N(IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9))))) → the INDEX function returns a value at the intersection of a row and column in a given range. In this expression, the D5:D9 is the array argument while, N(IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9)))) is the row_num argument that indicates the row location.
- Output → {1;1;0;0;1}
- TRANSPOSE(INDEX(D5:D9,N(IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9)))))) → the TRANSPOSE function converts a vertical range of cells to a horizontal range. Here, INDEX(D5:D9,N(IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9))))) is the array argument.
- Output → {1,1,0,0,1}
- CONCATENATE(TRANSPOSE(INDEX(D5:D9,N(IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9))))))) → the CONCATENATE function joins several texts of strings.
- Output → {“1″,”1″,”0″,”0″,”1”}
- 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.
Read More: How to Convert Decimal Number to Hexadecimal (Hex) in Excel
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.
=CONCATENATE(C13,C14,C15,C16,C17)
In the above formula, the C13, C14, C15, C16, and C17 represent the sorted “Remainder” values.
Read More: How to Convert Hex to Decimal in Excel
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
⚡ Code Breakdown:
Here, we’ll explain the VBA code used to convert text to binary.
- In the first portion, the function is given a name, here it is String_To_Binary().
- Next, define the variables j and lng and assign the data type Long.
- In the second potion, use the Len function to determine the length of the given argument.
- Then, use the For Loop to loop through each text in the string and use the DEC2BIN and MID functions to convert the text to binary numbers.
- At this time, jump to the D5 cell >> use the function to convert the “Employee Names” text to binary numbers.
=String_To_Binary(C5)
On this occasion, the C5 cell indicates the “Employee Name Jim”.
Read More: How to Convert Hex Numbers to Binary in Excel
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.