Statistical analysis often tends to use a system in which you show numbers in a simplified form and this system is known as Scientific Notation. In Excel, this function is used to present very large numbers as well as very small numbers.
How to do scientific notation in Excel? To understand this query, you can continue reading this post. You can write a number as a smaller coefficient that multiplies 10 times with an exponent. On the right-hand side, you will find the exponent appears as a small number that shows how many times the coefficient is multiplied by 10 to get the original value.
Suppose, a number is presented as m x 10², the m would be multiplied by 10 twice to get to the original number.
How to Do Scientific Notation in Excel with Number Format?
Here the Number Format option is used to enter scientific notation in Excel. Let’s follow the steps given below:
- Choose the cell range D5:D10.
- Open the Home tab and from there choose the Number menu and click on the DropDown box.
- Click on Scientific.
- Ultimately, you will notice the scientific notation in Excel is added. That’s it!
How to Do Scientific Notation in Excel with Format Cells Option?
Here is another useful method in which we will use the Format Cells option to do Scientific Notation.
For this follow the steps given below:
- Choose the cell range D5:D10.
- Right-click to open the Context menu.
- From the Context menu, choose the Format Cells option.
- A dialog box of Format Cells will appear.
- Click on the Scientific option from the group menu.
- Now, you can change the Decimal Places of the number.
- Click OK.
That’s it!
Manually Typing to Enter Scientific Notation in Excel:
Other than the above-mentioned methods, you can manually type the Scientific Notation as well. For this, we have the following steps:
- Enter “2.847379794e9” in cell D5.
- Press ENTER key.
- From cell D5, the value could be as:
- 2847379794 as 2.847379794e9 or 28.47379794e8
In the above value, “e” is not case sensitive that simply shows “e or E” are similar.
Now, the value is all set to scientific notation.
For all the other cells, you need to repeat the same procedure. In case, if you have a larger data set that has many cells, try another method.
How to Switch Off Automatic Scientific Notation?
You already know that Excel by default helps in changing numbers more than 12 digits into scientific notation. It also appears when the cell width is not so broad to make the number appear completely. Excel does not provide any option to deactivate this option by default. Below are some points you can consider to make it effective:
- Select the cell range in which you don’t want to use the scientific notation.
- Right-click on the selection and choose the Format Cells option.
- From the dialog box that appears, open the Number group given in the Number tab.
- Convert the decimal places to zero.
That’s it!
Scientific Notation Restrictions in Excel:
- 9 x 10 ^ 307 is the largest positive number that you can save in Excel with scientific notation.
- -9.9 x 10 ^ -307 is the smallest number to add in Excel with Scientific Notation.
- All Excel numbers can be defined up to 15 digits only.
- Numbers above 15 digits will be rounded off to zeros by default.
Closing Thoughts:
Rarely, anyone talks about Scientific notation in Excel, however, it is still an important topic. In this post, you have learned how to do scientific notation in Excel with multiple approaches. Try all of them at least once!
