Scientific notation is a mathematical expression represented as a decimal number multiplied by a power of 10. It is especially useful when we want to express very small or very large values using fewer number of digits, or when we want to simplify how values are presented in data set.
Figure 1. Final result: Scientific Notation
Excel allows us to present a value in scientific notation through the scientific format.
How to use scientific notation
In order to apply the scientific notation format, we select the cells we want to format, then apply the scientific format through either of these two methods:
- Number Format menu on the ribbon
- Click Home tab > Number Tools drop-down arrow > Scientific
Figure 2. Scientific format in menu option
- Format Cells in menu options or use the keyboard shortcut Ctrl + 1
- Right click the cells and select Format Cells
Figure 3. Format cells option
The Format Cells dialog box will appear.
- Select Scientific in the Number tab
- Set the number of decimal places
Figure 4. Scientific format settings in Format Cells
Scientific notation format
After we have setup the cells to a scientific format, we copy the values in column B into column C.
Figure 5. Output: Scientific Notation
- For the scientific notation format, the only option is the number of decimal places
- We can set the decimal places by using Format Cells or through the ribbon buttons
Figure 6. Decimal places ribbon options
- We can type a scientific notation directly into a cell even if the cell is in General format
Enter 1.01E5 into a cell
Figure 7. Entering scientific notation into a cell
The value in the cell is 101000 but the display is 1.01E+05 and the format is automatically changed from General to Scientific.
Figure 8. Output: Scientific notation in a cell
- When we want to enter a value less than zero in scientific format, we add a “minus” sign before the exponent.
Enter 1.01E-5 into a cell
Figure 9. Entering a value less than zero
The value in the cell is 0.0000101 and the display in scientific format is 1.01E-05.
Figure 10. Output: Scientific notation of a value less than zero
- Excel automatically displays a number having 12 digits or more into a scientific format, even if the cell format is not set as Scientific
Enter 123456789000 into a cell
Figure 11. Entering a large value into a cell
Figure 12. Output: Large value automatically displayed as scientific notation