Yes, you can remove scientific notation in Excel. But if you’re double-clicking cells and hitting Enter hoping it’ll ‘stick’, you’re overwriting precision—and possibly corrupting data.
Quick Answer
Change the number format of the affected cells from General or Scientific to Number or Text—but only before entering or pasting large numbers (like 1234567890123), because once Excel converts them to scientific notation internally, reformatting alone won’t recover lost digits.
All the Methods
Method
Steps
Best For
Limitations
Pre-format as Text
Select column → Home → Number Format dropdown → Text → then paste or type
Importing long IDs, SKUs, or phone numbers
Numbers formatted as Text won’t calculate (SUM, AVERAGE fail)
Custom Number Format
Right-click → Format Cells → Number → Custom → enter 0 or 000000000000000
Displaying up to 15-digit numbers without trailing zeros
Copy → right-click → Paste Special → Values → then reformat
Fixing already-pasted scientific notation (e.g., after CSV import)
Only works if original source had full digits—won’t restore lost precision
Apostrophe prefix
Type ' before number (e.g., '1234567890123456)
One-off entries; quick manual fixes
Visible apostrophe in formula bar; not scalable
TEXT function + CONCATENATE
=TEXT(A1,"0") or =TEXT(A1,"000000000000000")
Dynamic display in reports where values feed other text-based outputs
Result is text—can’t be summed or used in math without VALUE()
Method 1 Deep Dive: Pre-format as Text (The Only Safe Way for IDs)
This is what most people skip—and it’s why their order IDs like 789012345678901 become 7.89E+14 with no way back. The trick isn’t fixing it after—it’s preventing it.
Let’s say you’re importing a supplier list from Alibaba’s bulk export. Column A contains 16-digit PO numbers:
A1
B1
C1
PO-789012345678901
Acme Corp
2024-03-15
PO-203948576102938
BrightLamp Ltd
2024-03-18
PO-918273645019283
NovaGear Inc
2024-03-22
PO-456789012345678
Sunrise Trading Co.
2024-03-25
If you paste that into a blank sheet with default formatting, Excel reads 789012345678901 as a number—and immediately truncates it to 15 digits: 789012345678900. Gone forever.
Do this instead:
Select column A (click the ‘A’ header)
Press Ctrl+1 → choose Text under Category → OK
Now paste your data. Or use Data → Get Data → From Text/CSV, and set column type to Text during import
What makes this elegant is that it preserves every digit—not as a number, but as an identifier. And yes, you *can* still do VLOOKUPs on Text-formatted PO numbers. Just wrap your lookup value in TEXT() if needed: =VLOOKUP(TEXT(E1,"0"),A:C,2,FALSE).
Method 2 Deep Dive: Custom Number Format (For Display-Only Numbers)
Sometimes you need real numbers—not text—that just happen to be huge: invoice totals, shipment weights in grams, or API response IDs that must stay numeric for downstream formulas.
Say your finance team sent you this batch of gross sales figures (Column B, rows 2–6):
A2
B2
C2
Sarah Chen
123456789012345
$45,200
Rajiv Mehta
987654321098765
$61,850
Maya Lopez
456789012345678
$32,100
Tariq Al-Farsi
234567890123456
$54,920
These appear as 1.23E+14, 9.88E+14, etc. You *could* widen the column—but Excel won’t show all digits unless you change formatting.
Here’s the fix:
Select B2:B6
Press Ctrl+1 → go to Number tab → choose Custom
In the Type field, enter exactly 000000000000000 (15 zeros)
Click OK
That forces Excel to display all 15 digits—even if some are zero-padded. It doesn’t change the underlying value, and SUM(B2:B6) still works perfectly.
But here’s what most people miss: Excel stores only 15 significant digits. So if you type 1234567890123456 (16 digits), the last digit becomes 0. No formatting trick recovers it. That’s why Method 1 (Text) is non-negotiable for true 16+ digit integrity.
Cheat Sheet
Scenario
Action
Shortcut
Notes
Pasting new IDs (SKUs, POs)
Format column as Text first
Ctrl+1 → Text → OK
Do this BEFORE pasting
Already-pasted scientific notation
Copy → Paste Special → Values → then Ctrl+1 → Custom → 000000000000000
Alt+E+S+V → Enter → Ctrl+1
Only works if original source had full digits
Entering one ID manually
Type ' then the number
Apostrophe key (left of Enter)
Apostrophe won’t print, but appears in formula bar
Using in formulas (e.g., MATCH)
Wrap lookup with TEXT(): TEXT(A1,"0")
Type directly in formula
Ensures match against Text-formatted columns
Importing from CSV
Data → Get Data → From Text/CSV → Set column data type to Text
Alt+A+T → select file → click Transform Data → right-click column → Change Type → Text
Preserves leading zeros & long IDs
Lisa Anderson
Lisa is a certified Microsoft trainer who writes step-by-step guides for Power Automate
