Excel Scientific Notation
Scientific Notation in excel is a special way of writing small and large numbers which helps us to compare and use the same in calculations.
How to Format Scientific Notation in Excel? (with Examples)
Example #1 – For Positive Exponent
Now we will see how to work on excel with the formatting of scientific notation. Below are the data for this example that I use. Copy the above number list and paste it in your worksheet area.
Copy and paste the first column numbers to the next column.
Now by selecting the range, click on the drop-down list of “Number Format”.
At the bottom, you can see “Scientific” formatting option, click on this to apply this formatting.
Now scientific formatting is applied to large and small numbers.
Now take an example of the first instance. General formatting number was 4750528335 and scientific number is 4.75E+09 i.e. 4.75 x 109 (10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10).
Now look at A6 cell value, general formatting value was 49758 and scientific value is 4.98E+04 i.e. 4.98 * 104 (10 * 10 * 10 * 10).
As you can see, the decimal value should have been 4.97 instead it shows as 4.98 this is because after two digits of decimal third digit value is “5” so, it is rounded to the nearest number i.e. instead of 97 it will be 98.
If you don’t want to see this rounding off to the nearest values, then you can increase the decimal value from 2 to 3 digits.
Now we can see scientific numbers in three-digits.
Example #2 – For Negative Exponent
You need to know how negative exponent works with excel scientific notation. Now, look at the below image.
First value is 0.0055 which is formatted as 5.5E-03 i.e. 5.5 x 10-3
Now look at the second number 4.589 which is formatted as 4.6E+00 i.e. 4.6 x 100
Things to Remember
- First, you need to understand how scientific notation works in mathematics and then learn the same in excel.
- We can only change the decimal values like 2, 3 and 4 digits.
- Excel uses scientific format automatically for large and small numbers of 12 digit values or more.
This has been a guide to scientific notation in excel. Here we discuss how to use scientific formatting in excel for positive and negative exponent along with examples and downloadable excel template. Below you can find some useful excel VBA articles –