Skip to content
ArumSoft Learning
KO
    ####Excel

    Why Excel shows #### in a cell

    Works in: All Excel versions, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerDouble-click the right edge of the column header (the line between the column letters) to make the column fit. If the hashes stay, the cell holds a negative date or time.

    Causes and fixes

    1. The column is too narrow
    2. The result is a negative date or time
    3. 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+.