Scientific notation lets you write very large or very small numbers in a compact form
Scientific notation expresses numbers as a decimal between 1 and 10, multiplied by a power of 10. For example, 5,000,000 becomes 5 × 10^6, or written as 5E+06 in a spreadsheet. The letter E (or e) stands for "times 10 to the power of". Spreadsheets handle scientific notation in two ways: you can enter it directly when typing, or format existing numbers to display that way.
You use scientific notation in spreadsheets when you work with measurements in physics, chemistry, astronomy, or engineering — distances between stars, atomic weights, or bacterial counts. It also appears when you import data from scientific instruments or databases. The main reason to use it is readability: comparing 3.2E-08 is easier than comparing 0.000000032.
Most spreadsheets (Excel, Google Sheets, LibreOffice Calc) handle scientific notation the same way. The process takes seconds once you know where to look.
Key Takeaways
- You can type scientific notation directly into a cell using the format 5E+06 or 3.2E-08, and the spreadsheet will recognize it as a number.
- To convert an existing number to scientific notation display, select the cells and format them as "Scientific" from the number format menu.
- In Excel, right-click and choose Format Cells; in Google Sheets, use Format > Number > Scientific Notation from the menu bar.
- The E in scientific notation means "times 10 to the power of," so 1.5E+03 equals 1,500 and 2E-04 equals 0.0002.
- Spreadsheets treat scientific notation as regular numbers, so formulas and calculations work the same whether the display shows E notation or decimal form.
Typing scientific notation directly into cells
When you type a number in scientific notation, the spreadsheet recognizes it as a value you can use in calculations. Type the number in the format: a decimal, then the letter E (uppercase or lowercase), then a plus or minus sign, then the power of 10. For example, type 5E+06 for 5 million, or 3.2E-08 for 0.000000032.
Press Enter after typing. The spreadsheet stores the actual number and may display it either in scientific notation or in regular decimal form, depending on the cell width and your format settings. If you type 5E+06 and the column is wide enough, Excel typically shows it as 5000000. Google Sheets often keeps the E notation visible. Either way, the number itself is correct and works in formulas.
This method is fastest when you are copying data from a research paper, lab report, or database that already uses E notation. You paste or type it directly without converting.
Converting existing numbers to scientific notation in Excel
If you have a column of large or small numbers already entered as regular decimals, you can format them to display as scientific notation without changing the actual values. Select the cells you want to format. Right-click and choose Format Cells (or press Ctrl+1 on Windows, Command+1 on Mac).
In the Format Cells dialog, click the Number tab. In the Category list on the left, click Scientific. You will see a preview of how the number will look. The default shows two decimal places (for example, 5.00E+06). If you want more or fewer decimal places, change the number in the Decimal places field. Click OK.
Your numbers now display in scientific notation, but the spreadsheet still treats them as regular numbers. Formulas that reference these cells work exactly the same. If you change the format back to General or Number, the cells return to decimal display.
Converting existing numbers to scientific notation in Google Sheets
Select the cells containing the numbers you want to format. Click Format in the menu bar, then click Number, then click Scientific Notation. Google Sheets when ready converts the display.
To adjust decimal places, click Format > Number > Custom Number Format. In the custom format box, type 0.00E+00 for two decimal places, or 0.000E+00 for three. The zeros before the E control how many decimal places appear. Click explore.
Like Excel, Google Sheets keeps the underlying number unchanged. Only the display format switches. Calculations and references work normally.
Understanding the E notation: positive and negative powers
The number after the E tells you which direction to move the decimal point. A positive number (E+) means the original number is large. For example, 1.5E+03 means 1.5 × 1,000, which equals 1,500. The +03 means "move the decimal point 3 places to the right."
A negative number (E-) means the original number is small. For example, 2E-04 means 2 × 0.0001, which equals 0.0002. The -04 means "move the decimal point 4 places to the left." This is where scientific notation shines: writing 0.0000000032 is error-prone, but 3.2E-09 is clear and compact.
When you see a number in scientific notation, the part before the E (called the mantissa) is always between 1 and 10. The part after the E is the exponent. Spreadsheets follow this standard, so 50E+06 will often auto-correct to 5E+07 because 50 is not between 1 and 10.
When scientific notation appears without you asking for it
Sometimes a spreadsheet switches to scientific notation on its own. This usually happens when a column is too narrow to display a large number in decimal form. Widen the column by dragging the column border, and the number may revert to regular display. If it stays in E notation, the cell is probably already formatted that way.
Another reason is that you pasted data from a source that was already in scientific notation, or the spreadsheet imported data from a CSV file where the original source used E notation. Check the Format Cells dialog to see what format is applied. If you want decimal display instead, change the category to Number and set decimal places.
Spreadsheets also use scientific notation for numbers so large or small that they cannot fit in a standard cell width. This is a display choice, not a calculation error. The number is still accurate.
Using scientific notation in formulas and calculations
Once a number is in scientific notation — whether you typed it that way or formatted it — it behaves exactly like any other number in formulas. You can add, subtract, multiply, and divide. You can use it in SUM, AVERAGE, or any other function. The spreadsheet handles the conversion internally.
For example, if cell A1 contains 5E+06 and cell B1 contains 2E+06, the formula =A1+B1 returns 7000000 (or 7E+06 if that cell is also formatted as scientific). The calculation is correct regardless of how the numbers display.
One caution: if you are working with very small numbers (like 1E-15), rounding errors can accumulate in long chains of calculations. This is a limitation of how computers store decimal numbers, not a spreadsheet problem. For most business and scientific work, this is not an issue.
Frequently Asked Questions
Can I type scientific notation into a spreadsheet if I do not know the exact exponent?
No, you need to know or calculate the exponent yourself. Spreadsheets do not convert decimal numbers to scientific notation when you type them — they only recognize E notation if you type it explicitly. If you have a decimal number and want to know its scientific notation form, use a calculator or convert it manually, then type the E notation into the spreadsheet.
What is the difference between 5E+06 and 5e+06?
There is no difference. Spreadsheets accept both uppercase E and lowercase e. They mean the same thing: "times 10 to the power of." Use whichever you prefer or whatever your source material uses.
If I format a cell as scientific notation, will it stay that way if I copy it to another spreadsheet?
The format usually carries over if you copy and paste the cell itself, but it depends on the destination spreadsheet and how you paste. If you paste as values only, the format may not transfer. To be safe, reapply the scientific notation format in the new spreadsheet after pasting.
Can I mix scientific notation and regular numbers in the same column?
Yes. You can have 5E+06 in one cell and 1500 in another cell in the same column. If you format the entire column as scientific notation, both will display that way. If you format only some cells, the others keep their original display. Mixing is fine for calculations, but it can look messy in a report.
Why does my spreadsheet show 5E+06 when I typed 5000000?
The column is probably too narrow to display the full number, or the cell is formatted as scientific notation. Widen the column first. If it still shows E notation, right-click, choose Format Cells, and change the category from Scientific to Number. Then click OK.