It’s 3:12 PM. You’re pasting lab sensor readings into Excel: 6.02214076E+23, 1.602176634E-19, 9.1093837015E-31. You hit Enter. Excel shows 6.02E+23. Your colleague says, “Just format the cell.” You do — but now 0.000000123 becomes 1.23E-07, and your finance team rejects the report because ‘it looks like an error.’
Quick Answer
To write scientific notation in Excel, you don’t type E+ manually and hope — you either enter the number as a literal (e.g., 6.02E23 in A1), or apply Number Format > Scientific to a numeric cell. But here’s what most miss: Excel treats 6.02E23 as a number — not text — and will round it if the cell isn’t wide enough or the precision exceeds 15 digits.
All the Methods
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
Direct entry (1.23E-06) | Instant | Exact (if ≤15 sig digits) | Easy |
| Home > Number > Scientific format | 12 sec (Ctrl+1 → Sci → OK) | Rounds to displayed digits only | Easy |
Custom format 0.00E+00 | 28 sec (manual typing) | Preserves full stored value | Medium |
| TEXT() function + CONCATENATE | 42 sec (formula per cell) | Text output — no math possible | Medium |
| Paste Special > Text (to prevent auto-conversion) | 8 sec (Alt+E+S+T) | Guarantees exact string — no calculation | Easy |
Method 1 Deep Dive: Direct Entry — How to Put Scientific Notation in Excel
Type 6.022E23 into cell A1 and press Enter. Excel instantly converts it to a number — not text. Check the formula bar: it shows 6.022E+23. That’s correct.
But try 1.234567890123456789E10. Excel displays 1.23456789012345E+10 — and truncates everything after the 15th digit. The trailing 6789 is gone forever. This is non-negotiable: Excel stores only 15 significant digits.
Here’s what most people miss: If you need to preserve more than 15 digits (e.g., atomic mass constants), you must enter them as text first — before Excel sees them as numbers. Do this:
- Select column A (or your target range).
- Right-click → Format Cells → Category: Text.
- Now type
6.02214076E+23— it stays exactly as typed.
Sample data in A2:A7:
| Cell | Entry | What Excel Stores |
|---|---|---|
| A2 | 6.022E23 | 6.022E+23 (number, 4 sig digits) |
| A3 | 1.602176634E-19 | 1.602176634E-19 (10 sig digits) |
| A4 | 9.1093837015E-31 | 9.1093837015E-31 (11 sig digits) |
| A5 | '1.234567890123456789E10 | '1.234567890123456789E10 (text, all 19 chars) |
| A6 | =TEXT(1234567890123456789,"0.00E+00") | 1.23E+18 (text, rounded to 3 sig digits) |
| A7 | =CONCATENATE("1.234567890123456789","E+10") | 1.234567890123456789E+10 (text, exact) |
Note the apostrophe in A5: that forces text mode. No math. No rounding. Just raw fidelity.
Method 2 Deep Dive: Formatting — How to Add Scientific Notation in Excel
This is how to add scientific notation to existing numbers — say, you’ve already pasted 0.000000123 into B2. You want it to show as 1.23E-07.
Do this: Select B2 → Press Ctrl+1 → Choose Scientific → Set Decimal places to 2 → Click OK.
Now B2 displays 1.23E-07. But — and this is critical — the underlying value is still 0.000000123. You can use it in formulas. It’s still a number.
Where people get tripped up: formatting doesn’t change the stored value. So if you copy B2 and paste into Word, you’ll get 1.23E-07 — but if you paste into another Excel sheet with General format, it becomes 1.23E-07 again, not 0.000000123. Why? Because Excel pastes the formatted appearance by default — unless you use Paste Special > Values.
Try this test: In C2, type =B2*1000. Result? 1.23E-04. It works — because B2 is still numeric.
Now try custom formatting. Right-click B2 → Format Cells → Custom → Type: 0.000E+00. You get 1.230E-07. Three decimals. Same number. More control.
Here’s the counterintuitive tip: If your number is 0, Scientific format shows 0.00E+00 — which looks wrong. To fix that, use a custom format with conditions: [=0]"0";0.00E+00. Now zero shows as plain 0.
Real-world example: Lab results from Acme Corp (C3:C8):
| Sample ID | Concentration (mol/L) | Scientific Display |
|---|---|---|
| S-7821 | 0.00000000214 | 2.14E-09 |
| S-7822 | 0.000000000000891 | 8.91E-13 |
| S-7823 | 0.000000000000000307 | 3.07E-16 |
| S-7824 | 0 | 0 |
| S-7825 | 123456789012345 | 1.23E+14 |
| S-7826 | 1234567890123456 | 1.23E+15 |
Notice row 6: Excel rounds 1234567890123456 to 1.23E+15 — because only 15 digits are retained. The last digit (6) was dropped during storage, before formatting ever applied.
Cheat Sheet
| Task | How to Do It | Keyboard Shortcut |
|---|---|---|
| Enter scientific notation as text (exact) | Type '6.022E+23 (apostrophe first) | None — must type ' |
| Apply Scientific format | Select cells → Ctrl+1 → Scientific → OK | Ctrl+1 |
| Paste values only (avoid auto-formatting) | Copy → Alt+E+S+V → Enter | Alt+E+S+V |
| Paste as text (preserve exact strings) | Copy → Alt+E+S+T → Enter | Alt+E+S+T |
| Custom format: 3-digit mantissa | Format Cells → Custom → 0.000E+00 | Ctrl+1 → Custom |
Force zero to show as 0, not 0.00E+00 | Custom format: [=0]"0";0.00E+00 | Ctrl+1 → Custom |
| Convert number to scientific text (for labels) | =TEXT(A1,"0.00E+00") | None — formula |
