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.

dataset for excel decimal to binary

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.

Using the DEC2BIN Function

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.

 Utilizing INT and MOD Functions

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

Using MOD function

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


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”}

Applying Formula into the Formula Bar

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

Selecting Formula and pressing F9 key

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

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

Removing Curly Brackets

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

excel decimal to binary Utilizing INT and MOD Functions

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

excel decimal to binary using the INT and MOD functions

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.

Employing Quotient and MOD Functions

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

Pasting with Value and Source formatting

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

Sorting data smallest to largest

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

excel decimal to binary using the QUOTIENT, MOD, and CONCATENATE functions

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.

Dataset for How to Convert Text to Binary in Excel

📌 Steps:

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

Go to developer tab

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

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

Inserting 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

VBA code for excel text to binary

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.

VBA Code explanation

  • 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”.

excel text to binary with VBA Code

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.

Practice Section for excel decimal to binary

Download Practice Workbook


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

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF