Seongwoo Choi / Journal

What to check when ##### appears in an Excel cell: column width and negative dates or times

At a glanceWhen an Excel cell shows only #####, the value may not be gone: the column may be too narrow, or a date/time result may be negative. This article explains how to check both causes.

Conceptual illustration for What to check when ##### appears in an Excel cell: column width and negative dates or times.
AI-generated editorial illustration; not a product screenshot.
In this article 5 sections
  1. 1. Adjust the column width first
  2. 2. Check whether the date or time result is negative
  3. 3. Change the result cell to a number format to check the value
  4. 4. Summary of the checking order
  5. 5. Distinguish it from other Excel errors

When an Excel cell shows only #####, it is easy to panic because the value appears to have disappeared. However, according to Microsoft Support documentation, this display usually has one of two causes: the column is not wide enough to show the cell contents, or the result of a cell with a date or time format is negative. This article organizes the steps for checking these two causes first.

1. Adjust the column width first

Microsoft explains that ##### may appear when the cell contents are longer than the column width. Double-click the right boundary of the column heading to automatically adjust the column width to fit the contents. If you want a specific size, you can also drag the boundary to adjust it manually.

How to check: See whether all the other cells in the same column also show #####. If the entire column does, a column-width issue is likely. If only one cell does, check in the next step whether the value in that cell is a negative date or time.

2. Check whether the date or time result is negative

If ##### remains even after widening the column, suspect a negative date or time. Microsoft explains that when the result of a formula with date and time formatting is negative, it may still appear as ##### even if the column is widened. For example, if start time A2 is 18:00 and end time B2 is 09:00, calculating =B2-A2 produces a negative result. If this value is given a time format, ##### may appear in the cell. This example explains how the rule in the documentation applies; it is not the result of testing an actual workbook.

Microsoft's documentation recommends three checks. In workbooks that use the 1900 date system, confirm that the date and time values are positive. When subtracting dates, check that the subtraction order is correct. When adding or subtracting times, adjust the formula so that it does not produce a negative result.

3. Change the result cell to a number format to check the value

If a negative result is kept in a date or time format, the actual value is not visible. Microsoft also advises changing formula cells that produce negative results to a number format other than date or time so that you can check the value. You can open the Format Cells window with Ctrl+1 (according to the documentation). Changing the category to General or Number lets you check the negative value, which you can use to correct the formula order.

Caution: Changing the display format is a measure for checking the value. If you intend to keep using the result as a date or time, the fundamental solution is to correct the formula so that it does not produce a negative result.

4. Summary of the checking order

  1. Check whether all the cells in the same column show #####.
  2. Double-click the column-heading boundary to adjust the width.
  3. If ##### remains, check whether the date or time result is negative. Review the subtraction order and formula.
  4. Change the result cell to a number format to see the actual value, then correct the formula so that the result is positive.

5. Distinguish it from other Excel errors

Source: Microsoft Support - How to correct a ##### error , Microsoft Support - Format numbers as dates or times

Sources and original article

Read the Korean original on Naver

This article reflects public information available when it was written. Specifications, prices and schedules can change; check the linked official documentation.

This English edition was written and translated with AI assistance, with sources provided. For errors or additional information, please request a correction. Editorial policy