Scientific Notation in Excel – A Step-by-Step Guide
(Note: This guide on how to use the scientific notation in Excel is suitable for all Excel versions including Office 365)
Excel is widely used across industries ranging from education and finance to engineering. This is only possible with the help of the various dedicated data formats in Excel. You may already know that numbers are displayed in different styles in different fields.
For example, in the accounting department, numbers are shown in a certain order along with their respective currencies. While in the science & engineering fields, very large or very small numbers are usually displayed in terms of exponents along with their decimal places.
Related:
- How to use EDATE in Excel? 11 Easy Examples
- How to Enable Excel Dark Mode? 2 Easy Methods
- How to Graph a Function in Excel? 2 Easy Ways
This guide will look at one of the most important data formats in Excel: The Scientific Notation.
How to Use the Scientific Notation Format in Excel?
Using the scientific notation in Excel is quite straightforward. Just follow these steps:
- Select the cells or range of cells where you want to apply the scientific notation.
- Click on the drop-down menu list from the Number group of the Home tab.
- Locate and click on the Scientific formatting option from the drop-down list.
This will apply the scientific notation to the selected cells.
In this example, the normal format number 4589762 gets converted to 4.59E+06. This means that the number is equal to 4.59×10^6.
Please note that Excel will only display two decimal digits by default, and round off the trailing numbers suitably. In the above example, the number 4589762 was rounded off to 4.59E+06.
If you want to avoid this, just click on the Increase Decimal button right below the Number group drop-down.
This will make Excel display the desired number of decimals before rounding them off.
So, clicking on the button will display the number 4.589E+06.
Similarly, you can use the nearby Decrease Decimal button to decrease the number of decimal places in your scientific notations.
Note: Excel applies the positive scientific notation for large numbers and the negative scientific notation for small numbers.
For example, the scientific notation of 0.0065 will be 6.5E-03. Whereas the scientific notation of 8.1 will be 8.1E+00.
Did you know that Excel automatically assigns scientific notation to all numbers which are more than 12 digits long?
How to Convert Scientific Notation Back to Numbers?
After you are done with your calculations, it is advisable to convert your scientific notation numbers back to normal notation. There are two different ways to do this.
Let us explore them one by one.
Method 1:
- Select the numbers you want to convert to normal formatting.
- Right-click on your selection and choose Format Cells.
- In the Format Cells dialog box, go to the Custom group in the Number tab and type ‘0’ in the blank space.
- Click OK.
This will immediately convert all scientific notation numbers to normal numbers.
Also Read:
- How to Add Leading Zeros in Excel? 4 Easy Methods
- How to Use Nested VLOOKUP in Excel? A Step-by-Step Guide
- Excel String Compare – 5 Easy Methods
Method 2:
- Arrange all the necessary numbers in a column, let us say from A2:A10
- In the adjacent column, just type TRIM(A2) and drag this formula from A2 to A10.
This will instantly convert all the numbers from scientific notation to normal format. The advantage of this method is that you still get to retain the original scientific notation as it is for future references.
Limits of Scientific Notation in Excel
The largest positive number which can be stored in Excel in scientific notation is 9.9X10^307. Similarly, the smallest number which can be written in scientific notation is -9.9X10^-307.
Number Precision in Excel
While working with the scientific notation in Excel, it is important to keep in mind that Excel’s numbers are precise only up to 15 digits. After that, Excel automatically rounds off the extra digits to zeros.
How to Turn Off Automatic Scientific Notation?
As we saw earlier, Excel automatically converts numbers with more than 12 digits to the scientific notation. This can also happen if the cell width is too narrow to display the number completely.
So, how do you stop this from happening?
Unfortunately, Excel does not allow you to switch this off by default. The only way you can prevent this from happening is as follows.
- Select the range of cells where you need to avoid using the scientific notation.
- Right-click on your selection and select Format Cells.
- In the dialog box, go to the Number group in the Number tab and change the decimal places to zero.
This way you can prevent Excel from converting numbers to automatic scientific notation.
Suggested Reads:
- How to Hide and Unhide Columns in Excel? (3 Easy Steps)
- How to Stop Excel From Rounding? 4 Easy Ways
- How to Make a Pie Chart in Excel? A Step by Step Guide
Closing Thoughts
The scientific notation in Excel is a seldom covered topic, but very important nonetheless. In this guide, we saw a brief overview of the scientific notation in Excel, while covering all its important aspects.
If you have any questions, please feel free to ask in the comments section. We are always happy to help.
If you find this guide helpful and are looking for more guides, please visit our Excel resources center.
Want to skill up in Excel?
Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!