Excel sometimes feels like it has a mind of its own. Almost every “bug” has a logical reason — usually Excel trying to help. Here are fifteen, with the why and the fix.
In this article
- 1. Typing 1-2 becomes 01-Feb
- 2. Leading zeros disappear (00123 → 123)
- 3. Long numbers turn into 1.23457E+15 and end in 0
- 4. ##### in a cell
- 5. The formula shows as text instead of a result
- 6. Numbers stored as text (green triangle)
- 7. SUM shows 0 or a too-small total
- 8. Formulas stop updating
- 9. #SPILL!
- 10. Circular reference warning
- 11. VLOOKUP returns #N/A for a value you can see
- 12. Gene names turn into dates (SEPT2 → 2-Sep)
- 13. The file is huge but has little data
- 14. Dates sort in the wrong order
- 15. Copying a filtered list pastes hidden rows too
1. Typing 1-2 becomes 01-Feb
Why: Excel guesses that text that looks like a date is a date. Fix: format cells as Text first, or type an apostrophe: '1-2.
2. Leading zeros disappear (00123 → 123)
Why: numbers do not have leading zeros. Fix: Text format, or custom format 00000 if it must stay a number.
3. Long numbers turn into 1.23457E+15 and end in 0
Why: Excel keeps only 15 significant digits (here is why). Fix: store card, Aadhaar and account numbers as Text.
4. ##### in a cell
Why: the column is too narrow for the number, or a date/time is negative. Fix: double-click the column border to widen; check negative dates.
5. The formula shows as text instead of a result
Why: the cell was formatted as Text before you typed, or Show Formulas (Ctrl+`) is on. Fix: set format to General and press F2 then Enter.
6. Numbers stored as text (green triangle)
Why: imported from a system that exported them as text. Fix: select → warning icon → Convert to Number, or Data → Text to Columns → Finish.
7. SUM shows 0 or a too-small total
Same cause as #6 — text numbers are ignored.
8. Formulas stop updating
Why: calculation is set to Manual (often by a macro or another workbook opened first). Fix: Formulas → Calculation Options → Automatic, or press F9.
9. #SPILL!
Why: a dynamic-array formula needs empty cells to spill into. Fix: clear the blocking cells (even spaces), or move the formula out of a table.
10. Circular reference warning
Why: a formula refers to its own cell, directly or through others. Fix: Formulas → Error Checking → Circular References shows the cell.
11. VLOOKUP returns #N/A for a value you can see
Why: invisible differences — trailing spaces, text vs number, non-breaking spaces from the web. Fix: TRIM, convert types, compare with LEN.
12. Gene names turn into dates (SEPT2 → 2-Sep)
Such a common problem that scientists renamed several human genes in 2020, and Microsoft later added an option to turn off automatic data conversion (File → Options → Data).
13. The file is huge but has little data
Why: the “used range” extends far beyond the data (formatting was applied to whole columns, or rows were once used). Fix: delete unused rows/columns below and right of the data, save, reopen.
14. Dates sort in the wrong order
Why: some “dates” are text (often dd/mm vs mm/dd confusion on import). Fix: convert with DATEVALUE or Text to Columns choosing DMY.
15. Copying a filtered list pastes hidden rows too
Why: a normal selection includes hidden rows in older behaviour. Fix: select, press Alt+; (visible cells only), then copy.
Stuck on a step? Ask a question and the AI answers using this article.