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.

### 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 theargument.*reference***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 theargument which represents*logical_test***TRUE**so the function returns theargument that is*value_if_false***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 theargument while,*array***N(IF({1},ROWS(D5:D9)-ROW(D5:D9)+MIN(ROW(D5:D9))))**is theargument that indicates the row location.*row_num***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 theargument.*array***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.

### 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.

## 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”*.

## 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**