Excel errors / #####

##### in a cell in Excel

The column is too narrow to show the number, or a date is negative.

A cell full of hash signs is not an error in the formula. The value is there and correct; Excel cannot display it. Either the column is too narrow for the number or date, or the cell is formatted as a date or time and its value is negative.

Excel does this on purpose. Showing part of a number, 1,234 where the value is 1,234,567, would be worse than showing nothing.

Have a formula that shows #####? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.

2 causes of #####, and how to fix each

  1. The column is too narrow

    A number, a date or a time that does not fit in its column is shown as hashes. Text is treated differently: it runs over into the next cell or is cut off.

    How to fix it. Double-click the line on the right of the column letter and the column widens to fit. Or select the column and use Home > Format > AutoFit Column Width. Showing fewer decimals or a shorter date format also helps.

  2. A negative date or time

    Subtracting a later date from an earlier one gives a negative number. As a number that is fine. In a cell formatted as a date it cannot be shown, because Excel's calendar has no dates before 1900.

    ABC
    1StartEnd
    22026-03-152026-03-01########

    In C2: =B2-A2 → ########

    How to fix it. If the result is a number of days, format the cell as General or Number, and it shows -14. If the order of the dates should not matter, take the absolute value.

    ABC
    1StartEnd
    22026-03-152026-03-0114

    In C2: =ABS(B2-A2) → 14

Common questions

Why does Excel show ##### instead of a number?

The column is not wide enough for it. Double-click the right edge of the column heading to widen it. If the column is already wide, the cell is formatted as a date or time and holds a negative value.

How do I fix ##### in a date cell?

Widen the column first. If the hashes stay, the date is negative: usually two dates subtracted the wrong way round, or a number of days in a cell that is formatted as a date. Change the format to General to see the number.

Practise it

Functions involved

Other errors