It’s 3:12 PM on a Tuesday. You’re reviewing Q2 payroll data from HR when you spot $1.23E+05 in cell D7. Your stomach drops. Is that $123,000? Or $12.30? You copy the cell into chat and type ‘Is this wrong?’ — and your colleague replies, ‘Nope, just Excel being weird.’
The Problem
Excel shows E+05 (or E-03, E+08) when numbers are too large or too small for the current column width or number format. It’s not broken. It’s just speaking scientific notation — and most users don’t realize it’s silently truncating precision or hiding real values.
Here’s what happens in practice. Below is a raw export from a payroll system — opened directly in Excel without formatting adjustments:
| Employee | Annual Salary | Bonus % | Tax Withheld |
|---|---|---|---|
| Sarah Chen | 1.23E+05 | 7.5 | 2.46E+04 |
| Rajiv Mehta | 8.9E+04 | 5.2 | 1.78E+04 |
| Maya Torres | 1.67E+05 | 10.0 | 3.34E+04 |
| James Wu | 4.2E+04 | 3.8 | 8.4E+03 |
| Lena Petrova | 9.8E+04 | 6.1 | 1.96E+04 |
| Akira Tanaka | 2.1E+05 | 12.5 | 4.2E+04 |
That 1.23E+05 looks like a typo. But it’s actually 123,000. And 2.46E+04 is 24,600. Excel converted those numbers automatically because the column width was 8 characters — and the default General format kicked in.
Worse: if someone copies 1.23E+05 into another system (like SAP or QuickBooks), they’ll paste the text “1.23E+05”, not the number. That breaks downstream integrations.
The Solution
Fix it in under 30 seconds — no formulas needed. Do this:
- Select the affected cells (e.g., D2:D7).
- Right-click → Format Cells (or press Ctrl+1).
- In the Number tab, pick Number or Currency.
- Set Decimal places to 0 (for salaries) or 2 (for tax amounts).
- Click OK.
You’ll see the real values instantly:
| Employee | Annual Salary | Bonus % | Tax Withheld |
|---|---|---|---|
| Sarah Chen | $123,000 | 7.5% | $24,600 |
| Rajiv Mehta | $89,000 | 5.2% | $17,800 |
| Maya Torres | $167,000 | 10.0% | $33,400 |
| James Wu | $42,000 | 3.8% | $8,400 |
| Lena Petrova | $98,000 | 6.1% | $19,600 |
| Akira Tanaka | $210,000 | 12.5% | $42,000 |
That’s it. No macros. No hidden settings. Just one dialog box.
Counterintuitive tip: If you double-click a cell showing 1.23E+05, then press F2 and Enter — nothing changes visually. But Excel *does* re-evaluate its internal number format. Sometimes that alone forces it to drop scientific notation. Try it before formatting — it works 60% of the time on imported CSVs.
Going Further
You can automate this across entire worksheets:
- To fix all numeric columns at once: Select the whole data range (e.g., A1:E100), then press Alt+H, O, I → increases column width to fit contents.
- Use
=TEXT(A1,"#,##0")to convert1.23E+05to display as123,000— but note: this makes it text, not a number. Don’t use it for calculations. - If you’re importing from CSV or database exports, go to Data → From Text/CSV, then in the preview window, click each numeric column → select Whole Number or Decimal Number under Data Type. This prevents E-notation before it hits your sheet.
- For reporting: apply custom number format
_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)in column D to force currency + prevent scientific notation even if column shrinks.
When NOT to Use This
This fix only works if the underlying value is truly numeric. If you see 1.23E+05 in a cell and =ISNUMBER(A1) returns FALSE, then it’s text — not a number. That means Excel never interpreted it as a number in the first place.
Check with =ISNUMBER(A1) in an adjacent cell. If it’s FALSE, you’ve got text masquerading as numbers — common when pasting from web tables or PDFs. Then you need:
=VALUE(SUBSTITUTE(A1,"E+","E"))— fixes malformed E-notation text=--A1(double-unary) — converts clean text-numbers like “123000” but fails on “1.23E+05” unless cleaned first- Text-to-Columns (Data tab → Text to Columns → Delimited → Next → Next → Column data format = Number) — safest for mixed garbage data
Also: never apply Number format to dates stored as text. You’ll get serial numbers (e.g., 45210), not dates. Always verify =ISNUMBER() first.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Format Cells dialog | Ctrl+1 | Works on any selected cell(s) |
| Auto-fit column width | Alt+H, O, I | H = Home tab, O = Format dropdown, I = AutoFit Column Width |
| Edit cell (F2 mode) | F2 | Then press Enter to confirm — triggers recalc of display format |
| Paste Values Only | Alt+E, S, V | Critical when pasting from external sources to avoid inherited formats |
