How to Use Number System Conversion in Excel?

This article is about Excel number system conversion. In this Excel tutorial, you will learn
– How to use built-in functions to convert binary, decimal, hexadecimal, and octal to each other
– Different formats in Excel and converting them from numbers
– Applying comma in numbers

We have used Microsoft 365 while preparing this article but the methods mentioned here are also applicable in other Excel versions also.

There are four types of numbers in the number system- decimal, binary, hexadecimal, and octal. Although Excel doesn’t distinguish between them as different number systems, the program can convert one to another quite easily.

Excel doesn’t have any proper use of these number systems other than decimal by itself. However, for record keeping and other external uses, these conversions come in very handy.

Download Practice Workbook

Numbers Systems in Excel

How to Use Number System Conversion in Excel?

In this section, you will learn about the methods of all conversions between binary, decimal, octal and hexadecimal numbers.

1. Convert Binary to Other Numbers

Using Excel’s built-in BIN2HEX, BIN2OCt and BIN2DEC functions, you can convert binary numbers to hexadecimal, octal and decimal numbers respectively.

However, in Excel, you can’t convert binary numbers with more than 10 digits to other number formats. The formulas will return #NUM! if you try to do so.

1.1. Convert Binary to Hexadecimal

• Select cell C5, then apply the formula below and press Enter.
• After that, drag the Fill Handle icon to get the hexadecimal values of the rest of the binary numbers.

1.2. Convert Binary to Octal

• Go to cell C5, insert the formula given below, then hit Enter.
• Pull the Fill Handle to get the octal values of the rest of the binary numbers.

1.3. Convert Binary to Decimal

• Select cell C5, copy and paste the formula as shown and click on Enter.
• Hold and drag-down the Fill Handle icon to get the result of all other values.

2. Convert Decimal to Other Numbers

Utilizing Excel’s DEC2BIN, DEC2OCT and DEC2HEX functions, you can do the conversion of decimal numbers to binary, octal and hexadecimal numbers respectively.

In Excel, you can convert only those decimal values to other numbers that are within range -512 and 511. The formulas will return #NUM! if you try to do so.

2.1. Convert Decimal to Binary

• Choose cell C5 and apply the below formula. Then press Enter.
• Next, drag the Fill Handle icon to cell C9.

2.2. Convert Decimal to Hexadecimal

• Pick up cell C5, insert the formula given below, and hit Enter.
• Use the Fill Handle to auto-fill the rest of the cells.

2.3. Convert Decimal to Octal

• Select cell C5.
• Then apply the formula below and press Enter.
• By dragging the Fill Handle icon get the octal values of the rest of the decimal numbers.

3. Convert Octal Number to Other Numbers

You can convert octal numbers to decimal, binary and hexadecimal numbers using Excel’s built-in OCT2DEC, OCT2BIN and OCT2HEX functions respectively.

Must keep in mind that octal numbers contain digits 0 to 7 only. So, you can’t use 8 and 9 within your octal numbers.

And in Excel, octal numbers with more than 10 digits can’t be converted to other numbers using formula.

3.1. Convert Octal to Decimal

• Activate cell C5, insert the formula below, and click on Enter.
• Drag-down using the Fill Handle icon as shown and you will get the decimal values of all the octal numbers.

3.2. Convert Octal to Binary

• Select cell C5 and apply the formula below and press Enter.
• Use the Fill Handle icon to drag down.

3.3. Convert Octal to Hexadecimal

• Choose cell C5, then insert the below formula and press Enter.
• After that, select the Fill Handle icon and drag-down to get the hexadecimal values of the rest of the octal numbers.

4. Convert Hexadecimal to Other Numbers

You can convert hexadecimal numbers to binary, decimal and octal numbers using Excel’s built-in HEX2BIN, HEX2DEC and HEX2OCT functions respectively.

4.1. Convert Hexadecimal to Binary

In this section, we will use the HEX2BIN function. Sadly in Excel, this function can’t convert hexadecimal numbers with more than 199 to binary numbers. The formula will return #NUM if you put any values larger than 199 within the formula.

• Apply the following formula in cell C5 and press Enter.
• Hold and drag-down using the Fill Handle icon to get the binary values of the rest of the hexadecimal numbers.

4.2. Convert Hexadecimal to Decimal

Excel’s HEX2DEC function can convert hexadecimal numbers within 10 digits to decimal numbers.

• Select cell C5 and apply the formula below and hit Enter.
• Use the Fill Handle to drag-down and get the full output.

4.3. Convert Hexadecimal to Octal

Here, we will be using HEX2OCT function to convert hexadecimal numbers to octal numbers. But keep in mind, hexadecimal numbers greater than 1999999 can’t be converted to octal numbers using this formula.

• Activate cell C5 then insert the formula below and hit on Enter.
• Drag the Fill Handle icon for autofill.

How to Convert a Binary Number Longer than 10 bits to Other Numbers in Excel?

1. Converting Binary to Decimal

2. Converting Binary to Octal

3. Converting Binary to Hexadecimal

Main Content

VBA or multiple functions

https://stackoverflow.com/questions/35693029/bin2dec-for-numbers-longer-than-10-bits-in-excel

How to Convert a Decimal Number Out of Range -512 and 511 to Binary Number in Excel?

Main Content

VBA or Multiple Functions

How to Convert any Octal Numbers Other Numbers in Excel?

1. Converting Octal Number to Binary

2. Converting Octal Number to Decimal

=SUMPRODUCT(MID(A1,LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1)))+1,1)*8^(ROW(INDIRECT(“1:”&LEN(A1)))-1))

How to Customize Number Format in Excel?

There are multiple number format options available in Excel. If you go to the Home tab on your toolbar, you will see a section named Number. In the drop-down menu there, you will find several number format features as shown below. In this section, you will learn about some important number format features in Excel.

General Data Format

This one is the default number format. Excel finds the most suitable way to display the number based on its value when a cell is set to the General format. Like, the Annual Salary($) column was in currency format before.

Number Data Format

When you format a cell using the Number format, the number is shown with the default number of decimal places and without any special currency symbols or percentage signs. For that, you have to select the Annual Salary($) column and set the format as Number.

• You can even modify the decimal places of your numbers. In the Number section, you will find Increase Decimal and Decrease Decimal buttons.

• We have removed the decimal places from all the numbers by clicking the Decrease Decimal button.

Currency Data Format

• Suppose, you want to express the Annual Salary($) column with the Dollar ($) symbol.
• Select the Annual Salary($) column >> go to the Home tab >> click on Currency.

Accounting Data Format

There are many differences between accounting and currency number formats. The accounting format offers a clear alignment and presentation of currency values, especially in table form. It aligns currency symbols and decimal points in table form. The currency number format provides an easy to understand representation of currency values with the symbol positioned on the left.

• Select the Annual Salary($) column >> go to the Number section >> click on Accounting number format.

Short Date & Long Date Data Format

You can customize date data to its short and long forms. Like below, the dates are in short form as we have selected Short Date from the Home tabs’ Number section.

• Here, the dates are in long forms. We have selected the Long Date format.

More Number Formats

There are numerous data customization options in Excel. You can explore more number formats by clicking on the More Number Formats option as shown below.

• After clicking on that option, a dialog box titled Format Cells will appear.
• You can see on the left side that in the Category section, there are many formatting options like General, Number, Currency. You can easily explore each of them from there.

How to Apply Comma in Numbers in Excel?

Let’s say, you have a dataset as follows where column E is the Annual Salary($) column that is containing large values. So you wanted to apply commas in those numbers for a better understanding.

• Select column E >> go to the Home tab >> click on the comma symbol “,” as shown below.

Frequently Asked Questions

1. How do you count 1, 2, 3 in Excel?

Simply insert the digits 1, 2, and 3 into three consecutive cells in Excel to count the numbers. The COUNT function can then be used to count the number of cells that are filled with numbers.

The above can be used to count the numbers, assuming they are entered in cells A1, A2, and A3. Three numbers are present in the given range, as shown by the formula’s return value of 3.

2. How do I auto fill numbers in Excel?

There are several ways to auto fill numbers in Excel. The most effective and quick one is to input 2 or 3 numbers first that are maintaining the proper sequence. Then select those numbers range and drag the Fill Handle icon to auto fill the rest of the numbers.

• Suppose, you have input 1,2 in cell A1, A2
• Select the range A1:A2 and drag-down the Fill Handle

The rest of the cells will be filled automatically with 3,4,5,6…. If you type 2,4 in cell A1, A2 respectively then select range A1:A2 and drag-down using the Fill Handle icon, the rest of the cells will be filled automatically with 6,8,10,12……

3. How do I insert numbers 1 to 10 in Excel?

Learn these steps to make numbers 1 to 10 in Excel.

• Type 1 in cell A1.
• Type 2 in cell A2.
• Select range A1:A2.
• Double click on the Fill Handle Or simply hold and drag-down using the Fill Handle icon.
• This will insert numbers 1 to 10.

4. What is number format in Excel formula?

In an Excel formula, the number format describes how numerical values are shown in a cell. It enables you to select several formatting settings, including decimal places, currency symbols, percentage signs, and more, to determine how numbers appear. You can adjust the visual representation of numerical data in Excel by using various number formats without changing the data’s base values.

Conclusion

This article illustrates all the basics as well as advanced knowledge on the number system in Excel. Number conversion, customizing numbers, and dealing with several types of numbers are often necessary while working in Excel. Hope, you have already figured out what you were looking for from this article. Visit our site ExcelDemy to explore more related articles.

Number System in Excel: Knowledge Hub

<< Go Back to | Excel for Math | Learn Excel