Why Excel shows #### in a cell
Causes and fixes
- The column is too narrow
- The result is a negative date or time
- A cell formatted as Text holds very long text
1The column is too narrow
Excel won't cut a number or date in half, so it shows #### when it doesn't fit. The value itself is fine; click the cell and the formula bar shows it.
Fix
- Double-click the right edge of the column header to fit the widest value.
- Or use a shorter format, such as fewer decimals or a short date.
- Or shrink the text: Format Cells > Alignment > Shrink to fit.
2The result is a negative date or time
Excel can't show a date or time below zero. Subtracting an end time that's earlier than the start, for a shift that runs past midnight, gives a negative time and ####.
How to recognise it
Widening the column doesn't help. Change the cell to General and you see a negative number.
Fix
For times that cross midnight, use MOD so the result wraps to the next day:
=MOD(C2-B2,1)For days between two dates, format the result as General or Number, not as a date.
File > Options > Advanced > Use 1904 date system can show negative times, but it moves every date in the workbook by four years. Don't turn it on in an existing file.
3A cell formatted as Text holds very long text
In some versions, a cell formatted as Text that contains more than 255 characters shows #### instead of the text.
Fix
Change the cell format to General, then press F2 and Enter.
Still stuck?
- Click the cell and check the formula bar: if it shows the value you expect, it's only a width problem.
- Very large numbers in General format switch to scientific notation (1.2E+12) rather than ####. See Long numbers turn into E+.