How to Write Scientific Notation in Excel?

Scientific notation is commonly used by mathematicians, engineers, and statisticians when they are writing very large numbers or very small numbers.

MS Excel offers some built-in features to work with scientific notation, making it easy for users to format and manipulate data.

By understanding how to use the scientific notation feature in Excel, you can enhance the presentation and understanding of your numerical data.

What is a Scientific Notation?

First, let’s see what scientific notation is.

Scientific notation is a useful way to represent very large or very small numbers in a more manageable form.

In scientific notation, a number is written as a decimal value multiplied by 10 raised to a certain power. For example, the number 5,000 can be written as 5.00E+03, where 5 is the decimal value, and 3 is the positive exponent.

Similarly, very small numbers can also be represented using scientific notation in Excel, but with a negative exponent.

For example, the number 0.003 can be written as 3.00E-03, where 3 is the decimal value, and -3 is the negative exponent. This way, even the smallest and largest numbers can be represented in a concise and easy-to-read format.

Below are some more examples that should make it very clear to you how scientific notation works in Excel.

• 1.23E+00 = 1.23
• 1.23E+02 = 123
• 1.23E-02 = 0.0123

Now that you understand the basics of scientific notation, you can apply this knowledge to Excel.

Excel has a built-in feature that allows you to display numbers in scientific notation automatically. This can be helpful when dealing with large datasets or performing calculations with very large or very small numbers.

Also read: How to Get Rid of Scientific Notation in Excel

Using Number Format to Write Scientific Notation in Excel

When there are very large numbers or very small numbers, it is easy to convert them into scientific notations. Then, we can easily manage and interpret those numbers.

Column A of the below table shows two numbers in number format. I want to show the same number in column B using scientific notations.

To change the number format of column B to Scientific, I can apply the below simple steps.

1. Select the cells that I want to change the number format to scientific. So, in this case, I have selected cells B2 and B3.

2. Go to the Home tab.

3. Expand the Number Formatting drop-down (which is in the Number group).

4. Select the ‘Scientific’ option from the drop-down list.

Now, the number formatting of all the selected cells is converted to scientific notation.

As you can see in the below image, the formula bar will show the original number formatting instead of scientific notation (which can be seen in the cell).

In this method, Excel always shows the scientific notation with two decimal places. After applying the Scientific number formatting, you can use the “Increase Decimal” or “Decrease Decimal” icons to change the decimal places. Increasing the decimal places will allow you to display more precision in your values.

Note: One benefit of applying scientific notation is that you get a consistent layout of numbers. In our example, both the numbers in cells A2 and A3 look different and have different numbers of digits before and after the decimal. However, when we convert these into scientific notation, they look consistent.

Also read: How to Convert Decimal to Binary in Excel

Using Format Cells to Write Scientific Notation in Excel

Format Cells option is another method that you can use to write scientific notation in Excel.

Column A of the below table shows two numbers in number format. I want to show the same number in column B using scientific notations.

To change the number format of column B to Scientific, I can apply the below simple steps.

1. Select the cells that I want to change the number format to scientific. So, in this case, I have selected cells B2 and B3.

2. Press “Ctrl + 1” to open the “Format cells” dialog box. If you don’t like to use shortcuts, you can use one of the below methods to open the “Format Cells” dialog box.
3. Right-click on the selected cells and select “Format cells…”.

4. Click the small “More” button in the “Number” group of the “Home” tab.

5. Go to the “Number” tab of the “Format Cells” dialog box.

6. Select “Scientific” from the Category list.

Unlike the previous method, in this method, you can change the decimal places of the scientific notation If you want. You can see the scientific number formatting of the number in the top left selected cell in the Sample box.

7. Click the “OK” button of the “Format Cells” dialog box.

Then, Excel converts all the selected cells to scientific notations.

Note: You can also format large numbers in charts and show them in scientific notation. To do this, right-click on the number and then click on the Format option to get access to the Scientific Notation format. This can be done with numbers in the Axes as well as data labels

Manually Typing the Scientific Notation in Excel Cells

Sometimes you may want to manually type the scientific notation in Excel. Yes, you can do that too.

Column A of the below table shows two numbers in number format. I want to type the same numbers in column B using scientific notations.

To do that, follow the below simple steps.

1. Go to cell B2 and type the first significant digit of cell A2. Leading zeros are not significant digits.

So, in cell A2, the first significant digit is 9 while in cell A3, the first significant digit is 4.

2. Enter a decimal place.

3. Type all the remaining numbers after the decimal place.

4. Enter “E” at the end of numbers. You can type either “e” or “E”.

5. Enter the “+” sign if you have to move the decimal point to the right side to get the original value. If you have to move the decimal point to the left side to get the original value, you have to enter the “-” sign.

In cell B2, you have to enter the “+” sign because you have to move the decimal point to the right side to get the original value.

6. Next, enter how many places the decimal places that you have to move to get the original value. So, in Cell B2, you have to enter 12.

7. Press the “Enter” key.

You can do the same steps in cell B3.

Now, you have typed numbers in column B as scientific notation.

Before you type the numbers in a cell, make sure the number format of that cell is “General”. If the number format of the cell is not “General”, you may get incorrect results.

Note: While you can manually write scientific notations in Excel, it time-consuming and not the best way. It’s alright to do this when you only need to do it for a couple of numbers, but if you have more, it’s best to use the in-built Scientific format in Excel

Also read: Format Numbers to Show in Millions in Excel

Using TEXT Function to Write Scientific Notation in Excel

Sometimes you may want to get the result of a formula in scientific notation format. In such a situation, you can use the Excel TEXT function. It helps you to apply a format code to numbers.

Column A of the below table shows two numbers in number format. I want to add a cell reference to Column A numbers in Column B. But, I want the results in Scientific number format.

For that, I can use the below function.

=TEXT(A2,"0.00E+00")

The syntax of the TEXT function is TEXT(value, format_text). The first argument of the TEXT function is value. You have to enter the number or the formula you want to format.

In this case, I have entered A2 for the first argument. Next, I have to enter the format code.

The format code of the scientific notation is 0.00E+00. Always enter the format code in the TEXT function within quotes.

The TEXT function converts numbers to text. So, if you are using the result of your formula for further calculations, consider other methods to convert the result to scientific notation.

Using VBA to Write Scientific Notation in Excel

When you have to write numbers in scientific notation many times, you can use Excel VBA code to change the number formatting of selected cells to scientific.

Column A of the below table shows two numbers in number format. I want to show the same number in column B using scientific notations.

To change the number format of column B to Scientific, I can apply the below simple steps.

1. Select the cells that I want to change the number format to scientific. So, in this case, I have selected cells B2 and B3.

2. Press “Alt + F10” to open the VBA Editor.

3. Go to the “Insert” tab and select “Module”.

4. Enter the below VBA code in the new Module.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub ConvertToScientificNotation()
Selection.NumberFormat = "0.00E+00"
End Sub

5. Click the “Run” button.

As soon as you run the macro, the formatting of all the selected cells would be changed to Scientific, and if there are any numbers in them, these would be shown in the Scientific notation format

Note that if you want to reuse the macro in the same workbook, you need to save the file with .XLSM file format (called macro-enabled file format)

So these are the methods you can use to write scientific notation in Excel. The easiest way would be to use the in-built Scientific format of Excel. Alternatively, you can also use the TEXT function or the VBA code.

Other Excel articles you may also like:

• How to Convert Radians to Degrees in Excel (Easy Formula)
• How to Apply Accounting Number Format in Excel