You open a spreadsheet, glance at a column of salary figures, and see a row of hash symbols staring back at you. No numbers. No dates. Just ####. If you’ve worked in Excel for more than a week, you’ve seen this. It’s one of the most common things to go wrong, and it’s also one of the most misunderstood.
The #### error in Excel has more than one cause. Most people assume it always means the column is too narrow — and that’s usually right. But sometimes widening the column does nothing. The #### stays, no matter how wide you make it. That version of the error has a completely different root cause and needs a completely different fix.
This post covers every cause of the #### error, starting with the most common and working through to the ones that catch experienced users off guard. By the end, you’ll know exactly how to diagnose which type you’re dealing with and how to fix it.
Quick Takeaways:
- The most common cause is a column that’s too narrow to display the value. Double-click the right edge of the column header to AutoFit and fix it instantly.
- If widening the column doesn’t fix the ####, you likely have a negative date or time. Column width is irrelevant for this type — the formula itself needs to change.
- Excel cannot display negative dates. A formula like =A2-B2 where B2 is a later date than A2 will always show #### regardless of column width.
- Long date formats take more space than you’d expect. “31 January 2026” needs about 18 characters of column width. Switching to “31-Jan-2026” cuts that to 11.
- Ctrl + A, then double-click any column border auto-fits every column in the sheet at once. This is the fastest way to clear multiple #### errors after pasting data.
Cause 1: Column Too Narrow for a Number or Currency Value
This is the #### error you’ll encounter most often. A cell contains a number — a salary, a total, a price — and the column isn’t wide enough to show it. Excel doesn’t truncate numbers the way it truncates text. Instead it shows #### to signal that the value is there but can’t be displayed in the available space.
How to Fix It: AutoFit
Hover over the right edge of the column header — the letter at the top of the screen — until the cursor changes to a double-headed arrow. Double-click. Excel automatically resizes the column to fit its widest value. The #### disappears immediately.
This is called AutoFit, and it’s the fastest fix for the most common version of this error. You can also go to the Home tab, click Format in the Cells group, and choose AutoFit Column Width — but the double-click method is faster.
Fixing Multiple Columns at Once
If several columns are showing ####, don’t fix them one at a time. Press Ctrl + A to select the entire worksheet. Then double-click any column border in the header row. Every column in the sheet auto-fits simultaneously.
In the payroll dataset, columns D through G (Basic Salary, Allowances, Deductions, and Net Pay) are intentionally set to a width of 4 characters — far too narrow for values like 7,200 or 9,800. One Ctrl + A and a double-click clears all four at once.
Cause 2: Column Too Narrow for a Date
Dates behave the same way as numbers when it comes to ####. If the date format requires more characters than the column width allows, Excel shows hash symbols instead.
Why Long Date Formats Cause Problems
The format “DD MMMM YYYY” — which displays a date as “31 January 2026” — requires about 18 characters of column width. A column set to width 12 can’t display it. Switch to “DD-MMM-YYYY” and the same date becomes “31-Jan-2026” — only 11 characters. That fits comfortably in a narrower column.
How to Fix It
First try widening the column with a double-click. If the layout doesn’t allow for a wider column, change the date format instead. Select the cells, press Ctrl + 1 to open the Format Cells dialog, go to the Number tab, choose Date, and select a shorter format from the list.
Column H in the payroll dataset demonstrates this exactly. The pay dates are formatted as “DD MMMM YYYY” in a column of width 4. Both fixes work: widen the column, or change the format to something more compact like “DD/MM/YYYY” or “DD-MMM-YY.”
I’ve found that “DD-MMM-YYYY” (31-Jan-2026) is the best balance between readability and space. It’s unambiguous across regions, sorts correctly, and fits in a column width of around 12 without any issues.
Cause 3: Negative Date Result
Here’s the version that confuses most people. The column is wide. Very wide. The #### is still there. Widening it further changes nothing.
The formula bar shows a negative number. That’s the clue.
Why Excel Can’t Display Negative Dates
Excel stores dates as numbers, starting from January 1, 1900 as day 1. A date formula that produces a negative result — say, =A2-B2 where B2 is a later date than A2 — returns a number below zero. Excel’s date system has no concept of a date before day 1, so it can’t display the result as a date. It shows #### instead, and no amount of column resizing will change that.
In the payroll dataset, column J calculates days remaining until each employee’s review date. Eight employees have review dates already in the past. For those rows, the formula returns a negative number. The #### on those rows will not go away by widening the column.
How to Fix It
The fix depends on what you need the cell to show.
Option 1: Fix the formula order. If the subtraction is reversed (you accidentally put the earlier date first), swap them: =LaterDate – EarlierDate.
Option 2: Use ABS() to force a positive result. Wrapping the formula in ABS() returns the absolute value regardless of which date is larger: =ABS(A2-B2).
Option 3: Replace the number with meaningful text. This is often the cleanest solution for reporting. Instead of showing a number of days, use an IF statement to show a status label: =IF(ReviewDate-TODAY()<0,”Overdue”,”On Track”). The Fixed sheet in the practice file uses “Overdue / Due Soon / On Track” to replace the raw day count entirely.
Cause 4: Negative Time Result
Time values work exactly like dates under the hood. A formula that calculates the duration between two times — like =EndTime – StartTime — returns a negative number if the start time is later than the end time. This happens most often with overnight shifts.
The Overnight Shift Fix
If an employee starts at 10:00 PM and finishes at 6:00 AM, a simple =B2-A2 will return a negative time and show ####. The correct formula adds 1 to handle the midnight crossover: =IF(B2<A2, B2-A2+1, B2-A2).
The +1 represents one full day. When the end time is earlier than the start time, adding 1 day to the result gives the correct duration. This is a classic formula adjustment that Excel doesn’t handle automatically.
Cause 5: Very Large Numbers in Narrow Columns
Numbers with many digits — annual salary totals, multi-year projections, population figures — can exceed the column width even when the column looks reasonably sized. A value like 1,248,000 needs about 10 characters with thousand separators. A column of width 8 won’t show it.
Fix or Format
The immediate fix is the same as always: double-click to AutoFit. But for financial reports where you want to keep columns compact, a custom number format can display large numbers in abbreviated form.
Press Ctrl + 1, go to Number, choose Custom, and type #,##0,”K” in the Type field. The number 1,248,000 now displays as 1,248K. Use #,##0,,”M” for millions. This keeps your columns tidy while the full value remains stored in the cell.
Column K in the payroll dataset shows annual packages like 109,800 in a column of width 4. AutoFit resolves it immediately. For a compact layout, formatting as GHS amounts in thousands is the professional alternative.
One Fast Check Before Anything Else
Whenever you see ####, do this first: click the cell and look at the formula bar. If you see a valid number or date, the column is too narrow — double-click the column border to fix it. If you see a negative number in a cell formatted as a date, widening won’t help — fix the formula.
Those two checks cover over 90% of #### situations. The remaining causes (negative time, very large numbers, broken external links) follow the same diagnostic approach: what does the formula bar show, and is the column the real problem or is it the data itself?
Open the practice file’s first sheet to see all four error types live, then compare it to the Fixed sheet to see every solution applied. The Causes and Solutions reference sheet maps each cause to its specific fix, including prevention tips for avoiding the error before it appears.