You see a cell filled with hash symbols instead of your data. This is a common formatting error in Excel. The problem occurs when a column is too narrow or when a formula results in a negative time or date. This article explains how to widen columns and correct negative time calculations to display your data correctly.
Key Takeaways: Fixing #### Errors
- Double-click the column border: This automatically resizes the column to fit the longest entry and is the fastest fix for width issues.
- Home > Number > Format > More Number Formats: Use this to switch a cell from a time or date format to a general number format, revealing the underlying negative value.
- Use the ABS function: Wrap your time calculation with =ABS() to convert a negative result to a positive number that can display in a time format.
Why Excel Displays Hash Symbols in Cells
Excel shows hash symbols as a visual placeholder. It means the cell’s content does not fit the current display settings. There are two primary technical reasons for this. The first and most common is insufficient column width. When a number, date, or text string is wider than the column, Excel shows #### to indicate the data is truncated.
The second reason involves date and time formats. Excel stores dates and times as serial numbers. A negative serial number is invalid for standard date and time display. If a formula like =”9:00 PM”-“10:00 PM” results in a negative time, and the cell is formatted as “Time”, Excel cannot show the value and displays #### instead. The cell contains a valid number, but the chosen format cannot represent it.
How Column Width and Cell Format Interact
The column width error is purely visual. Your data is still in the cell and used in calculations. The time format error is a data type conflict. Excel will not show a negative value in a time-formatted cell, even if the column is extremely wide. You must address the core issue of the negative value or change the cell’s format.
Steps to Widen Columns and Correct Time Calculations
Follow these methods to resolve the hash symbol display. Start with the column width fixes, as they are the most common solution.
- AutoFit the column width
Move your mouse pointer to the right border of the column header (e.g., the line between column letters A and B). Double-click when the pointer changes to a double-sided arrow. Excel will instantly widen the column to fit the longest cell entry. - Manually adjust the column width
Click and drag the right border of the column header. A small tooltip will show the pixel width as you drag. Release the mouse button when the width is sufficient to display your data. - Change the number format for negative time
Select the cell showing ####. Go to the Home tab. In the Number group, click the dropdown menu that likely says “Time”. Select “General” from the list. The cell will now show the underlying negative decimal number, confirming the calculation issue. - Use the ABS function to fix time calculations
Edit the formula causing the negative time. Wrap the entire calculation in the ABS function. For example, change =A1-B1 to =ABS(A1-B1). This returns the absolute (positive) value of the difference, which can display correctly in a time format. - Apply a custom number format
For advanced control, select your cells. Press Ctrl+1 to open the Format Cells dialog. Go to the Number tab and select Custom. In the Type field, enter a format like [h]:mm:ss. This special format can display large hour values and, in some cases, handle negative results differently than standard time formats.
If #### Persists After Widening the Column
If the column is very wide but still shows hash symbols, the problem is almost certainly related to date or time formatting. The following scenarios cover specific persistent errors.
Cell Shows #### Even With Maximum Column Width
This confirms a negative time or date error. Change the cell format to General using the Home > Number dropdown. If a negative number appears, your formula logic is incorrect. Review the order of subtraction in your time calculations to ensure you are not subtracting a later time from an earlier one unless you intend to get a negative duration.
Pasted Data Shows #### in All Cells
Data pasted from an external source may have a fixed number format applied. Select the affected range. On the Home tab, click the Clear button (eraser icon). Choose Clear Formats. This resets the cells to the General format. Then, use AutoFit on the columns. You can reapply the correct number format afterward if needed.
#### Appears Only in Printed Preview or PDF
The column width might be adequate on screen but not at the print resolution or scale. In Page Layout view, column widths may differ. Go to File > Print to check the preview. Adjust scaling by going to Page Layout > Scale to Fit group and reducing the Scale percentage until the data fits.
Methods to Resolve #### Errors: Comparison
| Item | AutoFit Column Width | Change Number Format to General | Use ABS Function in Formula |
|---|---|---|---|
| Primary Use Case | Column is too narrow for content | Diagnosing negative time/date values | Permanently fixing negative time calculations |
| Speed of Fix | Instant | Instant | Requires formula editing |
| Data Alteration | None, visual only | Changes display, not value | Changes the calculated result to positive |
| Best For | Numbers, long text, standard dates | Identifying the root cause of an error | Time sheets, duration calculations where sign is not needed |
You can now clear hash symbols from your worksheet by adjusting column width or correcting time formulas. Use the AutoFit feature for a quick visual fix on standard data. For persistent errors, switch the cell format to General to inspect the underlying value. For future time-based sheets, consider using the TEXT function to format results as text, which avoids negative display issues entirely. Press F2 to edit a cell and see its true content in the formula bar, regardless of #### on the sheet.